After six months of using SQL daily, Priya does not think about syntax anymore. She thinks about patterns. When someone asks "show me the top 3 salespeople in each region," she reaches for the top-N-per-group pattern. When they ask "compare this month to last month," she reaches for LAG. Eight patterns cover 90% of what the business needs.

8Patterns that cover 90% of BI work
∞Questions they can answer
0Reason to reinvent them

Pattern 1: Deduplication

Remove duplicate rows, keeping only the most recent or first occurrence per entity.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY Salesperson ORDER BY OrderDate DESC) AS rn
  FROM SalesFact
)
SELECT * FROM ranked WHERE rn = 1;

Pattern 2: Top N per group

Top 3 salespeople by revenue in each region — the classic "per group" ranking question.

WITH ranked AS (
  SELECT Region, Salesperson, Revenue,
    RANK() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS rnk
  FROM SalesFact
)
SELECT * FROM ranked WHERE rnk <= 3;

Pattern 3: Period-over-period comparison

SELECT
  Salesperson, Revenue,
  LAG(Revenue) OVER (PARTITION BY Salesperson ORDER BY OrderDate) AS PrevRevenue,
  Revenue - LAG(Revenue, 1, 0) OVER (PARTITION BY Salesperson ORDER BY OrderDate) AS Change_
FROM SalesFact;

Pattern 4: Running total

SELECT OrderDate, Revenue,
  SUM(Revenue) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS YTD
FROM SalesFact;

Pattern 5: Pivot without PIVOT clause

Turn row values into columns using CASE WHEN inside SUM — works in every database.

SELECT Salesperson,
  SUM(CASE WHEN Region='North' THEN Revenue ELSE 0 END) AS North,
  SUM(CASE WHEN Region='South' THEN Revenue ELSE 0 END) AS South,
  SUM(CASE WHEN Region='East' THEN Revenue ELSE 0 END) AS East
FROM SalesFact
GROUP BY Salesperson;
💡
Analyst tip: This CASE WHEN pivot pattern is the most portable SQL technique you will learn. PIVOT syntax varies by database and often requires knowing column values in advance. CASE WHEN inside SUM works everywhere and is fully dynamic when combined with application code.

Pattern 6: Data reconciliation

Find records in Table A that do not exist in Table B — the classic "find the gaps" query.

-- Orders in SalesFact with no matching Category
SELECT s.*
FROM SalesFact s
LEFT JOIN Category c ON s.CategoryID = c.CategoryID
WHERE c.CategoryID IS NULL;

Building your SQL toolkit

These eight patterns are not exhaustive — SQL has many more features. But they are the 20% that covers 80% of daily BI analytical work. Each time you encounter a new business question, map it to the closest pattern, adapt, and move on. Over time you will build your own library of variations.

🎯 Final Challenge

Combine Patterns 2 and 5: find the top salesperson in each region AND show their revenue split across all categories in the same result. You will need a CTE for the ranking, then CASE WHEN pivot in the outer query.

FAQ

They complement each other rather than one coming first. Power BI has Power Query for data prep, but SQL is often where the data already lives and where the cleanest, most efficient transformations happen. Learning both simultaneously — SQL for the database layer, Power BI for the presentation layer — is the most practical approach for BI work.
More than ever. Power BI connects to SQL databases and often runs SQL under the hood. Python data work almost always starts with SQL to extract and filter data before pandas processes it. SQL is the universal language of structured data — every other tool sits on top of it.
`