Typorithm learning path
SQL: analytics and dependable queries
Use subqueries, set operations, windows, time series, safe parameters, plans and reversible transactions. Prerequisite: SQL query foundations. Reviewed 5 November 2026; all accepted and solution queries verified in SQLite and PostgreSQL 18 through the local lab. Windows build gradually; stretches are optional.
Study with a practical destination
Use the linked lessons in order, complete the lab and explain the decisions without the reference. Select the path and depth that match your role.
SQL: correct queries and practical analytics
Before starting: No previous SQL is required for query foundations. Complete its row-grain, NULL, join and aggregate lessons before the analytics track. Reading and self-checks work in the app; optional database execution uses a computer and the disposable companion lab.
Build and explain: Write a country-aware report with correct entity counts, observed denominators, explicit ties and reproducible time windows; bind values safely and inspect a local query plan.
- 1. Tables, rows and the question behind a query
- 2. NULL and three-valued logic
- 3. INNER JOIN and relationship cardinality
- 4. LEFT JOIN, missing matches and filter placement
- 5. Multiple one-to-many joins and honest counts
- 6. COUNT, SUM, AVG and observed denominators
- 7. Capstone: a country-aware hiring report
- 8. EXISTS, NOT EXISTS and nullable anti-joins
- 9. ROW_NUMBER and top-N per group
- 10. ROWS, RANGE and peer-aware running totals
- 11. Calendar scaffolds and justified zero days
- 12. Bound values and safe query boundaries
- 13. Indexes, EXPLAIN and measured query reasoning
- 14. Transactions, conditional changes and rollback evidence
- 15. Capstone: one representative role per company
Review evidence: Predict exact identities and values before running a query. Include empty companies, unknown and zero observations, repeated child relationships, tied ordering values and missing days. Reject the intentionally wrong queries and explain their failure. Production concurrency needs its own multi-connection evidence.
Your learning path
0 of 16 lessons completed
Saved on this device
Module 1 · 1 lessons
Scalar and correlated subqueries
Compare each observation with the intended global or group-level reference.
Module 2 · 1 lessons
EXISTS, NOT EXISTS and nullable anti-joins
Ask whether a related match exists without accidentally multiplying or poisoning the parent result.
Module 3 · 1 lessons
UNION ALL, UNION and INTERSECT
Choose whether combined populations preserve occurrences or represent distinct membership.
Module 4 · 1 lessons
CTEs and reviewable population pipelines
Name intermediate grains while keeping logical meaning separate from materialization assumptions.
Module 5 · 1 lessons
ROW_NUMBER and top-N per group
Rank an eligible population with a deterministic tie-breaker, then filter the ranked result.
Module 6 · 1 lessons
RANK, DENSE_RANK and intentional peers
Preserve measured-value ties while choosing whether subsequent ranks leave gaps.
Module 7 · 1 lessons
Window aggregates without collapsing detail
Attach group measurements to each retained source identity instead of grouping identities away.
Module 8 · 1 lessons
ROWS, RANGE and peer-aware running totals
Make the intended frame explicit when ordered aggregates encounter equal sort values.
Module 9 · 1 lessons
LAG, changes and the previous observed period
Aggregate to the intended time grain before comparing neighboring observations.
Module 10 · 1 lessons
Calendar scaffolds and justified zero days
Retain requested periods while separating complete-log zero from unknown collection coverage.
Module 11 · 1 lessons
Percentages, numeric division and honest populations
Make the numerator, denominator and rounding policy visible in a share calculation.
Module 12 · 1 lessons
Gaps and islands at the correct calendar grain
Mark sequence breaks only after deduplicating observations to the entity-day grain.
Module 13 · 1 lessons
Bound values and safe query boundaries
Keep user values out of SQL syntax and allowlist identifiers separately.
Module 14 · 1 lessons
Indexes, EXPLAIN and measured query reasoning
Inspect an engine's actual plan without treating a tiny fixture as a production benchmark.
Module 15 · 1 lessons
Transactions, conditional changes and rollback evidence
Verify an atomic unit by inspecting intermediate state and the state after rollback.
Module 16 · 1 lessons
Capstone: one representative role per company
Combine eligibility, tie policy, ranking and empty-company retention in a reviewable report.