SQL for Data Science Interview Questions for Data Science

SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); -- alternative with window function SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2;

SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1 ORDER BY cnt DESC;

SELECT DATE_TRUNC('month', order_date) AS month, SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled, SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned FROM orders GROUP BY 1 ORDER BY 1;

WITH rev AS ( SELECT category, product_id, SUM(price * qty) AS revenue FROM order_items GROUP BY category, product_id ), ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn FROM rev ) SELECT category, product_id, revenue FROM ranked WHERE rn <= 3;

WITH daily AS ( SELECT activity_date, COUNT(DISTINCT user_id) AS dau FROM events GROUP BY activity_date ) SELECT activity_date, dau, AVG(dau) OVER (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d_avg FROM daily ORDER BY activity_date;

WITH monthly AS ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_pct FROM monthly;

SELECT DISTINCT o.customer_id FROM orders o WHERE o.order_date >= '2026-01-01' AND o.order_date < '2026-02-01' AND NOT EXISTS ( SELECT 1 FROM orders f WHERE f.customer_id = o.customer_id AND f.order_date >= '2026-02-01' AND f.order_date < '2026-03-01' );

SELECT user_id, order_date, amount, SUM(amount) OVER ( PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_spend FROM orders ORDER BY user_id, order_date;

SELECT customer_id, MIN(order_date) AS first_order, MAX(order_date) AS last_order, MAX(order_date) - MIN(order_date) AS days_between, COUNT(*) AS orders FROM orders GROUP BY customer_id ORDER BY days_between DESC; -- PostgreSQL: DATE minus DATE gives days; use DATEDIFF in other engines

WITH signups AS ( SELECT user_id, DATE_TRUNC('week', signup_date) AS cohort, signup_date FROM users ), active AS ( SELECT DISTINCT s.user_id, s.cohort FROM signups s JOIN events e ON e.user_id = s.user_id AND e.event_date >= s.signup_date + INTERVAL '7 day' AND e.event_date < s.signup_date + INTERVAL '14 day' ) SELECT s.cohort, COUNT(*) AS users, ROUND(100.0 * COUNT(a.user_id) / COUNT(*), 1) AS week1_retention_pct FROM signups s LEFT JOIN active a ON a.user_id = s.user_id GROUP BY s.cohort ORDER BY s.cohort;

SELECT city, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount FROM orders GROUP BY city; -- PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual value

WITH steps AS ( SELECT user_id, MAX(CASE WHEN event = 'visit' THEN 1 ELSE 0 END) AS visited, MAX(CASE WHEN event = 'add_to_cart' THEN 1 ELSE 0 END) AS carted, MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS purchased FROM events WHERE event_date >= '2026-10-01' GROUP BY user_id ) SELECT SUM(visited) AS visits, SUM(carted) AS carts, SUM(purchased) AS buyers, ROUND(100.0 * SUM(carted) / NULLIF(SUM(visited), 0), 1) AS visit_to_cart_pct, ROUND(100.0 * SUM(purchased) / NULLIF(SUM(carted), 0), 1) AS cart_to_buy_pct FROM steps;

WITH d AS ( SELECT DISTINCT user_id, CAST(login_at AS DATE) AS day FROM logins ), g AS ( SELECT user_id, day, day - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS INT) AS grp FROM d ) SELECT DISTINCT user_id FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3; -- date minus row number is constant within a streak (PostgreSQL syntax)