SQLSQLiteAdvanced~6h

SQL: Advanced

The number the frame decided

Subqueries, set operations and CTEs, then window functions twice over — because the clause that decides what a running total added up is the one nobody writes. Generated questions that never run out, then a courier network in your own engine and five answers it has to give you.

Start this projectSee the modules
Modules
7
Drills
11
Build steps
8
Time
~6h

Walk out able to

Say what number a window puts in every row, frame included — and read an index and a query plan for what the engine could actually use.

What you drill

01

Query inside a query

Scalar, IN, EXISTS, anti-joins, UNION and EXCEPT.

02

Named steps, and loops

WITH pipelines, read twice, and recursion that halts.

03

Windows, and the frame

Ranks on ties, LAG and LEAD, ROWS against RANGE.

04

Indexes and plans

Which columns a search reaches, and what the plan says.

The pitch

What you practise, and what you leave with

A courier network in your own engine: 10 depots, 32 runs, and five answers only a query can give you.

You will practise

  • Subqueries, semi-joins and set operations
  • WITH pipelines and recursive CTEs
  • Ranking on ties, LAG and LEAD
  • Frames, index prefixes and plan trees

Afterwards you can

  • Name the frame nobody wrote, and what it adds up
  • Say when a join multiplies where a probe does not
  • Count what a recursion produces before it halts
  • Read a plan for the work it actually did

Modules

7 modules, 44 items

Lessons explain one idea. Drills repeat it until it sticks. Build steps make something that exists afterwards.

  1. 1

    Ask the table about its own answer

    ~40 min

    Filter a query by another query, and say how many rows survive it.

    • LessonWhat this project is about5 min
    • LessonA query used as a value6 min
    • LessonMembership, and existence8 min
    • LessonA query used as a table4 min
    • LessonTwo you should read, not write5 min
    • DrillCount what the subquery leaves12 min
  2. 2

    Subquery, join, or set operation

    ~41 min

    Say which of the three changes the row count, and which quietly doubles a total.

    • LessonThe join that doubled the total6 min
    • LessonThe semi-join and the anti-join7 min
    • LessonStacking whole results8 min
    • DrillSay which one multiplies11 min
    • DrillStack two results9 min
  3. 3

    Name each step with WITH

    ~44 min

    Read a long query top-down, and count what a recursion produces before it halts.

    • LessonRead a query top-down8 min
    • LessonA name for one statement only5 min
    • LessonOne keyword makes it a loop8 min
    • DrillFollow the pipeline12 min
    • DrillCount what the recursion produces11 min
  4. 4

    Aggregate without collapsing

    ~49 min

    Put a rank or a neighbour beside every row, instead of collapsing them all.

    • LessonA number beside every row8 min
    • LessonWhat lands on a tied row8 min
    • LessonRead a neighbouring row5 min
    • LessonWhere the filter has to go6 min
    • DrillRank the ties12 min
    • DrillLook one row back10 min
  5. 5

    The frame decides the number

    ~38 min

    Name the frame nobody wrote, and compute what any stated frame adds up.

    • LessonA running total that stands still5 min
    • LessonROWS counts rows, RANGE counts values8 min
    • LessonThe frames worth knowing6 min
    • LessonThe one that catches everybody5 min
    • DrillCompute what the frame sees14 min
  6. 6

    Indexes, and what the plan says

    ~58 min

    Say which columns of an index a query reaches, and read a plan for its row counts.

    • LessonAn index is a sorted list7 min
    • LessonWhere an inequality ends the run5 min
    • LessonWhen the table is never opened6 min
    • DrillCount the usable columns11 min
    • LessonThe plan is the program that ran8 min
    • LessonThe number that multiplies5 min
    • LessonWhere the estimate parted company6 min
    • DrillRead off the plan10 min
  7. 7

    Put it to work

    ~1h

    Get the syntax under your fingers, then load a network and question it.

    • DrillSpot the broken clause12 min
    • Build stepGet a prompt5 min
    • Build stepLoad the network6 min
    • Build stepRank inside each region12 min
    • Build stepChase a running total12 min
    • Build stepWalk the network downstream12 min
    • Build stepAsk the engine what it did7 min
    • Build stepWatch the index stop helping6 min
    • Build stepAsk it something of your own8 min

More in Data & databases

Keep going

All Data & databases projects

SQL: Queries

Say what a query returns before you run it.

Intermediate~3h6 modules

ORMs

Count the queries before the mapper sends them.

Intermediate~4h6 modules

SQL: Joins & NULL

The rows that found no partner, and the values that are not there.

Advanced~4h5 modules

PostgreSQL

Predict what the engine will do before it does it.

Advanced~4h6 modules

The clause you did not write decided the number.

The first item is free. ~6h of focused work, at your own pace.

Start SQL: AdvancedAll projects

Advanced~6h7 modules