15 Advanced SQL Interview Questions

WITH UserFirstLogin AS (
  -- Find each user's first login month
  SELECT user_id,
         DATE_TRUNC('month', MIN(login_date)) AS cohort_month
  FROM user_logins
  GROUP BY user_id
),
MonthlyLogins AS (
  -- Get unique user login months
  SELECT DISTINCT user_id,
         DATE_TRUNC('month', login_date) AS active_month
  FROM user_logins
)
SELECT
  f.cohort_month,
  COUNT(DISTINCT f.user_id) AS cohort_size,
  COUNT(DISTINCT CASE WHEN m.active_month = f.cohort_month + INTERVAL '1 month' THEN m.user_id END) AS retained_m1,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN m.active_month = f.cohort_month + INTERVAL '1 month' THEN m.user_id END) / COUNT(DISTINCT f.user_id), 2) AS m1_retention_rate
FROM UserFirstLogin f
LEFT JOIN MonthlyLogins m ON f.user_id = m.user_id
GROUP BY f.cohort_month
ORDER BY f.cohort_month;

WITH MonthlyRevenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS rev_month,
    SUM(amount) AS total_revenue
  FROM orders
  GROUP BY 1
)
SELECT
  rev_month,
  total_revenue,
  LAG(total_revenue, 1) OVER (ORDER BY rev_month) AS prev_month_revenue,
  ROUND(100.0 * (total_revenue - LAG(total_revenue, 1) OVER (ORDER BY rev_month)) / LAG(total_revenue, 1) OVER (ORDER BY rev_month), 2) AS mom_growth_pct
FROM MonthlyRevenue
ORDER BY rev_month;

WITH RankedEmployees AS (
  SELECT
    emp_id,
    emp_name,
    department_id,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department_id 
      ORDER BY salary DESC
    ) AS salary_rank
  FROM employees
)
SELECT department_id, emp_id, emp_name, salary, salary_rank
FROM RankedEmployees
WHERE salary_rank <= 3
ORDER BY department_id, salary_rank;

WITH DistinctDays AS (
  SELECT DISTINCT user_id, DATE(login_date) AS login_day
  FROM user_logins
),
GroupedLogins AS (
  SELECT
    user_id,
    login_day,
    login_day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day)) * INTERVAL '1 day' AS streak_group
  FROM DistinctDays
)
SELECT
  user_id,
  MIN(login_day) AS streak_start,
  MAX(login_day) AS streak_end,
  COUNT(*) AS consecutive_days
FROM GroupedLogins
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 5;

Query Pattern: SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_spend FROM orders;

Query Pattern: Use a LEFT JOIN on user IDs between Last Month's active cohort and This Month's active cohort, filtering with WHERE this_month.user_id IS NULL.

Query Pattern: AVG(daily_sales) OVER (ORDER BY sales_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d.

Solution: Use conditional aggregation: SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1_rev across desired dimensions.

Method: Use LAG(click_time) OVER (PARTITION BY user_id ORDER BY click_time) to find idle time. If click_time - prev_time > 30 min, flag as 1, otherwise 0. Take running SUM(flag) to assign session IDs.

Explanation: Negative operators (!=, NOT IN) prevent database query planners from using B-Tree indexes, triggering full table scans. Optimize by restructuring to positive filters, using partial indexes, or bitmap scans.

The Trap: If the subquery contains even a single NULL, the NOT IN evaluates to UNKNOWN for every row, returning an empty result set. Always use NOT EXISTS or filter WHERE other_col IS NOT NULL.

Query: SELECT e.emp_name FROM employees e JOIN employees m ON e.manager_id = m.emp_id WHERE e.salary > m.salary;

Query Pattern: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount) AS p95 FROM orders;

Query: WITH Dupes AS (SELECT ctid, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY ctid) as r FROM my_table) DELETE FROM my_table WHERE ctid IN (SELECT ctid FROM Dupes WHERE r > 1);

Solution: Use WITH RECURSIVE OrgTree AS (SELECT emp_id, emp_name, manager_id, 1 as lvl FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id, o.lvl + 1 FROM employees e JOIN OrgTree o ON e.manager_id = o.emp_id) SELECT * FROM OrgTree;