SQL patterns for analysts

42 reusable query patterns, each with a PostgreSQL example, a step-by-step explanation, notes on how MySQL, BigQuery, SQL Server and SQLite differ, and the mistakes that make the result wrong. You can also type the question in plain English and let SchemaChat write the query.

Window functions (10)Dates and time (9)Aggregation and pivots (7)Data cleaning (6)Joins and reconciliation (4)Business analytics patterns (6)

Window functions

Running Total
Moving Average
Rank Within a Group
Top N Per Group
Compare to the Previous Row
Percent of Total
Cumulative Percentage (Pareto)
First Row Per Group
Median
Quartiles with NTILE

Dates and time

Group by Month
Group by Week
Month-over-Month Growth
Year-over-Year Growth
Fill Missing Dates
Date Difference
Last N Days
Quarter to Date
Convert Time Zones

Aggregation and pivots

Conditional Aggregation
Pivot Rows to Columns
Count Distinct
HAVING: Filter Groups
Weighted Average
Subtotals with ROLLUP
Histogram Buckets

Data cleaning

Find Duplicate Rows
Remove Duplicates, Keep the Latest
Handle NULL Values
Trim and Standardise Text
Extract the Email Domain
Convert Text to a Date

Joins and reconciliation

Customers Without Orders
Join Fan-Out (Double Counting)
Hierarchy with a Recursive CTE
Compare Two Tables

Business analytics patterns

Cohort Retention
Funnel Conversion
RFM Segmentation
Churned Customers
Streaks (Gaps and Islands)
Outlier Detection

Looking for the formula behind a specific business number? The metrics library covers sales, marketing, SaaS, finance, retail, operations, support, HR and product metrics. Coming from spreadsheets? See the Excel to SQL equivalents, or the glossary for unfamiliar terms.