SQL Interview Crash Course

SQL Interview Prep

SQL Interview Crash Course

Structured for ML, Data, and Backend engineering interviews. Six modules of progressive difficulty, built around one unified e-commerce/analytics schema, capped off with a timed 10-question mock interview.

6 modules Progressive difficulty 1 unified schema 10-question mock interview Zero setup — read & drill

→How to use this course

Work through modules 1–6 in order — each builds on the last. Every module ends with practice problems; try them yourself before tapping "Show Answer". When you've drilled all six, take the timed mock interview and score yourself with the guide at the bottom. Tick off modules in the sidebar as you finish them to track progress — it's saved locally in your browser.

☰Modules

DBPractice Schema

Every example and problem in this course uses the same unified e-commerce/analytics schema:

users

id
name
email
signup_date
country

orders

id
user_id
amount
status
created_at

products

id
name
category
price

order_items

order_id
product_id
quantity
unit_price

events

id
user_id
event_type
event_time
session_id

Module 1

Core Foundations

SELECT, WHERE, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT

Mental Model

SQL runs in this order:
FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

1.1Basic SELECT

SQL

-- All columns
SELECT * FROM users;

-- Specific columns + alias
SELECT id, name, country AS user_country FROM users;

-- Computed column
SELECT id, name, UPPER(email) AS email_upper FROM users;

1.2WHERE — filtering rows

SQL

-- Equality, comparison
SELECT * FROM orders WHERE status = 'completed';
SELECT * FROM orders WHERE amount > 500;

-- Multiple conditions
SELECT * FROM orders WHERE status = 'completed' AND amount > 1000;
SELECT * FROM orders WHERE status = 'pending' OR status = 'cancelled';

-- IN (cleaner than multiple ORs)
SELECT * FROM orders WHERE status IN ('pending', 'cancelled');

-- BETWEEN (inclusive both ends)
SELECT * FROM orders WHERE amount BETWEEN 100 AND 1000;

-- LIKE (pattern match)
SELECT * FROM users WHERE email LIKE '%@ex.com';  -- ends with
SELECT * FROM users WHERE name  LIKE 'A%';        -- starts with
SELECT * FROM users WHERE name  LIKE '_ob';       -- _ = exactly one char

-- NULL checks — always use IS NULL, never = NULL
SELECT * FROM orders WHERE user_id IS NOT NULL;

1.3ORDER BY

SQL

SELECT * FROM orders ORDER BY amount DESC;
SELECT * FROM orders ORDER BY created_at ASC, amount DESC;  -- multi-column sort

1.4DISTINCT

SQL

-- Unique countries
SELECT DISTINCT country FROM users;

-- Count of unique countries
SELECT COUNT(DISTINCT country) AS unique_countries FROM users;

1.5GROUP BY + Aggregate Functions

Aggregate functions: COUNT, SUM, AVG, MIN, MAX

SQL

-- Orders per user
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;

-- Revenue per user (completed only)
SELECT user_id, SUM(amount) AS total_revenue, AVG(amount) AS avg_order, MAX(amount) AS biggest_order, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY user_id
ORDER BY total_revenue DESC;

Key RuleEvery column in SELECT must either be in GROUP BY or wrapped in an aggregate.

1.6HAVING — filter after aggregation

WHERE filters individual rows (before GROUP BY). HAVING filters groups (after GROUP BY).

SQL

-- Users with more than 1 completed order
SELECT user_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) > 1
ORDER BY completed_orders DESC;

TrapYou can't use SELECT aliases in HAVING.
Wrong: HAVING completed_orders > 1
Right: HAVING COUNT(*) > 1

1.7LIMIT / OFFSET (pagination)

SQL

-- Top 3 biggest orders
SELECT * FROM orders ORDER BY amount DESC LIMIT 3;

-- Page 2 (rows 4-6)
SELECT * FROM orders ORDER BY id LIMIT 3 OFFSET 3;

✎Practice Problems

P1 List all users from the US, ordered by signup_date newest first.

P2 How many orders are in each status?

P3 Which users have total order amount > 2000? Show user_id and total.

P4 Find the average order amount for completed orders per country.

Module 2

JOINs Deep Dive

INNER, LEFT, RIGHT, FULL, CROSS, SELF joins & anti-join patterns

Mental Model

JOINs combine rows from two tables based on a condition. Think of it as a Venn diagram:
INNER JOIN  = intersection only
LEFT JOIN   = left table + intersection (NULLs for right missing rows)
RIGHT JOIN  = right table + intersection (rare in practice, flip to LEFT)
FULL JOIN   = everything, NULLs where no match
CROSS JOIN  = cartesian product (every combo) -- expensive!
SELF JOIN   = table joined to itself

2.1INNER JOIN — only matching rows

Orders with user names (only users who have orders).

SQL

SELECT o.id AS order_id, u.name, o.amount, o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

2.2LEFT JOIN — all left rows, NULLs for missing right

All users, even those without orders.

SQL

SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

2.3Finding rows with NO match (anti-join pattern)

Classic interview question: "Find users who have never placed an order."

SQL

SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Alternative using NOT IN (careful with NULLs)
SELECT id, name
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- Alternative using NOT EXISTS (safest with NULLs)
SELECT id, name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

TrapNOT IN silently returns zero rows if the subquery contains any NULL values in user_id. NOT EXISTS doesn't have this problem — prefer it for anti-joins.

2.4Multiple JOINs

Order details: user name + product name + quantity.

SQL

SELECT u.name AS buyer, p.name AS product, oi.quantity, oi.unit_price,
       (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

2.5SELF JOIN — table joined to itself

Useful for hierarchical data (employee->manager) or comparing rows. Here: users who signed up on the same day as another user.

SQL

SELECT a.name AS user1, b.name AS user2, a.signup_date
FROM users a
JOIN users b ON a.signup_date = b.signup_date AND a.id < b.id;

Key RuleThe a.id < b.id condition prevents duplicate pairs (a,b) and (b,a) and stops a user from matching itself.

2.6CROSS JOIN — cartesian product

Every user x every product.

SQL

SELECT u.name, p.name AS product
FROM users u
CROSS JOIN products p;

2.7Aggregation across JOINed tables

Total revenue per country (completed orders only).

SQL

SELECT u.country,
       COUNT(DISTINCT u.id) AS user_count,
       COUNT(o.id) AS order_count,
       SUM(o.amount) AS total_revenue,
       AVG(o.amount) AS avg_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.country
ORDER BY total_revenue DESC NULLS LAST;

Key RuleA condition in ON vs WHERE behaves differently for LEFT JOINs.
ON — filter during join; missing rows still appear (with NULLs).
WHERE — filter after join; missing rows get eliminated.

2.8JOIN vs WHERE — execution mental model

SQL

-- IMPLICIT JOIN (old style -- avoid)
SELECT u.name, o.amount
FROM users u, orders o
WHERE u.id = o.user_id;

-- EXPLICIT JOIN (preferred)
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

✎Practice Problems

P1 List every product that has been ordered at least once. Show product name, category, and total units sold.

Answer

SELECT p.name, p.category, SUM(oi.quantity) AS units_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
GROUP BY p.id, p.name, p.category
ORDER BY units_sold DESC;

P2 Find users who have only 'cancelled' or 'pending' orders (no completed).

P3 Show the top-selling product category by revenue.

Answer

SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY revenue DESC
LIMIT 1;

P4 (HARD) Find users who ordered the same product as user_id=1. Don't include user 1 in the result.

Answer

SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_id IN (
  SELECT oi2.product_id
  FROM orders o2
  JOIN order_items oi2 ON o2.id = oi2.order_id
  WHERE o2.user_id = 1
)
AND u.id != 1;

Module 3

Window Functions

ROW_NUMBER, RANK, LAG/LEAD, running totals, NTILE, FIRST_VALUE, PERCENT_RANK

Mental Model

Window functions compute values ACROSS rows related to the current row
WITHOUT collapsing them -- unlike GROUP BY, every input row survives in the output.

3.1ROW_NUMBER, RANK, DENSE_RANK

SQL

-- ROW_NUMBER: sequential, no ties
SELECT user_id, amount, status,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM orders;

-- RANK: ties get same rank, then gap
-- DENSE_RANK: ties get same rank, no gap
SELECT user_id, amount,
       RANK() OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM orders;

Add PARTITION BY to rank within groups instead of across the whole table.

SQL

-- Per-partition ranking: rank each user's orders by amount
SELECT user_id, id AS order_id, amount,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS user_rank
FROM orders;

-- Get each user's MOST RECENT order
SELECT user_id, order_id, amount, created_at
FROM (
  SELECT user_id, id AS order_id, amount, created_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

Key RuleTo get "top N per group", wrap the windowed query in a subquery and filter on the rank column in the outer WHERE — you can't filter on a window function directly in the same SELECT's WHERE clause.

3.2LAG / LEAD — access previous/next row

Revenue vs previous order for each user.

SQL

SELECT user_id, id AS order_id, amount, created_at,
       LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_amount,
       LEAD(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS next_order_amount,
       amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS delta
FROM orders
WHERE status = 'completed'
ORDER BY user_id, created_at;

3.3Running totals and moving averages

SQL

-- Running total of revenue per user
SELECT user_id, created_at, amount,
       SUM(amount) OVER (
         PARTITION BY user_id ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status = 'completed';

-- 3-order moving average
SELECT user_id, created_at, amount,
       AVG(amount) OVER (
         PARTITION BY user_id ORDER BY created_at
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM orders
WHERE status = 'completed';

Cumulative % of total revenue — combine a running-total frame with an unbounded (whole-partition) frame in the same query.

SQL

SELECT user_id, amount,
       SUM(amount) OVER (
         ORDER BY amount DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative,
       SUM(amount) OVER () AS grand_total,
       ROUND(
         100.0 * SUM(amount) OVER (
           ORDER BY amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) / SUM(amount) OVER (),
         1
       ) AS cum_pct
FROM orders
WHERE status = 'completed';

Key RuleFrame clause cheat sheet:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW → running total
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 3-row moving window
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING → whole partition

3.4NTILE — buckets/percentiles

Divide users into 3 revenue tiers.

SQL

SELECT user_id, SUM(amount) AS total,
       NTILE(3) OVER (ORDER BY SUM(amount) DESC) AS tier
FROM orders
WHERE status = 'completed'
GROUP BY user_id;

3.5FIRST_VALUE / LAST_VALUE

Each order annotated with the user's first order amount.

SQL

SELECT user_id, id AS order_id, amount, created_at,
       FIRST_VALUE(amount) OVER (
         PARTITION BY user_id ORDER BY created_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS first_order_amount
FROM orders;

3.6PERCENT_RANK / CUME_DIST

SQL

SELECT user_id, amount,
       PERCENT_RANK() OVER (ORDER BY amount) AS pct_rank,
       CUME_DIST() OVER (ORDER BY amount) AS cum_dist
FROM orders;

✎Practice Problems

P1 For each user, find the order where they spent the most.

Answer

SELECT user_id, order_id, amount, created_at
FROM (
  SELECT user_id, id AS order_id, amount, created_at,
         RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk
  FROM orders
  WHERE status = 'completed'
) t
WHERE rnk = 1;

P2 Find users whose most recent order was smaller than their previous order.

Answer

SELECT user_id
FROM (
  SELECT user_id, amount,
         LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS prev,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
  WHERE status = 'completed'
) t
WHERE rn = 1 AND amount < prev;

P3 Compute month-over-month revenue change.

Answer

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  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))
         / LAG(revenue) OVER (ORDER BY month),
         1
       ) AS change_pct
FROM monthly;

P4 (HARD) Find the top-2 products by revenue per category.

Answer

SELECT category, product_name, revenue
FROM (
  SELECT p.category, p.name AS product_name,
         SUM(oi.quantity * oi.unit_price) AS revenue,
         RANK() OVER (
           PARTITION BY p.category
           ORDER BY SUM(oi.quantity * oi.unit_price) DESC
         ) AS rnk
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  JOIN orders o ON oi.order_id = o.id
  WHERE o.status = 'completed'
  GROUP BY p.category, p.id, p.name
) t
WHERE rnk <= 2;

Module 4

Aggregations, Subqueries & CTEs

Scalar, inline & correlated subqueries, EXISTS / NOT EXISTS, CTEs, recursive CTEs, ROLLUP

Mental Model

A subquery is just a query nested inside another. Three shapes:
- Scalar    -> returns ONE value, usable anywhere an expression is (SELECT list, WHERE)
- Inline    -> returns a table, used in FROM/JOIN like a temp table
- Correlated -> references the OUTER query, re-runs once PER ROW (can be slow)
CTEs (WITH ...) don't change what's possible -- they just name a subquery so the query reads top-to-bottom.

4.1Scalar Subquery (returns a single value)

Orders above the average completed-order amount.

SQL

SELECT id, user_id, amount
FROM orders
WHERE amount > (
  SELECT AVG(amount) FROM orders WHERE status = 'completed'
);

Annotate each order with the overall average, right in the SELECT list.

SQL

SELECT
  id, user_id, amount,
  (SELECT AVG(amount) FROM orders WHERE status = 'completed') AS avg_amount,
  amount - (SELECT AVG(amount) FROM orders WHERE status = 'completed') AS above_avg
FROM orders
WHERE status = 'completed';

4.2Inline View (subquery in FROM)

Top users by spend, using a derived table.

SQL

SELECT u.name, t.total
FROM users u
JOIN (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
) t ON u.id = t.user_id
ORDER BY t.total DESC;

4.3Correlated Subquery (references the outer query)

Users who spent more than the average spend across ALL users.

SQL

SELECT u.id, u.name, user_total
FROM (
  SELECT user_id, SUM(amount) AS user_total
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
) t
JOIN users u ON u.id = t.user_id
WHERE user_total > (
  SELECT AVG(sub_total)
  FROM (
    SELECT user_id, SUM(amount) AS sub_total
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
  ) avg_t
);

Each user's last order date (true correlated subquery — the inner query references u.id from the outer row).

SQL

SELECT u.name,
  (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

TrapA correlated subquery runs once per outer row. On large tables this can be much slower than an equivalent JOIN — check the execution plan before shipping one.

4.4EXISTS / NOT EXISTS

Users who have at least one completed order.

SQL

SELECT id, name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'completed'
);

Users who have NEVER placed an order.

SQL

SELECT id, name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Key RuleEXISTS only checks for row presence — it never looks at the values inside the subquery's SELECT list. SELECT 1 is idiomatic because the actual columns don't matter.

4.5CTEs — the WITH clause

A single CTE gives the derived table a name up front, so the main query reads cleanly.

SQL

WITH user_revenue AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
)
SELECT u.name, ur.total
FROM users u
JOIN user_revenue ur ON u.id = ur.user_id
ORDER BY ur.total DESC;

Chained CTEs — each one can reference any CTE defined before it.

SQL

WITH user_revenue AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
),
avg_revenue AS (
  SELECT AVG(total) AS avg_total FROM user_revenue
),
top_users AS (
  SELECT user_id, total
  FROM user_revenue, avg_revenue
  WHERE total > avg_total
)
SELECT u.name, t.total
FROM users u
JOIN top_users t ON u.id = t.user_id;

4.6Recursive CTE

Generate a date series (first 7 days of 2023) — the anchor member seeds the recursion, the recursive member repeats until its WHERE stops matching.

SQL

WITH RECURSIVE date_series AS (
  SELECT DATE '2023-01-01' AS d
  UNION ALL
  SELECT d + INTERVAL '1 day'
  FROM date_series
  WHERE d < DATE '2023-01-07'
)
SELECT d FROM date_series;

4.7Advanced Aggregation — ROLLUP, FILTER, CASE

ROLLUP adds subtotal and grand-total rows automatically.

SQL

SELECT country, status, SUM(o.amount) AS revenue
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY ROLLUP (country, status)
ORDER BY country, status;

FILTER (PostgreSQL) — conditional aggregates without CASE.

SQL

SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

The same thing with CASE WHEN — works in every SQL dialect, not just Postgres.

SQL

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

✎Practice Problems

P1 Use a CTE to find the 2nd highest revenue user.

Answer

WITH user_revenue AS (
  SELECT user_id, SUM(amount) AS total,
    DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
)
SELECT user_id, total
FROM user_revenue
WHERE rnk = 2;

P2 Find products that have been ordered MORE THAN the average number of times.

Answer

WITH product_order_counts AS (
  SELECT product_id, COUNT(DISTINCT order_id) AS times_ordered
  FROM order_items
  GROUP BY product_id
)
SELECT p.name, poc.times_ordered
FROM products p
JOIN product_order_counts poc ON p.id = poc.product_id
WHERE poc.times_ordered > (
  SELECT AVG(times_ordered) FROM product_order_counts
);

P3 (HARD) Using a recursive CTE, generate a "day of week" label for each order.

P4 (HARD) Find users who placed orders in 3 consecutive months.

Answer

WITH monthly_orders AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', created_at) AS order_month
  FROM orders
  WHERE status = 'completed'
),
with_lag AS (
  SELECT user_id, order_month,
    LAG(order_month, 1) OVER (PARTITION BY user_id ORDER BY order_month) AS prev1,
    LAG(order_month, 2) OVER (PARTITION BY user_id ORDER BY order_month) AS prev2
  FROM monthly_orders
)
SELECT DISTINCT user_id
FROM with_lag
WHERE order_month = prev1 + INTERVAL '1 month'
  AND prev1 = prev2 + INTERVAL '1 month';

Module 5

Advanced Patterns

CASE WHEN, PIVOT, NULL handling, date/string ops, set operations, query optimization, UPSERT

Mental Model

Most "advanced" SQL is just CASE WHEN wearing different hats:
- Bucketing a value       -> CASE WHEN ... THEN ... END
- Pivoting rows to columns -> SUM(CASE WHEN col = 'x' THEN val ELSE 0 END)
- Conditional counting     -> COUNT(CASE WHEN ... THEN 1 END)
Everything else in this module (NULLs, dates, strings, set ops) is about shaping data
that's already been aggregated or filtered.

5.1CASE WHEN — conditional logic in SQL

Simple bucketing.

SQL

SELECT id, amount,
  CASE
    WHEN amount < 200 THEN 'small'
    WHEN amount < 1000 THEN 'medium'
    ELSE 'large'
  END AS order_tier
FROM orders;

Conditional aggregation (pivot-style) — one row per user, one column per status.

SQL

SELECT
  user_id,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_rev,
  SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_rev,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count
FROM orders
GROUP BY user_id;

5.2PIVOT / CROSSTAB

Revenue by country x status, using the same conditional-aggregation pattern as 5.1.

SQL

SELECT
  u.country,
  SUM(CASE WHEN o.status = 'completed' THEN o.amount ELSE 0 END) AS completed,
  SUM(CASE WHEN o.status = 'pending' THEN o.amount ELSE 0 END) AS pending,
  SUM(CASE WHEN o.status = 'cancelled' THEN o.amount ELSE 0 END) AS cancelled,
  SUM(o.amount) AS grand_total
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.country
ORDER BY grand_total DESC;

5.3NULL Handling

COALESCE returns the first non-NULL value in its argument list.

SQL

SELECT id, COALESCE(amount, 0) AS safe_amount FROM orders;

NULLIF returns NULL when its two arguments are equal — the standard trick for avoiding divide-by-zero.

SQL

SELECT user_id, total / NULLIF(order_count, 0) AS avg_order
FROM (
  SELECT user_id, SUM(amount) AS total, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) t;

Traptotal / order_count throws a divide-by-zero error the moment any group has 0 rows counted a different way (e.g. a filtered COUNT). Wrapping the denominator in NULLIF(..., 0) turns that error into a harmless NULL result instead.

5.4Date / Time Operations

Extract parts of a timestamp.

SQL

SELECT
  id, created_at,
  EXTRACT(YEAR FROM created_at) AS yr,
  EXTRACT(MONTH FROM created_at) AS mo,
  EXTRACT(DOW FROM created_at) AS dow
FROM orders;

Truncate to a granularity — the classic "group by month" building block.

SQL

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

Date arithmetic.

SQL

SELECT
  id, created_at,
  created_at + INTERVAL '30 days' AS expires_at,
  CURRENT_DATE - created_at::DATE AS days_ago
FROM orders;

5.5String Operations

SQL

SELECT
  name,
  LOWER(name) AS lower_name,
  UPPER(name) AS upper_name,
  LENGTH(name) AS len,
  SUBSTRING(name, 1, 3) AS first3,
  CONCAT(name, ' (', country, ')') AS label,
  TRIM('  Alice  ') AS trimmed,
  REPLACE(email, '@ex.com', '') AS username
FROM users;

5.6Set Operations — UNION, INTERSECT, EXCEPT

Users who placed orders in June OR July.

SQL

SELECT DISTINCT user_id FROM orders
WHERE created_at >= '2023-06-01' AND created_at < '2023-07-01'
UNION
SELECT DISTINCT user_id FROM orders
WHERE created_at >= '2023-07-01' AND created_at < '2023-08-01';

Users in BOTH months — swap UNION for INTERSECT.

SQL

SELECT DISTINCT user_id FROM orders
WHERE created_at >= '2023-06-01' AND created_at < '2023-07-01'
INTERSECT
SELECT DISTINCT user_id FROM orders
WHERE created_at >= '2023-07-01' AND created_at < '2023-08-01';

Users who ordered in June but NOT July — swap in EXCEPT.

SQL

SELECT DISTINCT user_id FROM orders
WHERE created_at >= '2023-06-01' AND created_at < '2023-07-01'
EXCEPT
SELECT DISTINCT user_id FROM orders
WHERE created_at >= '2023-07-01' AND created_at < '2023-08-01';

Key RuleUNION / INTERSECT / EXCEPT all require the same number of columns with compatible types in both queries. Use UNION ALL instead of UNION when you don't need duplicates removed — it skips the dedup pass and is faster.

5.7Query Optimization Mental Models

EXPLAIN ANALYZE shows the actual execution plan a query used — always check it before assuming a query is "slow because SQL."

SQL

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Key Rule

Index usage — good: WHERE user_id = 1 (uses an index directly). Bad: WHERE YEAR(created_at) = 2023 (wrapping a column in a function blocks index usage).
Avoid SELECT * — only pull the columns you actually need; it reduces I/O and network transfer.
Filter early — push WHERE conditions down so fewer rows flow through JOINs and aggregations.

5.8UPSERT patterns

PostgreSQL INSERT ON CONFLICT — insert a row, or update it if the conflicting key already exists.

SQL

INSERT INTO users (id, name, email, signup_date, country)
VALUES (1, 'Alice Updated', 'alice@ex.com', '2023-01-10', 'US')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

✎Practice Problems

P1 Create a report showing daily revenue for July 2023. Columns: date, revenue, 7-day moving average.

Answer

WITH daily AS (
  SELECT DATE_TRUNC('day', created_at) AS day, SUM(amount) AS revenue
  FROM orders
  WHERE created_at >= '2023-07-01' AND created_at < '2023-08-01' AND status = 'completed'
  GROUP BY 1
)
SELECT day, revenue,
  AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily
ORDER BY day;

P2 For each user, show their first purchase category.

P3 Find sessions where a user viewed a page AND purchased within the same session.

Answer

SELECT DISTINCT e.user_id, e.session_id
FROM events e
WHERE e.event_type = 'page_view'
  AND EXISTS (
    SELECT 1 FROM events e2
    WHERE e2.user_id = e.user_id
      AND e2.session_id = e.session_id
      AND e2.event_type = 'purchase'
  );

Module 6

Hard Interview Problems

10 classic problems that separate good from great — solved end to end

Mental Model

These are the classic problems that separate good from great.
Each one below is fully worked: the question you'd hear in an interview, followed by the complete solution.

H1Nth Highest Value

"Find the 2nd highest order amount."

SQL

SELECT DISTINCT amount
FROM (
  SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk
  FROM orders
) t
WHERE rnk = 2;

H2Consecutive Days / Streak Detection

"Find users who logged in for at least 3 consecutive days." (Island detection)

SQL

WITH login_days AS (
  SELECT DISTINCT user_id, event_time::DATE AS login_date
  FROM events
),
with_groups AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::INT AS grp
  FROM login_days
),
streak_lengths AS (
  SELECT user_id, grp, COUNT(*) AS streak_len
  FROM with_groups
  GROUP BY user_id, grp
)
SELECT DISTINCT user_id
FROM streak_lengths
WHERE streak_len >= 3;

H3Session Analysis

"Group events into sessions where gap > 30 min = new session"

SQL

WITH with_prev AS (
  SELECT user_id, event_time,
         LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM events
),
with_session_start AS (
  SELECT user_id, event_time,
         CASE WHEN prev_time IS NULL OR event_time - prev_time > INTERVAL '30 minutes'
              THEN 1 ELSE 0 END AS is_new_session
  FROM with_prev
),
with_session_id AS (
  SELECT user_id, event_time,
         SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM with_session_start
)
SELECT user_id, session_id,
       MIN(event_time) AS session_start,
       MAX(event_time) AS session_end,
       COUNT(*) AS events_in_session,
       MAX(event_time) - MIN(event_time) AS duration
FROM with_session_id
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

H4Retention Analysis (Cohort)

"What % of users who signed up in month M placed an order in month M+1?"

SQL

WITH cohort AS (
  SELECT u.id AS user_id, DATE_TRUNC('month', u.signup_date) AS cohort_month
  FROM users u
),
orders_by_month AS (
  SELECT user_id, DATE_TRUNC('month', created_at) AS order_month
  FROM orders
  WHERE status = 'completed'
),
retention AS (
  SELECT c.cohort_month,
         COUNT(DISTINCT c.user_id) AS cohort_size,
         COUNT(DISTINCT o.user_id) AS retained_next_month
  FROM cohort c
  LEFT JOIN orders_by_month o
    ON c.user_id = o.user_id
    AND o.order_month = c.cohort_month + INTERVAL '1 month'
  GROUP BY c.cohort_month
)
SELECT cohort_month, cohort_size, retained_next_month,
       ROUND(100.0 * retained_next_month / NULLIF(cohort_size, 0), 1) AS retention_pct
FROM retention
ORDER BY cohort_month;

H5Median

No built-in MEDIAN function in standard SQL — this is the portable way to compute it.

SQL

WITH ordered AS (
  SELECT amount,
         ROW_NUMBER() OVER (ORDER BY amount) AS rn,
         COUNT(*) OVER () AS total
  FROM orders
  WHERE status = 'completed'
)
SELECT AVG(amount) AS median
FROM ordered
WHERE rn IN (FLOOR((total + 1) / 2.0), CEIL((total + 1) / 2.0));

H6De-duplication

Keep the latest record per key.

SQL

SELECT user_id, event_type, event_time
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, event_type
           ORDER BY event_time DESC
         ) AS rn
  FROM events
) t
WHERE rn = 1;

H7Running Balance / Account Overdraft Detection

SQL

WITH transactions AS (
  SELECT user_id, created_at,
         CASE status
           WHEN 'completed' THEN amount
           WHEN 'cancelled' THEN -amount
           ELSE 0
         END AS txn_amount
  FROM orders
),
running AS (
  SELECT user_id, created_at, txn_amount,
         SUM(txn_amount) OVER (
           PARTITION BY user_id ORDER BY created_at
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_balance
  FROM transactions
)
SELECT DISTINCT user_id
FROM running
WHERE running_balance < 0;

H8Funnel Analysis

SQL

WITH page_viewers AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_type = 'page_view'
),
clickers AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN page_viewers pv ON e.user_id = pv.user_id
  WHERE e.event_type = 'click'
),
purchasers AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN clickers c ON e.user_id = c.user_id
  WHERE e.event_type = 'purchase'
)
SELECT
  (SELECT COUNT(*) FROM page_viewers) AS step1_views,
  (SELECT COUNT(*) FROM clickers) AS step2_clicks,
  (SELECT COUNT(*) FROM purchasers) AS step3_purchases,
  ROUND(100.0 * (SELECT COUNT(*) FROM clickers) / NULLIF((SELECT COUNT(*) FROM page_viewers), 0), 1) AS view_to_click_pct,
  ROUND(100.0 * (SELECT COUNT(*) FROM purchasers) / NULLIF((SELECT COUNT(*) FROM page_viewers), 0), 1) AS overall_conversion_pct;

H9Gaps and Islands

Find gaps in a sequence of dates.

SQL

WITH daily_order_dates AS (
  SELECT DISTINCT created_at::DATE AS order_date
  FROM orders
),
with_next AS (
  SELECT order_date,
         LEAD(order_date) OVER (ORDER BY order_date) AS next_order_date
  FROM daily_order_dates
)
SELECT order_date AS gap_start,
       next_order_date AS gap_end,
       next_order_date - order_date - 1 AS gap_days
FROM with_next
WHERE next_order_date - order_date > 1;

H10Many-to-Many: Users Who Bought ALL Products in a Category

SQL

WITH digital_products AS (
  SELECT id FROM products WHERE category = 'digital'
),
user_digital_purchases AS (
  SELECT DISTINCT o.user_id, oi.product_id
  FROM orders o
  JOIN order_items oi ON o.id = oi.order_id
  WHERE oi.product_id IN (SELECT id FROM digital_products)
    AND o.status = 'completed'
)
SELECT user_id
FROM user_digital_purchases
GROUP BY user_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM digital_products);

☰Quick Reference: Most Common Interview Patterns

Top-N Per Group

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

Deduplication

ROW_NUMBER() rn=1

Consecutive/Streak

date - ROW_NUMBER() = const island trick

LAG/LEAD Comparison

prev/next row comparison for trends

Anti-Join

LEFT JOIN + IS NULL or NOT EXISTS

Running Total

SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)

Funnel

chained CTEs, each qualifying prior step

Cohort Retention

JOIN on cohort_month + interval

Median

PERCENTILE_CONT or ROW_NUMBER + AVG

Divide-by-Zero

NULLIF(denominator, 0)

Test Yourself

Mock Interview: 10 Timed SQL Problems

No peeking at answers. Set a timer for each problem before you start.

Rules

Set a timer. Aim for 5–8 min per problem. No looking back at the modules mid-problem — simulate real interview pressure.

DBSchema Reminder

users

id
name
email
signup_date
country

orders

id
user_id
amount
status
created_at

products

id
name
category
price

order_items

order_id
product_id
quantity
unit_price

events

id
user_id
event_type
event_time
session_id

Warm-up2–3 min each · 2 pts each

Find the total revenue per country for completed orders. Sort by revenue descending.

List users who have never placed any order.

Core5–7 min each · 3 pts each

For each user, show their most expensive single order. Include user name, order_id, and amount.

Find the top 3 best-selling products by total revenue (quantity × unit_price). Include product name, category, total units sold, and total revenue.

Show month-over-month revenue growth. Columns: month, revenue, previous_month_revenue, growth_pct. Round to 1 decimal.

Hard8–12 min each · 4 pts each

Funnel: Among users who had a page_view, what % also had a purchase event? Show both counts and the conversion rate.

Find users who placed orders in at least 2 different months. Show user_id, name, and the list of months ordered.

For each product, show: product name, its rank by revenue within its category, and whether it's in the top 2 of that category (yes/no).

Brutal10+ min each · 5 pts each · real FAANG-style

Detect "churned" users: users who placed orders in the first 3 months of data but placed NO orders in the last 3 months of data. Return user_id and name.

Q10

For each user, compute their "average time between orders" (in days). Only include users with 2+ orders. Sort by avg_days_between_orders ascending.

%Scoring Guide

Questions	Points	Tier
Q1–Q2	2 pts each	Warm-up
Q3–Q5	3 pts each	Core
Q6–Q8	4 pts each	Hard
Q9–Q10	5 pts each	Brutal

Total: 35 pts

28+Interview-ready

20–27Good — review the hard patterns

< 20Drill modules 3 and 4 again