Module 5: Model it in SQL · Lesson 5.3 · about 45 min
SQL the way senior analysts write it
Window functions, date spines that show missing days, gaps and islands, cohorts, T-SQL differences and known-answer tests that settle arguments fast.
Module 5 · about 45 minutes, plus a long lab · read first: 5.2, 4.5. You’ll be able to write running totals, ranks and period-over-period with window functions, build a date spine that tells “zero” apart from “missing”, find streaks with gaps and islands, build a cohort table, and prove each query with a tiny known-answer test.
1. Why this matters
The patterns below show up in BI interviews and every week in real work. As a pipeline engineer you also write them into models that hundreds of people read, so a quiet mistake (a moving average that skips closed days) spreads everywhere. Work them in DuckDB first for fast feedback, then run a few on Azure SQL to learn the T-SQL differences.
SQL fundamentals (SELECT, JOIN, GROUP BY) are not repeated here; fibub’s SQL courses will be listed on all courses when they open. If you know pandas, the same ideas appear in rolling windows and cohorts.
2. Window functions
A window function looks at neighbouring rows without collapsing them, like a runner who can see the people just ahead and behind in the queue while staying in place.
with daily as (
select restaurant_id, business_date, sum(qty * price) as revenue
from stg_pos group by all
)
select *,
sum(revenue) over (partition by restaurant_id order by business_date
rows between unbounded preceding and current row) as running_revenue,
avg(revenue) over (partition by restaurant_id order by business_date
rows between 6 preceding and current row) as ma7_rows,
revenue / nullif(lag(revenue, 7) over (partition by restaurant_id order by business_date), 0) - 1
as vs_same_weekday_last_week,
rank() over (partition by business_date order by revenue desc) as rank_on_day
from daily;
partition byrestarts the window for each restaurant.rows between 6 preceding and current rowcounts rows.range between interval 6 days preceding and current rowcounts time. They give different answers as soon as a day is missing, which is the next topic.nullif(x, 0)avoids dividing by zero.
3. Date spines: zero or missing?
A moving average over rows silently skips days with no rows. Build a spine, every restaurant times every date, and left-join the data to it. Then decide for each empty cell: closed (exclude), not received (flag it), or genuinely zero sales (0).
A spine is like a school register with every pupil’s name printed for every day: an empty box stands out, where a list of only the pupils who turned up would hide the absences.
with spine as (
select r.restaurant_id, d.date_key as business_date
from dim_restaurant r
cross join dim_date d
where d.date_key between date '2026-09-01' and date '2026-10-29'
and d.date_key >= r.open_date -- no rows before a restaurant opened
)
select s.*, coalesce(t.revenue, 0) as revenue, t.revenue is null as no_data
from spine s left join daily t using (restaurant_id, business_date);
4. Gaps and islands
Find runs of consecutive days: a store’s silent days, a guest’s consecutive stays, a campaign’s streak above target.
with flagged as (
select store_code, business_date,
business_date - cast(row_number() over (partition by store_code order by business_date) as int) as grp
from spine_with_flags
where no_data
)
select store_code, min(business_date) as gap_start, max(business_date) as gap_end, count(*) as days
from flagged
group by store_code, grp
order by days desc;
Maths and principle
Principle: in a run of consecutive whole numbers, value minus rank is constant. That constant labels the run.
Equation: for sorted days with ranks , let . If then ; a jump of days makes jump by .
Analogy: people walking up a staircase one step per second. Subtract the clock from the step number and everyone who never paused shares the same answer; a pause changes it.
Where it breaks: duplicate days give two rows the same day but different ranks. Deduplicate first, or use dense_rank().
Try it: days 3, 4, 5, 9, 10. Ranks 1 to 5. Labels? (2, 2, 2, 5, 5: two islands.)
5. Cohorts
Retention by first-purchase month for a loyalty programme, or repeat guests for a hotel:
with firsts as (
select customer_id, date_trunc('month', min(order_date)) as cohort from orders group by 1
),
activity as (
select distinct customer_id, date_trunc('month', order_date) as month from orders
)
select cohort, datediff('month', cohort, month) as month_n, count(*) as active
from firsts join activity using (customer_id)
group by 1, 2
order by 1, 2;
Read the result as a triangle: each cohort’s row starts at 100 % in month 0 and shows how many came back in month 1, 2, 3. The statistics course covers how to tell real changes in retention from noise.
6. T-SQL differences to remember
| DuckDB or Postgres | T-SQL (SQL Server, Azure SQL) |
|---|---|
limit 10 |
select top (10) or offset 0 rows fetch next 10 rows only |
date_trunc('month', d) |
datetrunc(month, d) on newer versions, or datefromparts(year(d), month(d), 1) |
d - interval 7 day |
dateadd(day, -7, d) |
qualify rn = 1 |
compute rn in a CTE, filter where rn = 1 outside |
group by all |
list the columns |
generate_series(...) |
generate_series on newer versions, otherwise a numbers table |
Function availability depends on the SQL Server version and compatibility level: check Microsoft Learn for yours.
7. Write SQL like code
- One CTE per step, named for what it holds:
daily_revenue,spine,with_flags. - No
select *in final models; explicit join conditions. - Known-answer tests: a tiny hand-made input (ten rows) where you can work out the answer on paper, and a test that compares the query’s output with it. When an analyst says “your week-on-week number is wrong”, the known-answer test settles it in a minute.
- Window function
- A calculation over neighbouring rows (running total, rank, lag) that keeps every row.
- ROWS vs RANGE
- ROWS counts rows back; RANGE counts time back. They differ when days are missing.
- Date spine
- Every entity times every date, so missing days appear as rows you can flag.
- Gaps and islands
- Finding runs of consecutive values with "value minus row number".
- Physical meaning
- An area manager sees a store's 7-day average looking healthy. In fact the store's tills were silent for three days and the average was taken over the seven days that did report, stretching back two weeks. A spine with a no_data flag turns that into a visible "3 days missing" on the dashboard.
“A 7-row moving average is a 7-day moving average.” Only if every day has exactly one row. Closed days, late files and new stores break that. Use a spine or a RANGE window, and decide on purpose what an empty day means.
8. Practice
Write each as a .sql file with a known-answer test on a ten-row fixture:
- Running revenue per restaurant, restarting each month.
- A 7-day moving average with a RANGE window; compare with ROWS on a store with gaps.
- Same-weekday-last-week % change per restaurant.
- Top 3 menu items per brand per week, ties included.
- Each restaurant’s best and worst day, with the date.
- Silent islands for one store.
- Hotel: runs of consecutive sold-out nights (occupancy on the books ≥ 98 %).
- Hotel: share of bookings by lead-time bucket (0 to 1, 2 to 7, 8 to 30, 31 to 90, over 90 days) and channel.
- Ads: the first date each campaign’s cumulative spend passed $1,000.
- Retail: refund rate per store per week, with zero-refund weeks shown as 0, not missing.
- Pivot channel revenue into columns per brand per week (
pivotin DuckDB,case whenin T-SQL). - A cohort table for synthetic loyalty customers.
Run five of them on Azure SQL and write down each dialect change.
What to check: every known-answer test passes; your ROWS and RANGE moving averages differ on the store with gaps, and you can say which is right; the islands query matches what you count by eye. Commit as feat(sql): analytical SQL patterns with known-answer tests.
- Window functions compute over neighbouring rows without collapsing them; PARTITION BY restarts per entity.
- A date spine makes missing days visible; decide for each whether it is closed, not received or a real zero.
- Gaps and islands: day minus row number is constant inside a run.
- One named CTE per step, no SELECT * in final models, and a known-answer test for every pattern.
Check yourself
Sources
- DuckDB documentation: window functions and the QUALIFY clause https://duckdb.org/docs/
- Microsoft Learn: SELECT - OVER clause (Transact-SQL) https://learn.microsoft.com/sql/t-sql/queries/select-over-clause-transact-sql
- PostgreSQL documentation: window functions tutorial https://www.postgresql.org/docs/current/tutorial-window.html
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.