SQLRepPracticeMock interviewGuidesPlaygroundLearning path
Interview questionsPostgres errorsCheat sheetsTermsPrivacyRefundssupport@sqlrep.com
© SQLRep · Practice SQL online, right in your browser.

Cheat sheets · Module 6

Interview Classics cheat sheet

Recognize a classic interview question and know the pattern to use.

Self-join

A self-join joins a table to itself under two aliases, so one row can be compared with a related row in the same table. For a manager hierarchy: FROM employees e JOIN employees m ON m.id = e.manager_id pairs every employee with their manager.

SELECT c.name AS category, p.name AS parent
FROM categories c
JOIN categories p ON p.id = c.parent_id;
Practice: Earning more than their manager →

GROUP BY · HAVING to find duplicates

To find duplicates, GROUP BY every column that defines 'the same thing' and keep groups with HAVING COUNT(*) > 1. Leaving a column out merges rows that aren't really duplicates.

SELECT email, COUNT(*) AS copies
FROM signups
GROUP BY email
HAVING COUNT(*) > 1;
Practice: Duplicate card charges →

COUNT(DISTINCT) · funnel steps

A funnel counts how many different users reached each step. Users can repeat a step (view pricing twice), so count COUNT(DISTINCT user_id), not COUNT(*). To keep steps in a custom order, ORDER BY a CASE expression.

SELECT stage, COUNT(DISTINCT lead_id) AS leads
FROM pipeline
GROUP BY stage
ORDER BY CASE stage WHEN 'new' THEN 1 WHEN 'won' THEN 2 END;
Practice: Signup-to-purchase funnel →

Gaps and islands

Consecutive dates share a trick: subtract each row's ROW_NUMBER (per user, in date order) from its date and every day in the same streak lands on the same 'anchor' date. GROUP BY that anchor to measure each streak, then take the MAX per user.

WITH g AS (
  SELECT day,
    day - (ROW_NUMBER() OVER (ORDER BY day))::int AS anchor
  FROM active_days
)
SELECT MIN(day) AS start, COUNT(*) AS length
FROM g GROUP BY anchor;
Practice: Longest login streak →

Scalar subquery · MAX below the MAX

An aggregate with no GROUP BY always returns exactly one row, with NULL when nothing matches, while LIMIT/OFFSET returns zero rows when there's nothing at that position. So the classic 'second-highest, or NULL if there isn't one' is the MAX of everything below the MAX: a scalar subquery finds the top value, the outer MAX finds the next distinct one.

SELECT MIN(lap_time) AS second_fastest
FROM laps
WHERE lap_time > (SELECT MIN(lap_time) FROM laps);
Practice: Second-largest charge →

Aggregate of an aggregate · LEFT JOIN · COUNT(column)

Some questions need an aggregate of an aggregate: count per customer in a subquery, then GROUP BY that count to see how many customers have each count. To include customers with zero, start from customers and LEFT JOIN, and count a column of the joined table: COUNT(o.id) skips the NULL row a LEFT JOIN leaves, while COUNT(*) counts it as 1.

SELECT posts, COUNT(*) AS authors
FROM (
  SELECT author_id, COUNT(*) AS posts
  FROM blog_posts GROUP BY author_id
) AS per_author
GROUP BY posts
ORDER BY posts;
Practice: Customers by number of orders →

SUM(CASE) · signed amounts · LEFT JOIN + COALESCE

Ledgers usually store every amount as a positive number plus a type, so a balance is a signed sum: add credits and subtract debits with SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END). Users with no entries need a LEFT JOIN, and COALESCE(..., 0) turns their NULL sum into 0.

SELECT sku,
  SUM(CASE WHEN direction = 'in' THEN qty ELSE -qty END) AS on_hand
FROM stock_moves
GROUP BY sku;
Practice: Wallet balance from a ledger →

EXISTS · NOT EXISTS · per-customer anti-join

'Did A but never B' depends on a customer's whole history, so a WHERE on single rows can't answer it: item = 'a' AND item <> 'b' is true for every 'a' row. Check the history with EXISTS (...) for A and NOT EXISTS (...) for B, both correlated on the customer. EXCEPT between two lists of ids works too.

SELECT s.name
FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM enrolments e
  WHERE e.student_id = s.id AND e.course = 'Calculus'
);
Practice: Bought oat milk, never coffee beans →

Conditional ratio · JOIN filter · FILTER

A rate is a filtered count over a total: COUNT(*) FILTER (WHERE outcome = 'x') / COUNT(*). When some rows must be ignored entirely (test accounts, banned users), join the table that flags them and filter in WHERE, so they leave the numerator and the denominator. Multiply by 100.0 to avoid integer division.

SELECT s.carrier,
  ROUND(100.0 * COUNT(*) FILTER (WHERE s.late) / COUNT(*), 2) AS late_pct
FROM shipments s
JOIN merchants m ON m.id = s.merchant_id
WHERE NOT m.is_sandbox
GROUP BY s.carrier;
Practice: No-show rate without test accounts →

Self-join · a.item < b.item · COUNT(DISTINCT)

To find pairs inside a group, self-join the table on the group key. a.item < b.item keeps each unordered pair once (bagel + jam, never also jam + bagel) and drops an item paired with itself; <> would keep both orders. If an item can repeat inside a group, count distinct groups, not joined rows.

SELECT a.actor AS actor_1, b.actor AS actor_2, COUNT(*) AS films
FROM cast_list a
JOIN cast_list b ON b.film_id = a.film_id AND a.actor < b.actor
GROUP BY a.actor, b.actor;
Practice: Items bought together →

MIN first activity · LEFT JOIN on date + 1

Day-1 retention asks whether a user came back exactly one day after their first activity. Find each user's first date with MIN, then LEFT JOIN their activity on the same user and date = first_date + 1 (DATE + 1 is the next day in Postgres). The LEFT JOIN keeps users who never returned, and deduplicating activity first stops a busy day from counting twice.

WITH f AS (
  SELECT player_id, MIN(played_on) AS first_day
  FROM games GROUP BY player_id
)
SELECT player_id, first_day, first_day + 7 AS day_7
FROM f;
Practice: Day-1 return rate →

Self-join on date + 1 · consecutive-day trends

To compare a row with the next calendar days, self-join on the date: JOIN t d2 ON d2.key = d1.key AND d2.day = d1.day + 1. Unlike LAG, which just reads the previous row, a date join can't skip over a missing day. Chain a third alias for day + 2 and compare the values with a strict >.

SELECT a.city, a.day
FROM temps a
JOIN temps b ON b.city = a.city AND b.day = a.day + 1
WHERE b.high < a.high;
Practice: Three days of rising sales →

Practice every concept interactively at sqlrep.com