Skip to content
Ameen Abozaid
Englishالعربية
MID-011

SQL mastery

  • 7 Lessons
  • 5 h practice (estimate)
Start module
On this page 8 topics

Overview

Topics

  • CTEs
  • recursive queries
  • window functions
  • advanced joins
  • set operations

Learn first

5 h of practice (estimate)

Learning outcomes

  1. Structure complex queries with CTEs and write recursive CTEs for hierarchies.
  2. Use window functions (ROW_NUMBER, RANK, LAG, running totals, partitions).
  3. Use set operations, lateral joins, and FILTER clauses.
  4. Write upserts with INSERT … ON CONFLICT.

Lessons

  1. 01CTEs for readable queries
  2. 02Recursive CTEs
  3. 03Window functions
  4. 04Advanced joins and LATERAL
  5. 05Set operations and FILTER
  6. 06Upserts
  7. 07Demo: building the monthly sales report in SQL

Lab and exercises

Lab

MID-011-LAB1 · Analytics queries

Outcomes: 1–4 · Time: ~3 h Using the Shop schema with generated data, write: (a) top 3 products per category per month, (b) customer retention cohorts (first-order month vs months since), (c) the category tree with totals including all descendants. Write a plain Java implementation of each to cross-check. Acceptance: one SQL query per report; results match the Java implementation on the same data.

Exercises (assessment: timed SQL test plus these exercises)

IDOutcomeTask
MID-011-EX011Rewrite a three-level nested subquery as chained CTEs.
MID-011-EX021List every descendant of category 5 with its depth using a recursive CTE.
MID-011-EX032For each customer, number orders by date and show days since the previous order.
MID-011-EX042Explain the difference between ROW_NUMBER, RANK, and DENSE_RANK on tied values 10, 10, 8.
MID-011-EX053For each category, get the 2 newest products using LATERAL.
MID-011-EX063Produce paid, cancelled, and refunded counts per month in one query with FILTER.
MID-011-EX074Write an idempotent upsert for product_stock(product_id, qty) that adds to the quantity.
Show solutions
  • EX01 WITH a AS (...), b AS (SELECT ... FROM a ...), c AS (...) SELECT ... FROM c; each step named.
  • EX02 Anchor WHERE id = 5, 0 AS depth; recursive part joins category c ON c.parent_id = t.id, depth + 1.
  • EX03 row_number() OVER (PARTITION BY customer_id ORDER BY created_at) and created_at - lag(created_at) OVER (PARTITION BY customer_id ORDER BY created_at).
  • EX04 ROW_NUMBER 1,2,3; RANK 1,1,3; DENSE_RANK 1,1,2.
  • EX05 FROM category c CROSS JOIN LATERAL (SELECT ... WHERE category_id = c.id ORDER BY created_at DESC LIMIT 2) p.
  • EX06 date_trunc('month', created_at) with three count(*) FILTER (WHERE status = ...) columns, grouped by month.
  • EX07 INSERT ... VALUES (...) ON CONFLICT (product_id) DO UPDATE SET qty = product_stock.qty + EXCLUDED.qty.
  • The "window vs GROUP BY" and "aggregate in Java" mistakes are covered by EX03, EX06, and the lab cross-check.

Quiz

Outcomes: O1 CTEs and recursion · O2 window functions · O3 joins, set operations, FILTER · O4 upserts.

A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.

  1. 1A recursive CTE consists of:O1
  2. 2To avoid infinite recursion on cyclic data you can:O1
  3. 3In current PostgreSQL, a non-recursive CTE referenced once is usually:O1
  4. 4Unlike GROUP BY, a window function:O2
  5. 5RANK on values 10, 10, 8 gives:O2
  6. 6To filter on row_number() = 1 you must:O2
  7. 7LATERAL lets a subquery:O3
  8. 8Faster set operation when duplicates do not matter:O3
  9. 9count(*) FILTER (WHERE x) is mainly a cleaner form of:O3
  10. 10In ON CONFLICT DO UPDATE, EXCLUDED refers to:O4

Practical part

Timed SQL test (three queries from the lab's families) plus exercises MID-011-EX01 to EX07 and the lab. Pass: quiz 8/10, the timed test answered correctly, and lab results matching the Java check.

Common mistakes

Pulling rows into Java to aggregate; window functions confused with GROUP BY.

Assessment

Timed SQL test.

Career relevance

Reporting, data platforms (SRB-007).

Type to search levels, phases, modules and lessons.