The SQL 50 study guide

7-day SQL 50 study plan

About two hours a day. Each day: learn one idea here, then solve that LeetCode section. Before writing SQL, answer the five decode questions in a comment. Pick your dialect at the top; every snippet and solution follows it.

1. Day 1
   Learn: Execution order, Decode a question. Solve: Select 0 of 5 solved

   Step through the execution order twice, learn the five decode questions, then solve these five using only WHERE.

2. Day 2
   Learn: Which join. Solve: Basic Joins 0 of 9 solved

   Try every join type and filter in the playground first. The WHERE-vs-ON trap decides half of these.

3. Day 3
   Learn: Toolbox, MySQL vs PostgreSQL. Solve: Basic Aggregate Functions 0 of 8 solved

   Conditional aggregation and rates. In PostgreSQL, watch integer division and ROUND on floats.

4. Day 4
   Learn: WHERE vs HAVING, Phrase finder. Solve: Sorting and Grouping 0 of 7 solved

   Every problem here is a grain question. Write the output row in a comment before typing.

5. Day 5
   Learn: Window functions. Solve: Advanced Select and Joins 0 of 7 solved

   LAG, LEAD and running sums. Play with every function in the window playground first.

6. Day 6
   Learn: Subquery, CTE or window. Solve: Subqueries 0 of 7 solved

   Two-pass problems. Compute something in a CTE, then filter or aggregate it.

7. Day 7
   Learn: MySQL vs PostgreSQL, Quiz. Solve: Advanced String Functions, Regex, Clause 0 of 7 solved

   String and regex syntax differs most between dialects. Finish with the quiz.

8. Week 2
   Review: open All 50 problems and pick the Review filter. It lists problems marked Revisit and anything you solved over a week ago. Re-solve them without hints.

Decode a question before you type

Answer these five on paper. The query then almost writes itself, in the same order the database runs it.

1. What does one output row represent?Read the expected output, not the story. One row per user? Per product? Per (month, country)? This is the grain, and it decides your GROUP BY.
2. What is one row of each input table?If the input is finer than the output (many visits per customer), you need GROUP BY. If it matches, you probably don’t.
3. Which tables, and must unmatched rows survive?Columns in two tables means a join. Rows with no match must still appear (“0 if none”, “never did”) means LEFT JOIN. Otherwise INNER.
4. Filter rows or filter groups?Condition on a raw column goes in WHERE. Condition on a count, sum or average goes in HAVING. If a condition affects only a numerator, use CASE inside the aggregate instead of WHERE.
5. Does a row need another row’s value?Previous day, rank in group, running total, first per user. That means a window function, or a self join.

Which join, on what

Join on the column that means the same thing in both tables. Then ask one question: must rows without a match still appear?

Toolbox: what to use when

Four tools cover most of SQL 50. Each has one job.

SELECT class
FROM courses
WHERE student IS NOT NULL   -- rows
GROUP BY class
HAVING COUNT(*) >= 5;       -- groups

SELECT month,
  COUNT(*) AS trans_count,
  SUM(CASE WHEN state = 'approved'
      THEN 1 ELSE 0 END) AS approved_count,
  SUM(state = 'approved') AS same_in_mysql
FROM t GROUP BY month;SELECT month,
  COUNT(*) AS trans_count,
  SUM(CASE WHEN state = 'approved'
      THEN 1 ELSE 0 END) AS approved_count,
  COUNT(*) FILTER (WHERE state = 'approved') AS same_in_pg
FROM t GROUP BY month;

SELECT user_id,
  ROUND(IFNULL(AVG(action = 'confirmed'), 0), 2)
    AS confirmation_rate
FROM signups s
LEFT JOIN confirmations c USING (user_id)
GROUP BY user_id;SELECT user_id,
  ROUND(COALESCE(AVG((action = 'confirmed')::int), 0), 2)
    AS confirmation_rate
FROM signups s
LEFT JOIN confirmations c USING (user_id)
GROUP BY user_id;

SELECT *
FROM sales
WHERE (product_id, year) IN (
  SELECT product_id, MIN(year)
  FROM sales GROUP BY product_id
);

Subquery, CTE or window function?

Output polish (where easy marks are lost)

Window functions: GROUP BY that keeps every row

GROUP BY squashes rows into one per group. OVER computes a group value but leaves every row in place. PARTITION BY says what the group is. ORDER BY inside OVER says the order within it.

Phrase finder

Questions repeat the same phrases. Each phrase points at a tool. Tap one.

MySQL vs PostgreSQL for SQL 50

LeetCode accepts both. Most of SQL 50 is the same in each; these are the places where a correct idea fails on syntax or gives a different number.

Same in both: LIMIT … OFFSET, CTEs, window functions, tuple IN like (a, b) IN (SELECT …), CASE, % for remainder.

Check yourself

Eight scenarios. Pick the approach, then read why.

All 50 SQL problems

Set a status as you go. Each problem has its own page with hints one level at a time, a tested solution, and space for your notes and your own query. Everything is saved in this browser; export it to keep a backup or move it to another device.

Nothing here yet.