real world patterns

This is where everything comes together. These are the problems that keep showing up in actual jobs and interviews, and each one is a combo of CTEs, window functions and joins you've already seen. Once you know these shapes, most "hard" sql questions are just one of them in disguise.

1. top N per group

"Top 2 earners in each department", "latest order per customer", "best selling product per category".

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary FROM ranked WHERE rn <= 2;

ROW_NUMBER for exactly N, DENSE_RANK if ties should all be included.

2. deduplication

Duplicate rows got in, keep one per email, the newest:

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY signup_date DESC) AS rn
  FROM customers
)
SELECT * FROM ranked WHERE rn = 1;

To actually delete the extras:

DELETE FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM (
    SELECT customer_id,
      ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY signup_date DESC) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);

3. running totals & cumulative %

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT order_date, revenue,
  SUM(revenue) OVER (ORDER BY order_date) AS running_total,
  ROUND(100.0 * SUM(revenue) OVER (ORDER BY order_date) / SUM(revenue) OVER (), 1) AS cumulative_pct
FROM daily;

4. period over period (growth)

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

NULLIF so a zero month doesn't blow up the division.

5. first / last event per user

SELECT DISTINCT
  customer_id,
  FIRST_VALUE(order_date) OVER w AS first_order,
  LAST_VALUE(order_date)  OVER w AS last_order,
  COUNT(*) OVER (PARTITION BY customer_id) AS orders
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

(or just MIN / MAX with GROUP BY, honestly simpler for this one. Use the window version when you need other columns from that first row.)

6. gaps and islands

Find streaks, like consecutive days a user was active. The trick: for consecutive dates, date - row_number gives the same value, so you can group by it.

WITH activity AS (
  SELECT * FROM (VALUES
    (1, DATE '2024-03-01'), (1, '2024-03-02'), (1, '2024-03-03'),
    (1, '2024-03-07'), (1, '2024-03-08'),
    (2, '2024-03-05')
  ) AS t(user_id, day)
),
grp AS (
  SELECT user_id, day,
    day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS island
  FROM activity
)
SELECT user_id, MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS days
FROM grp
GROUP BY user_id, island
ORDER BY user_id, streak_start;

User 1 gets two streaks, 3 days and 2 days. Looks like magic the first time, write out the row numbers by hand once and it'll click.

7. retention / cohorts

Which month did each customer start in, and did they come back later?

WITH first_order AS (
  SELECT customer_id, DATE_TRUNC('month', MIN(order_date))::date AS cohort
  FROM orders
  GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT o.customer_id, f.cohort,
    DATE_TRUNC('month', o.order_date)::date AS active_month
  FROM orders o JOIN first_order f ON o.customer_id = f.customer_id
)
SELECT cohort,
  (EXTRACT(YEAR FROM AGE(active_month, cohort)) * 12 + EXTRACT(MONTH FROM AGE(active_month, cohort)))::int AS months_later,
  COUNT(*) AS customers
FROM activity
GROUP BY 1, 2
ORDER BY 1, 2;

8. funnel

How many people made it through each step:

SELECT
  COUNT(DISTINCT customer_id) FILTER (WHERE payload ->> 'type' = 'page_view')   AS viewed,
  COUNT(DISTINCT customer_id) FILTER (WHERE payload ->> 'type' = 'add_to_cart') AS added,
  COUNT(DISTINCT customer_id) FILTER (WHERE payload ->> 'type' = 'purchase')    AS bought
FROM events;

9. filling missing dates

Reports with gaps look wrong. Generate the full calendar and LEFT JOIN onto it:

SELECT d::date AS day, COUNT(o.order_id) AS orders
FROM generate_series(DATE '2024-03-01', DATE '2024-03-31', INTERVAL '1 day') d
LEFT JOIN orders o ON o.order_date = d::date
GROUP BY 1
ORDER BY 1;

how to approach a new hard question

1. figure out what one row of the final answer is
2. write it as steps in plain words
3. one CTE per step
4. run each CTE on its own and look at the output before moving on

Most "I can't solve this" moments are trying to write it all in one go.

practice

Check queries.sql.

-- real world patterns
-- run sample-data.sql first

-- examples ---------------------------------------------------------

-- top 2 per department
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary FROM ranked WHERE rn <= 2;

-- gaps and islands
WITH activity AS (
  SELECT * FROM (VALUES
    (1, DATE '2024-03-01'), (1, '2024-03-02'), (1, '2024-03-03'),
    (1, '2024-03-07'), (1, '2024-03-08'), (2, '2024-03-05')
  ) AS t(user_id, day)
),
grp AS (
  SELECT user_id, day,
    day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS island
  FROM activity
)
SELECT user_id, MIN(day) AS streak_start, MAX(day) AS streak_end, COUNT(*) AS days
FROM grp GROUP BY user_id, island ORDER BY user_id, streak_start;

-- funnel
SELECT
  COUNT(DISTINCT customer_id) FILTER (WHERE payload ->> 'type' = 'page_view')   AS viewed,
  COUNT(DISTINCT customer_id) FILTER (WHERE payload ->> 'type' = 'add_to_cart') AS added,
  COUNT(DISTINCT customer_id) FILTER (WHERE payload ->> 'type' = 'purchase')    AS bought
FROM events;


-- practice ---------------------------------------------------------

-- 1. the best selling product (by units) in each category
-- 2. each customer's most recent order, with the product names in it
-- 3. the customer who spent the most in each city
-- 4. the longest gap in days between any two consecutive orders (store wide), and which orders
-- 5. what % of customers who ordered, ordered more than once?
-- 6. daily revenue for june 2024 including days with 0, plus a 7-day moving average


-- answers ----------------------------------------------------------

-- 1
WITH units AS (
  SELECT p.category, p.product_name, SUM(oi.quantity) AS sold
  FROM order_items oi JOIN products p ON oi.product_id = p.product_id
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY category ORDER BY sold DESC) AS rnk FROM units
)
SELECT category, product_name, sold FROM ranked WHERE rnk = 1;

-- 2
WITH latest AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT c.first_name, l.order_id, l.order_date, STRING_AGG(p.product_name, ', ') AS products
FROM latest l
JOIN customers c ON l.customer_id = c.customer_id
JOIN order_items oi ON l.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE l.rn = 1
GROUP BY c.first_name, l.order_id, l.order_date;

-- 3
WITH spend AS (
  SELECT c.city, c.first_name, SUM(o.amount) AS total
  FROM customers c JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.city, c.first_name
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY city ORDER BY total DESC) AS rnk FROM spend
)
SELECT city, first_name, total FROM ranked WHERE rnk = 1;

-- 4
WITH gaps AS (
  SELECT order_id, order_date,
    LAG(order_id)   OVER (ORDER BY order_date) AS prev_order,
    order_date - LAG(order_date) OVER (ORDER BY order_date) AS gap_days
  FROM orders
)
SELECT * FROM gaps WHERE gap_days IS NOT NULL ORDER BY gap_days DESC LIMIT 1;

-- 5
WITH counts AS (
  SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id
)
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE n > 1) / COUNT(*), 1) AS pct_repeat FROM counts;
-- 3 out of 6, so 50.0

-- 6
WITH days AS (
  SELECT d::date AS day FROM generate_series(DATE '2024-06-01', DATE '2024-06-30', INTERVAL '1 day') d
),
daily AS (
  SELECT days.day, COALESCE(SUM(o.amount), 0) AS revenue
  FROM days LEFT JOIN orders o ON o.order_date = days.day
  GROUP BY days.day
)
SELECT day, revenue,
  ROUND(AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS moving_avg_7d
FROM daily
ORDER BY day;