Practice & Interview Patterns

How to actually get good at SQL

Reading SQL ≠ writing SQL. Build the muscle by:

1. Solve 50–100 small problems. LeetCode SQL 50, HackerRank SQL Prep, StrataScratch. 2. Reproduce every query in two engines. Postgres + SQLite is a friendly pair; MySQL too if you'll use it at work. 3. Read EXPLAIN ANALYZE for every non-trivial query. Build intuition for what's expensive. 4. Ship one project. A tiny dashboard you query yourself beats 50 contrived problems.

A 4-week study plan

Week	Focus	Goal
1	SELECT, WHERE, ORDER, LIMIT, JOINs	finish LeetCode "Easy" SQL
2	GROUP BY, HAVING, subqueries, set ops	finish LeetCode "Medium" SQL up to 25
3	Window functions, CTEs, recursive	top-N-per-group + running totals on sight
4	DDL, constraints, indexes, transactions	design a schema + EXPLAIN tuning

The 8 interview classics

Every SQL interview pulls from this set. If you can write all eight cold, you're prepared.

1. Top-N per group

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;

2. Second highest (or N-th highest) salary

-- N = 2
SELECT MAX(salary) AS second_highest
FROM   salaries
WHERE  salary < (SELECT MAX(salary) FROM salaries);
-- generic with window
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM salaries
) t WHERE rk = 2;

3. Employees earning more than their manager

SELECT e.name
FROM   employees e
JOIN   employees m ON e.manager_id = m.id
WHERE  e.salary > m.salary;

4. Customers who never ordered (anti-join)

SELECT c.*
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE  o.id IS NULL;
-- Or:
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

5. Find duplicates

SELECT email, COUNT(*) AS n
FROM   users
GROUP  BY email
HAVING COUNT(*) > 1;
-- Or with a window function
SELECT * FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY email) AS n FROM users
) t WHERE n > 1;

6. Running total / cumulative sum

SELECT day, sales,
       SUM(sales) OVER (ORDER BY day
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM daily_sales;

7. Gaps and islands (consecutive runs)

-- Find runs of consecutive days a user logged in
SELECT user_id, MIN(day) AS island_start, MAX(day) AS island_end, COUNT(*) AS len
FROM (
  SELECT user_id, day,
         day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::INT AS grp
  FROM   logins
) t
GROUP BY user_id, grp
ORDER BY user_id, island_start;

The classic "subtract a row number from a date to identify a streak" trick.

8. Pivot — rows to columns

-- Pivot status counts by month
SELECT
  date_trunc('month', created_at) AS month,
  COUNT(*) FILTER (WHERE status = 'paid')    AS paid,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending,
  COUNT(*) FILTER (WHERE status = 'failed')  AS failed
FROM payments
GROUP BY 1
ORDER BY 1;

(SQL Server has PIVOT; MySQL uses SUM(CASE WHEN ...). The conditional-aggregate pattern is universal.)

Mini-project: ship a tiny analytics dashboard

Build the smallest possible end-to-end SQL project. Suggested schema:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY, email TEXT UNIQUE,
  country CHAR(2), created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  amount NUMERIC(10,2), status TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_created ON orders (created_at);

Seed with ~10k users + ~50k orders. Then write one query per KPI:

• DAU / WAU / MAU (COUNT(DISTINCT user_id) over time windows)
• Revenue per day, with 7-day moving average
• Top 10 countries by revenue (LEFT JOIN to keep zero-revenue countries)
• New vs returning customers per week
• Cohort retention table (users who signed up in week W still active in week W+N)
• Funnel — visited → added to cart → paid

Run EXPLAIN ANALYZE on each. Add the right index when something's slow. That's the entire job description for a junior data engineer.

Recommended platforms

Platform	Best for
LeetCode SQL 50	the curated 50-question warmup; answer all of them
LeetCode Database	full library, Easy → Hard
HackerRank SQL	beginner-friendly progression
StrataScratch	real interview questions from real companies
DataLemur	window-function and analytics-flavored interviews
SQLZoo	interactive in-browser practice
SQLBolt	the gentlest intro tutorial
PgExercises	one nicely-designed schema, dozens of problems
Mode Analytics SQL Tutorial	explanations + practice in one

Reference docs worth bookmarking

• Postgres docs — most thorough; the gold standard for SQL learning even if you use MySQL.
• Use The Index, Luke! — the canonical free indexing tutorial.
• MySQL 8.0 Reference Manual — for the differences when you actually run MySQL.
• SQL Style Guide (sqlstyle.guide) — readable formatting conventions.

Tips that compound

1. Always alias your tables (u, p, o) — multi-table queries become readable. 2. Format vertically. One column per line, keywords aligned. SELECT … FROM … on the same line is a smell. 3. Comment the *why*, never the *what*. Anyone can read what SUM(amount) does; explain why this exact metric. 4. Wrap multi-statement work in transactions. Even ad-hoc cleanup. 5. Check your DELETEs and UPDATEs by running them as SELECTs first. Same WHERE, same JOINs. 6. EXPLAIN ANALYZE before AND after an index change. 7. Save reusable queries (analytics, ETL) in your repo — version-controlled SQL beats institutional memory.

You've finished the SQL track 🏆

You can now:

• Read and write every common SQL statement (DDL + DML).
• Compose multi-table queries with joins, GROUP BY/HAVING, subqueries, set ops, window functions and CTEs.
• Design a schema with the right types, constraints and indexes.
• Reason about transactions, isolation levels and concurrency.
• Use EXPLAIN to make slow queries fast.

Next stop: build the mini-project above, push it to GitHub, and start grinding LeetCode SQL. Every SQL interview from junior to staff is now in scope.