SQL — Mid · Resources

1All the joins

0/4▶

Mid SQL means every join type without thinking, including self-joins and the execution order.

Join fluency

INNER, LEFT, RIGHT, FULL, CROSS, and the self-join, each with an example.

SQLBolt — JOINs.

SELECT execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

SQLBolt — Order of execution.

Orders per customer including zero-order customers — LEFT JOIN + COUNT(orders.id).

W3Schools — LEFT JOIN.

Anti-join (rows in A not in B) via LEFT JOIN ... WHERE b.id IS NULL or NOT EXISTS.

PostgreSQL — joins.

2Subqueries & CTEs

0/4▶

How you keep a non-trivial query readable under pressure.

Compose queries

Subqueries in WHERE/FROM/SELECT; correlated vs non-correlated.

Mode — Subqueries.

CTEs (WITH) — when they read better than nested subqueries.

PostgreSQL — WITH.

Recursive CTEs (org chart, hierarchy traversal) — the intro.

PostgreSQL — recursive WITH.

EXISTS vs IN vs join — semantics and performance.

Use The Index, Luke.

3Query classics — likely live

0/6▶

The queries interviews reuse over and over. Be able to write each without hesitation.

Write these cold

Second-highest salary — subquery, OFFSET, and DENSE_RANK().

LeetCode #176.

Find / delete duplicates keeping one row.

LeetCode #182 — then #196 for delete.

Top-N per group with a window + outer filter.

PostgreSQL — Window.

Revenue per month — date bucketing + aggregate.

Mode — SQL tutorial.

Pivot rows to columns with conditional aggregation (SUM(CASE WHEN ...)).

Mode — CASE.

Consecutive / streak problems (logins N days in a row).

LeetCode — SQL 50.

4Window functions (intro)

0/3▶

The line between junior and mid. "Top N per group" is the signature use.

The essentials

ROW_NUMBER() / RANK() / DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...).

PostgreSQL — Window functions.

Running totals with SUM() OVER (ORDER BY ...).

Mode — Window functions.

Window vs GROUP BY — keep every row vs collapse.

Mode — Window functions.

5Views & conditional logic

0/3▶

Structuring reusable queries and expressing branching cleanly.

Structure & branch

Views & materialized views — a saved query vs stored results.

PostgreSQL — Views.

CASE expressions for bucketing and conditional aggregates.

Mode — CASE.

Date/time bucketing (DATE_TRUNC) for time-series rollups.

PostgreSQL — date/time.