Advanced SQL Coding Questions

Data, analytics and backend interviews increasingly ask “harder” SQL: time-series comparisons, streaks, hierarchies and medians. These solutions use window functions and CTEs that run on PostgreSQL and MySQL 8. Tables: orders(id, user_id, amount, created_at), logins(user_id, login_date), employees(id, name, manager_id, salary).

1. Find users who logged in on 3 or more consecutive days.

The gaps-and-islands trick: subtract a row number from the date; consecutive days share the same difference, forming a group.

WITH days AS (
  SELECT DISTINCT user_id, login_date FROM logins
), grouped AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) * INTERVAL '1 day' AS grp
  FROM days
)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

2. Calculate month-over-month revenue growth.

Aggregate per month, then compare with the previous row using LAG.

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) 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), 1) AS growth_pct
FROM monthly
ORDER BY month;

3. Find the median salary.

PostgreSQL has PERCENTILE_CONT. Portable version: rank rows from both ends and average the middle one or two.

-- PostgreSQL
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees;

-- Portable
SELECT AVG(salary) AS median
FROM (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS cnt
  FROM employees
) t
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2);

4. Show the full reporting chain under a manager (org chart).

A recursive CTE starts with the manager and repeatedly joins direct reports.

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees WHERE id = 1                       -- the manager
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.level + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT REPEAT('  ', level) || name AS org_chart, level
FROM chain
ORDER BY level, name;

5. Find each user's first order and the days to their second order.

Number each user's orders, then use LEAD to look at the next one.

SELECT user_id, created_at AS first_order,
       next_order - created_at AS gap
FROM (
  SELECT user_id, created_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
         LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order
  FROM orders
) t
WHERE rn = 1;

6. Compute a 7-day moving average of daily revenue.

A window frame defines which rows to average.

SELECT day, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS avg_7d
FROM (
  SELECT CAST(created_at AS DATE) AS day, SUM(amount) AS revenue
  FROM orders GROUP BY 1
) d
ORDER BY day;

7. Find customers who bought in January but not in February.

Set logic with EXCEPT (or NOT EXISTS in MySQL versions without EXCEPT).

SELECT user_id FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
EXCEPT
SELECT user_id FROM orders WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';

8. Pivot: count orders per user per weekday.

Conditional aggregation works in every database.

SELECT user_id,
       COUNT(*) FILTER (WHERE EXTRACT(DOW FROM created_at) = 1) AS mon,
       COUNT(*) FILTER (WHERE EXTRACT(DOW FROM created_at) = 2) AS tue,
       COUNT(*) FILTER (WHERE EXTRACT(DOW FROM created_at) = 3) AS wed
FROM orders
GROUP BY user_id;
-- MySQL: SUM(CASE WHEN DAYOFWEEK(created_at) = 2 THEN 1 ELSE 0 END) AS mon