Window functions

If you only learn one advanced SQL concept this year, make it window functions. They let you calculate aggregates over a partition of your data without collapsing rows — which is exactly what you need for most business reporting tasks.

The pattern I use most: RANK() OVER (PARTITION BY partner_id ORDER BY booking_date DESC) — this gives you the most recent booking for each partner without a subquery.

Running totals

SUM(revenue) OVER (PARTITION BY month ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — this single line gives you a running total within a partition. It replaces what used to require a self-join or a correlated subquery.

Cohort analysis

Cohort analysis in SQL requires a consistent pattern: find the first event date for each entity, then calculate time elapsed from that date for all subsequent events. DATEDIFF(event_date, first_event_date) is your core calculation. Group by cohort (the period of the first event) and elapsed period to get the classic cohort retention grid.

Practical tip: Always build your cohort definition table as a separate CTE before joining it to your events table. This makes the query easier to read, test, and modify when the definition changes — and it always changes.

Date dimension tricks

Don't calculate date parts inline — use a date dimension table. A simple calendar table with pre-calculated columns (day_of_week, is_weekend, is_holiday, fiscal_quarter) makes your reporting queries cleaner and your Power BI measures faster. Build it once, use it everywhere.

The CASE pattern

CASE WHEN combined with aggregation is underused. SUM(CASE WHEN status = \'completed\' THEN revenue ELSE 0 END) — this lets you pivot row data into columns without a PIVOT clause, which is often slower and harder to maintain. Master this pattern and you'll write half as many subqueries.

`