15 Advanced SQL Interview Questions

WITH UserFirstLogin AS (
  -- 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 (
  -- One row per user per active month
  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
),
WithPrevious AS (
  SELECT
    rev_month,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY rev_month) AS prev_revenue
  FROM MonthlyRevenue
)
SELECT
  rev_month,
  total_revenue,
  prev_revenue,
  ROUND(100.0 * (total_revenue - prev_revenue)
    / NULLIF(prev_revenue, 0), 2) AS mom_growth_pct
FROM WithPrevious
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:

SELECT
  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: keep the first copy of each row and delete the rest.

-- PostgreSQL: ctid is the physical row id
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: a recursive CTE that starts at the top of the tree and walks down one level at a time.

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;