Business analytics patterns: SQL patterns and examples

Cohorts, funnels, segmentation, churn lists, streaks and outliers: the multi-step queries that analysts write most often.

Cohort RetentionA cohort retention table groups customers by the month they first bought, then shows what share came back in each later month.
Funnel ConversionA funnel query counts how many users reach each step of a journey, such as visit, signup and purchase, in order.
RFM SegmentationRFM segmentation scores each customer on recency, frequency and monetary value, so you can tell your best, lapsing and one-off customers apart.
Churned CustomersA churn list finds customers whose last activity is older than a cutoff, such as no order in 90 days, so you can see who has lapsed.
Streaks (Gaps and Islands)The gaps-and-islands technique finds runs of consecutive days, such as a user's longest streak of daily activity.
Outlier DetectionOutlier detection flags values far from the typical range, using a z-score or the interquartile range, so unusual orders or readings can be reviewed.