SQL for Analysts
PostgreSQL · 17 modules + capstone, projects and electives · three real businesses (NorthCart, Cloudly, FleetOps) · interview checkpoint after every module.
Enrolling creates your progress record for this course. You can browse the curriculum below first.
Curriculum
Module 1 · Know your data, pull the right rows
Grain, the logical order of a query, three-valued logic, and the types underneath.Optional depth lesson · 1.10
Checkpoint 1 · Interview warm-up10 timed questions + 2 optional bonus + 4 explain-it questions
All threeModule 2 · Counting and summing
COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, percentages and ROUND.Optional depth lesson · 2.8
Checkpoint 2 · Summaries interview10 timed questions + 2 optional bonus + 4 explain-it questions
All threeModule 3 · If-then logic: CASE
Labels, custom sort orders, cleaning values, conditional counts and the first pivot. · 6 lessons · about 2.6 h·
3.4 Counting with conditions26 min · FleetOps · COUNT(CASE …) and SUM(CASE …) count only the rows that match, inside each group
Being writtenFleetOps·
3.5 FILTER: the Postgres shortcut22 min · Cloudly · COUNT(*) FILTER (WHERE …), same answer as SUM(CASE …), easier to read
Being writtenCloudly·
3.6 Rates inside a group27 min · NorthCart · rate = conditional count / total count per group; return rate by category; keep only groups above 5%
Being writtenNorthCart·
3.7 Your first pivot26 min · Cloudly · one column per value using conditional sums: long list becomes a wide table
Being writtenCloudlyOptional depth lesson · 3.8
·
3.8 Conditional averages and the ELSE trapdepth24 min · FleetOps · AVG(CASE …) with ELSE 0 vs no ELSE gives different answers; when each is right
Being writtenFleetOps·
3.9 Lab: Lina's channel report28 min · NorthCart · composition lab: CASE labels, conditional counts, rates and a pivot, plus ILIKE from Module 1
Being writtenNorthCart◇
Checkpoint 3 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 4 · Reports over time
EXTRACT, DATE_TRUNC, monthly reports, period comparisons and subtotals. · 8 lessons · about 3.4 hBeing written·
4.0 EXTRACT: pull out a part of a date24 min · NorthCart · EXTRACT(year / month / dow / hour FROM …); orders by weekday and hour
Being writtenNorthCart·
4.1 DATE_TRUNC: round down to the month25 min · Cloudly · DATE_TRUNC('month', …) turns every date into its month bucket; group by it
Being writtenCloudly·
4.2 A monthly report, start to finish26 min · NorthCart · month bucket, totals, ORDER BY month, TO_CHAR for a readable month label
Being writtenNorthCart·
4.3 Weeks and quarters24 min · FleetOps · DATE_TRUNC('week') starts on Monday; quarters; choosing the right bucket
Being writtenFleetOps·
4.4 The vanishing month22 min · Cloudly · months with no rows simply disappear from a grouped report; how to spot the gap (fixed with a date spine in 6.6)
Being writtenCloudly·
4.5 This period vs last period26 min · NorthCart · compare two periods in one query with conditional sums over date ranges
Being writtenNorthCartOptional depth lesson · 4.6
·
4.6 Subtotals and grand totalsdepth27 min · FleetOps · GROUPING SETS, ROLLUP, CUBE and GROUPING() to add total rows
Being writtenFleetOps·
4.7 Lab: Jo's monthly on-time report28 min · FleetOps · composition lab: month buckets, conditional counts, rates and HAVING, plus ::date from Module 1
Being writtenFleetOps◇
Checkpoint 4 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 5 · Combining tables: joins
INNER, LEFT, RIGHT and FULL joins, aliases, anti-joins, three-table joins. · 10 lessons · about 4.2 hBeing written·
5.0 Why join? Two tables, one question24 min · NorthCart · keys link tables (recap of 1.0); CROSS JOIN on a tiny example; a join is every pair, then a filter (model card)
Being writtenNorthCart·
5.1 INNER JOIN … ON25 min · NorthCart · INNER JOIN keeps matching pairs only; ON says how rows match
Being writtenNorthCart·
5.2 Table aliases and ambiguous columns22 min · Cloudly · short table aliases; "column reference is ambiguous" and how to fix it
Being writtenCloudly·
5.3 LEFT JOIN: keep everyone27 min · NorthCart · LEFT JOIN keeps every row on the left and fills blanks on the right; counting with LEFT JOIN includes zeros
Being writtenNorthCart·
5.4 Who is missing? The anti-join25 min · Cloudly · LEFT JOIN … WHERE right.key IS NULL finds rows with no partner (trials with no subscription)
Being writtenCloudly·
5.5 Joining three tables26 min · FleetOps · chain joins step by step; check the row count after each join
Being writtenFleetOps·
5.6 Joins plus GROUP BY27 min · NorthCart · join first, then group; revenue per category via order lines, products and categories
Being writtenNorthCart·
5.7 RIGHT JOIN and FULL OUTER JOIN26 min · Cloudly · RIGHT is LEFT turned round; FULL keeps both sides; reconcile two lists (invoices vs payments, vehicles vs fuel logs)
Being writtenCloudlyOptional depth lesson · 5.8
·
5.8 USING and NATURAL JOINdepth20 min · FleetOps · USING(col) shortcut; why NATURAL JOIN is risky
Being writtenFleetOps·
5.9 Lab: Ravi's account health list28 min · Cloudly · composition lab: three-table join, LEFT JOIN with zeros, anti-join, GROUP BY and CASE
Being writtenCloudly◇
Checkpoint 5 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 6 · Join traps and special joins
Fan-out, the WHERE that breaks a LEFT JOIN, bridges, self-joins, range joins, CROSS JOIN grids. · 9 lessons · about 3.9 hBeing written·
6.0 Fan-out: why totals double26 min · NorthCart · joining a one-row-per-order table to a many-rows-per-order table repeats values; SUM goes wrong
Being writtenNorthCart·
6.1 Check the grain24 min · Cloudly · compare COUNT(*) with COUNT(DISTINCT key) before and after a join; sum at the right table
Being writtenCloudly·
6.2 The WHERE that breaks a LEFT JOIN25 min · FleetOps · a WHERE on the right table turns LEFT into INNER; move the condition into ON
Being writtenFleetOps·
6.3 Many-to-many and bridge tables26 min · NorthCart · a bridge table links two tables (order_items links orders and products); count at the right level
Being writtenNorthCart·
6.4 Self-joins: a table with itself26 min · FleetOps · join a table to itself with two aliases; pairs on the same day; the same driver on two routes
Being writtenFleetOps·
6.5 Range joins: ON with BETWEEN25 min · Cloudly · non-equal joins: match a row to the period it falls in (invoice inside a subscription)
Being writtenCloudly·
6.6 CROSS JOIN grids and the date spine27 min · FleetOps · generate_series makes every month; CROSS JOIN with hubs makes a full grid; LEFT JOIN fills zeros (fixes 4.4)
Being writtenFleetOpsOptional depth lesson · 6.7
·
6.7 How Postgres runs a joindepth24 min · FleetOps · nested loop, hash and merge joins in plain words; first look at EXPLAIN
Being writtenFleetOps·
6.8 Lab: Grace's double-counted revenue28 min · Cloudly · composition lab: find and fix a fan-out, a broken LEFT JOIN and a missing month in one report
Being writtenCloudly◇
Checkpoint 6 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 7 · Questions inside questions: subqueries and CTEs
Scalar and IN subqueries, derived tables, CTEs, correlated subqueries, EXISTS. · 11 lessons · about 4.8 hBeing written·
7.0 A query inside a query24 min · NorthCart · scalar subquery: compare each row with one number worked out by another query
Being writtenNorthCart·
7.1 IN with a subquery23 min · Cloudly · WHERE col IN (SELECT …) uses another query as the list
Being writtenCloudly·
7.2 A subquery in FROM26 min · FleetOps · derived table: summarise first, then join (the clean fan-out fix from 6.0)
Being writtenFleetOps·
7.3 WITH: name your steps26 min · Cloudly · a CTE gives a step a name; read top to bottom; same result as the derived table
Being writtenCloudly·
7.4 Chaining CTEs26 min · NorthCart · several CTEs in a row, each using the one before; debugging one step at a time
Being writtenNorthCart·
7.5 Correlated subqueries26 min · NorthCart · the inner query re-runs for each outer row; above their own category average
Being writtenNorthCart·
7.6 EXISTS, NOT EXISTS and the NOT IN trap27 min · Cloudly · EXISTS checks if a match exists; NOT IN breaks when the list has a NULL; NOT EXISTS is safe
Being writtenCloudly·
7.7 Same answer, three ways26 min · FleetOps · solve one question with a correlated subquery, a join and a CTE; compare readability (interview skill)
Being writtenFleetOpsOptional depth lesson · 7.8
·
7.8 Recursive CTEsdepth27 min · FleetOps · WITH RECURSIVE: a date spine by counting up; walking a tree of parents and children
Being writtenFleetOpsOptional depth lesson · 7.9
·
7.9 LATERAL and CTE materialisationdepth26 min · NorthCart · LATERAL lets a subquery see the row beside it (latest 3 orders per customer); when Postgres stores a CTE
Being writtenNorthCart·
7.10 Lab: Jo's capacity alert28 min · FleetOps · composition lab: date spine, CTEs and a correlated check: first day each hub went over capacity
Being writtenFleetOps◇
Checkpoint 7 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 8 · Window functions I: ranking and neighbours
OVER, PARTITION BY, ROW_NUMBER, RANK, top-N per group, DISTINCT ON, LAG and LEAD. · 9 lessons · about 3.8 hBeing written·
8.0 OVER(): a number for every row25 min · NorthCart · a window function keeps every row and adds a summary next to it (model card: a group that does not collapse)
Being writtenNorthCart·
8.1 PARTITION BY vs GROUP BY25 min · Cloudly · PARTITION BY makes groups without collapsing rows; side by side with GROUP BY
Being writtenCloudly·
8.2 ROW_NUMBER: number the rows25 min · NorthCart · ROW_NUMBER() OVER (PARTITION BY … ORDER BY …); first order per customer
Being writtenNorthCart·
8.3 RANK and DENSE_RANK: handling ties24 min · FleetOps · ROW_NUMBER vs RANK vs DENSE_RANK on tied values
Being writtenFleetOps·
8.4 Top-N per group27 min · NorthCart · rank inside a CTE, then filter; you cannot put a window in WHERE (window trap)
Being writtenNorthCart·
8.5 DISTINCT ON: first per group, the Postgres way22 min · Cloudly · DISTINCT ON (key) with ORDER BY picks one row per group
Being writtenCloudly·
8.6 LAG and LEAD: the row before and after25 min · Cloudly · LAG looks back, LEAD looks forward inside a partition
Being writtenCloudly·
8.7 Month-over-month change27 min · NorthCart · monthly totals in a CTE, LAG for last month, growth % with NULLIF guard
Being writtenNorthCart·
8.8 Lab: Ravi's monthly leaderboard28 min · Cloudly · composition lab: monthly totals, ranking per month, MoM change and a join
Being writtenCloudly◇
Checkpoint 8 · Interview warm-upabout 8 timed questions + explain-it questions
Being writtenAll threeModule 9 · Window functions II: running, rolling and shares
Running totals, frames, moving averages, percent of total, FIRST_VALUE, NTILE. · 10 lessons · about 4.1 hBeing written·
9.0 Running totals25 min · NorthCart · SUM(…) OVER (ORDER BY …) keeps adding as it goes; reset per group with PARTITION BY
Being writtenNorthCart·
9.1 Frames: which rows the window sees26 min · FleetOps · ROWS BETWEEN 6 PRECEDING AND CURRENT ROW; the default frame
Being writtenFleetOps·
9.2 Moving averages24 min · Cloudly · rolling averages smooth noisy daily numbers
Being writtenCloudlyOptional depth lesson · 9.3
·
9.3 ROWS vs RANGE vs GROUPSdepth25 min · FleetOps · how the three frame types treat ties and gaps in dates
Being writtenFleetOps·
9.4 Percent of total26 min · NorthCart · value / SUM(value) OVER (PARTITION BY …); share of a group
Being writtenNorthCart·
9.5 FIRST_VALUE, LAST_VALUE and the frame trap25 min · Cloudly · FIRST_VALUE works out of the box; LAST_VALUE needs a full frame
Being writtenCloudly·
9.6 NTILE: quartiles and buckets23 min · FleetOps · NTILE(4) splits rows into equal-sized groups
Being writtenFleetOps·
9.7 Windows over grouped results26 min · NorthCart · GROUP BY first, then a window over the groups (rank of monthly totals); windows after joins
Being writtenNorthCartOptional depth lesson · 9.8
·
9.8 Named windowsdepth20 min · Cloudly · the WINDOW clause names a window once and reuses it
Being writtenCloudly·
9.9 Lab: MoM growth per plan28 min · Cloudly · composition lab: MoM growth per plan with share of total, ranked within each month
Being writtenCloudly◇
Checkpoint 9 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll three★
Mock interview 1 (after the window modules)60 minutes: 6 live queries + 4 explain-it, the tutor plays the interviewer
Being writtenAll threeModule 10 · Dates and time in depth
Intervals, AGE, EPOCH, time zones, date spines, business days, YoY, ISO weeks, medians. · 10 lessons · about 4.3 hBeing written·
10.0 Intervals: adding and subtracting time24 min · Cloudly · date + INTERVAL '7 days'; NOW(); last 30 days filters
Being writtenCloudly·
10.1 AGE and days between25 min · Cloudly · AGE() for human-readable gaps; date minus date in days; days from trial to first payment
Being writtenCloudly·
10.2 EPOCH: durations in hours and minutes25 min · Cloudly · EXTRACT(EPOCH FROM interval) / 3600 for hours; response times
Being writtenCloudly·
10.3 Time zones: timestamptz and AT TIME ZONE27 min · Cloudly · timestamp vs timestamptz; AT TIME ZONE; Sydney accounts that logged local time
Being writtenCloudly·
10.4 Date spines with generate_series25 min · NorthCart · a row for every day; LEFT JOIN data onto it so empty days show 0
Being writtenNorthCart·
10.5 Business days and week starts26 min · FleetOps · skip weekends with EXTRACT(isodow); count working days between two dates
Being writtenFleetOps·
10.6 Year over year26 min · NorthCart · compare a month with the same month last year (LAG 12 or a self-join)
Being writtenNorthCartOptional depth lesson · 10.7
·
10.7 ISO weeks and fiscal calendarsdepth24 min · FleetOps · ISO week numbers and years; a fiscal year that starts in April
Being writtenFleetOps·
10.8 Medians and percentiles25 min · Cloudly · percentile_cont(0.5) WITHIN GROUP: the middle value; why median beats average for durations
Being writtenCloudly·
10.9 Lab: Mei's SLA report28 min · Cloudly · composition lab: response hours, SLA targets by priority (urgent 2h, high 8h, medium 24h, low 72h), breach rate per month
Being writtenCloudly◇
Checkpoint 10 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 11 · Text in depth
TRIM, case, pieces of text, building text, mapping typos, regular expressions. · 11 lessons · about 4.5 hBeing written·
11.0 Tidy text: TRIM, UPPER, LOWER, INITCAP24 min · FleetOps · remove spaces and fix capitals before grouping; LENGTH to spot trailing spaces
Being writtenFleetOps·
11.1 Pieces of text: LEFT, RIGHT, SUBSTRING, POSITION24 min · FleetOps · cut out part of a string by position; tracking-number prefixes
Being writtenFleetOps·
11.2 SPLIT_PART: email domains24 min · NorthCart · SPLIT_PART(email, '@', 2); counting by domain after LOWER
Being writtenNorthCart·
11.3 Building text: ||, CONCAT and FORMAT23 min · Cloudly · join pieces into one string; NULL inside || vs CONCAT
Being writtenCloudly·
11.4 Map typos to the clean list27 min · FleetOps · CASE + ILIKE (or a mapping CTE) turns 27 spellings into the 12 real hubs, then recount
Being writtenFleetOps·
11.5 Regular expressions: ~ and ~*26 min · FleetOps · regex patterns for "starts with letters then digits"; ~ is case-sensitive, ~* is not
Being writtenFleetOps·
11.6 regexp_replace and regexp_matches25 min · NorthCart · clean with a pattern; pull out the part that matches
Being writtenNorthCartOptional depth lesson · 11.7
·
11.7 LIKE vs SIMILAR TO vs regexdepth20 min · Cloudly · when each is the right tool; SIMILAR TO in brief
Being writtenCloudlyOptional depth lesson · 11.8
·
11.8 Gluing rows into text: string_aggdepth22 min · NorthCart · string_agg(col, ', ') turns many rows into one line
Being writtenNorthCartOptional depth lesson · 11.9
·
11.9 Arrays and JSON for analystsdepth27 min · NorthCart · array_agg and unnest; reading JSON with -> and ->>; jsonb_array_elements
Being writtenNorthCart·
11.10 Lab: Jo's clean weekly report28 min · FleetOps · composition lab: YoY by ISO week with normalised hub names and regex-validated tracking numbers
Being writtenFleetOps◇
Checkpoint 11 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 12 · Shaping and combining results
UNION, INTERSECT, EXCEPT, pivots and unpivots, reporting polish. · 8 lessons · about 3.4 hBeing written·
12.0 UNION ALL: stack results23 min · NorthCart · stack two result sets with the same columns
Being writtenNorthCart·
12.1 UNION vs UNION ALL: sets and bags24 min · Cloudly · UNION removes duplicates, UNION ALL keeps them (model card); which one totals need
Being writtenCloudly·
12.2 INTERSECT and EXCEPT25 min · NorthCart · rows in both lists; rows in the first but not the second
Being writtenNorthCart·
12.3 Pivots, revisited25 min · Cloudly · long to wide with conditional aggregation at scale: months as columns
Being writtenCloudly·
12.4 Unpivot: wide to long25 min · NorthCart · turn columns back into rows with UNION ALL or LATERAL VALUES
Being writtenNorthCart·
12.5 Composed shaping27 min · FleetOps · pivot a windowed measure (rank or share) into a wide report
Being writtenFleetOpsOptional depth lesson · 12.6
·
12.6 crosstab and reporting polishdepth24 min · NorthCart · crosstab in brief (if available), ROLLUP totals inside pivots, TO_CHAR number formats
Being writtenNorthCart·
12.7 Lab: churned accounts by plan and quarter28 min · Cloudly · composition lab: EXCEPT to find churned accounts, pivoted plan × quarter, with rank
Being writtenCloudly◇
Checkpoint 12 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 13 · Data quality
Duplicates, keep-latest, orphans, double charges, impossible data, control totals. · 7 lessons · about 3 hBeing written·
13.0 Finding duplicates24 min · NorthCart · GROUP BY … HAVING COUNT(*) > 1; duplicate emails that differ only in capitals
Being writtenNorthCart·
13.1 Dedupe: keep the latest26 min · Cloudly · ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY created DESC) = 1
Being writtenCloudly·
13.2 Orphans: rows pointing to nothing24 min · Cloudly · anti-join to find foreign keys with no parent (orphan invoices)
Being writtenCloudly·
13.3 Double charges and reconciliation26 min · NorthCart · find payments charged twice; reconcile payments against orders
Being writtenNorthCart·
13.4 Impossible and inconsistent data27 min · FleetOps · negative quantities, overlapping routes, scans out of order, missing scans, products renamed mid-year
Being writtenFleetOps·
13.5 Control totals23 min · NorthCart · check a result against a known total before you share it
Being writtenNorthCart·
13.6 Lab: Grace's clean customer list28 min · Cloudly · composition lab: dedupe, orphan check, double-payment check and control totals in one CTE chain
Being writtenCloudly◇
Checkpoint 13 · Interview warm-upabout 8 timed questions + explain-it questions
Being writtenAll threeModule 14 · Fast and readable SQL
EXPLAIN, indexes, sargable filters, style, proving a refactor, transactions. · 9 lessons · about 3.8 hBeing written·
14.0 EXPLAIN: how Postgres plans a query25 min · FleetOps · read a plan from the inside out; Seq Scan, rows, cost
Being writtenFleetOps·
14.1 EXPLAIN ANALYZE: estimate vs reality25 min · FleetOps · actual times and row counts; spotting a bad estimate
Being writtenFleetOps·
14.2 Indexes26 min · FleetOps · an index is the index at the back of a book; CREATE INDEX; Index Scan vs Seq Scan
Being writtenFleetOps·
14.3 Index-friendly filters25 min · FleetOps · do not wrap the column (use a range, not col::date = …); avoid SELECT * in reports
Being writtenFleetOpsOptional depth lesson · 14.4
·
14.4 Filter early, CTE fences and temp tablesdepth25 min · FleetOps · reduce rows early; when a CTE is computed once; temp tables for big intermediate steps
Being writtenFleetOps·
14.5 Readable SQL23 min · NorthCart · a style guide: layout, naming CTEs, comments that explain why
Being writtenNorthCart·
14.6 Prove a refactor26 min · NorthCart · old vs new query: EXCEPT both ways and control totals prove they match
Being writtenNorthCartOptional depth lesson · 14.7
·
14.7 Safe experiments: transactionsdepth22 min · Cloudly · BEGIN, ROLLBACK, COMMIT and savepoints
Being writtenCloudly·
14.8 Lab: make Jo's slow report fast and readable28 min · FleetOps · composition lab: dedupe, index-friendly rewrite, prove equality, under 100 ms
Being writtenFleetOps◇
Checkpoint 14 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 15 · Analytics patterns I: customers and revenue
Repeat purchase, AOV, cohorts, retention, churn, MRR, YTD, baskets, marketing ROI. · 9 lessons · about 4 hBeing written·
15.0 Repeat purchase and average order value25 min · NorthCart · customers with 2+ orders; AOV; per country
Being writtenNorthCart·
15.1 Cohorts: group customers by their first month26 min · NorthCart · first order month per customer; cohort sizes
Being writtenNorthCart·
15.2 The retention triangle28 min · Cloudly · months since first purchase; share still active; the cohort triangle
Being writtenCloudly·
15.3 Churn, expansion and reactivation27 min · Cloudly · flag accounts that left, grew, shrank or came back (upgrade and downgrade in one month)
Being writtenCloudly·
15.4 MRR and the MRR bridge28 min · Cloudly · MRR and ARR; new, expansion, contraction and churned MRR month by month
Being writtenCloudly·
15.5 YTD and rolling 12 months26 min · NorthCart · year-to-date totals; rolling 12-month sums; NULLIF guards for growth
Being writtenNorthCart·
15.6 Basket analysis: bought together27 min · NorthCart · self-join order lines to count product pairs
Being writtenNorthCart·
15.7 Marketing ROI26 min · NorthCart · spend per channel vs orders and revenue per channel; cost per order
Being writtenNorthCart·
15.8 Lab: retention by channel28 min · NorthCart · composition lab: retention triangle by first-order channel with a rolling 3-month average
Being writtenNorthCart◇
Checkpoint 15 · Interview warm-upabout 8 timed questions + explain-it questions
Being writtenAll threeModule 16 · Analytics patterns II: behaviour and operations
Funnels, conversion windows, sessions, DAU/MAU, streaks, anomalies, ops and service KPIs. · 10 lessons · about 4.4 hBeing written·
16.0 Funnels: step-by-step drop-off27 min · Cloudly · count users at each product step; conversion between steps (Cloudly events)
Being writtenCloudly·
16.1 Conversion windows26 min · Cloudly · did a trial convert within 14 days? trial-to-paid conversion rate per month
Being writtenCloudly·
16.2 Sessions: 30-minute gaps27 min · Cloudly · LAG to find gaps, a running sum to number sessions
Being writtenCloudly·
16.3 DAU, MAU and stickiness25 min · Cloudly · daily and monthly active users; DAU/MAU ratio
Being writtenCloudly·
16.4 Gaps and islands: streaks28 min · FleetOps · ROW_NUMBER difference trick to find runs of days
Being writtenFleetOps·
16.5 Anomalies: z-scores and percentiles26 min · FleetOps · average and standard deviation per hub; flag days 3 standard deviations away
Being writtenFleetOps·
16.6 Operations KPIs27 min · FleetOps · on-time rate, vehicle utilisation, cost per parcel from fuel logs, stops per mile
Being writtenFleetOps·
16.7 Service KPIs: NPS and SLA25 min · Cloudly · NPS = % promoters - % detractors; SLA breach rate (reuses 10.9)
Being writtenCloudlyOptional depth lesson · 16.8
·
16.8 Reading an A/B test in SQLdepth26 min · NorthCart · conversion per group, difference and a simple significance check (needs a small experiment table, see data notes)
Being writtenNorthCart·
16.9 Lab: Jo's driver board28 min · FleetOps · composition lab: streaks, anomalies and ops KPIs on one board
Being writtenFleetOps◇
Checkpoint 16 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll threeModule 17 · Building for others: views, changes and models
Views, materialised views, INSERT/UPDATE/DELETE, upserts, temp tables, keys, star schema. · 11 lessons · about 4.5 hBeing written·
17.0 Views: save a query as a table23 min · NorthCart · CREATE VIEW; query it like a table; when to use one
Being writtenNorthCart·
17.1 Materialised views24 min · NorthCart · stored results; REFRESH MATERIALIZED VIEW; fast but can be stale
Being writtenNorthCart·
17.2 INSERT and RETURNING22 min · Cloudly · add rows; RETURNING shows what changed (Reset data restores the database)
Being writtenCloudly·
17.3 UPDATE … FROM24 min · Cloudly · change rows using another table; always test with SELECT first
Being writtenCloudly·
17.4 DELETE … USING22 min · Cloudly · remove rows matched in another table; safety habits
Being writtenCloudly·
17.5 Upsert: ON CONFLICT and MERGE26 min · FleetOps · insert or update in one step; MERGE in brief
Being writtenFleetOps·
17.6 CREATE TABLE AS and temp tables22 min · FleetOps · save a result as a new table; temporary tables for one session
Being writtenFleetOps·
17.7 Keys and constraints as documentation24 min · FleetOps · primary keys, foreign keys, NOT NULL, CHECK; reading them to learn a schema
Being writtenFleetOps·
17.8 Star schema for reporting27 min · NorthCart · facts and dimensions; one fact table, small dimension tables (bridge to Power BI)
Being writtenNorthCartOptional depth lesson · 17.9
·
17.9 SQL functions and parameterised reportsdepth25 min · NorthCart · CREATE FUNCTION with SQL; a report that takes a date range
Being writtenNorthCart·
17.10 Lab: a KPI view kept up to date28 min · FleetOps · composition lab: materialised KPI view fed by UPSERT, verified with control totals
Being writtenFleetOps◇
Checkpoint 17 · Interview warm-upabout 8 timed questions + optional bonus + explain-it questions
Being writtenAll three★
Mock interview 2 (end of the core course)60 minutes: 6 live queries + 4 explain-it, the tutor plays the interviewer
Being writtenAll threeCapstone and portfolio projects
Guided-but-open projects; the tutor plays the stakeholder. · 4 lessons · about 14 hBeing written·
C.0 Capstone: does late delivery hurt sales?120 min · All three · cross-business brief using all three databases (NorthCart ships via FleetOps); ambiguous requirements; 5-line manager summary
Being writtenAll three·
P.1 Project 1: Olist marketplace240 min · New dataset · delivery delay vs review score, seller performance, repeat-customer rate, revenue by state and category; SQL repo + 1-page memo + 3 charts
Being writtenNew dataset·
P.2 Project 2: Healthcare operations240 min · New dataset · 30-day readmission, length of stay, claim denials, no-shows; SQL repo + KPI dashboard spec
Being writtenNew dataset·
P.3 Project 3: Your choice240 min · New dataset · finance (fraud), HR attrition or marketing attribution, chosen to match target job ads
Being writtenNew datasetElectives (Detailed path)
Optional deep dives after the core course. · 4 lessons · about 8 hBeing writtenOptional depth lesson ·
·
E1 JSON and arrays in depthdepth120 min · NorthCart · jsonb operators, indexing JSON, arrays in depth
Being writtenNorthCartOptional depth lesson ·
·
E2 Regex and full-text searchdepth120 min · Cloudly · advanced regex, tsvector, tsquery, ranking
Being writtenCloudlyOptional depth lesson ·
·
E3 PL/pgSQL basicsdepth120 min · NorthCart · procedural functions, variables, loops, error handling
Being writtenNorthCartOptional depth lesson ·
·
E4 Partitioning and internalsdepth120 min · FleetOps · table partitioning, MVCC and VACUUM in plain words
Being writtenFleetOps