Week 5 · Model it in SQL › Day 31

Day 31. SQL the way senior analysts write it

1. Why this matters

This is your SQL drill day, closing your stated biggest gap. Every pattern below shows up in BI interviews and weekly in real work. Do them in DuckDB first (fast feedback), then run the same ones on Azure SQL to learn the T-SQL differences.

2. Window functions

with daily as (
  select restaurant_id, business_date, sum(qty * price) as revenue
  from stg_qsr_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,
  revenue / nullif(lag(revenue, 7) over (partition by restaurant_id order by business_date), 0) - 1 as wow_same_weekday,
  rank() over (partition by business_date order by revenue desc)              as rank_on_day
from daily;

rows between counts rows; range between interval 6 day preceding counts time. They differ when days are missing, which is the next topic.

3. Date spines: zeros vs missing

A moving average over rows silently skips missing days. Join to a spine of all dates × all entities, then decide: missing because closed (exclude), missing because not received (flag), or genuinely zero (0).

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'
)
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 consecutive runs: a store's silent days, a guest's consecutive stays, a campaign's streak above target.

-- islands of consecutive days with no data for each store
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;

5. Cohorts

Retention by first-purchase month for a loyalty programme (retail) or repeat guests (hotels):

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 m from orders)
select cohort, datediff('month', cohort, m) as month_n, count(*) as active
from firsts join activity using (customer_id)
group by 1, 2 order by 1, 2;

6. T-SQL differences to remember