SQL Patterns Every Working Analyst Should Own

SQL is the most durable skill in analytics. BI tools rotate every few years, notebooks come and go, but the relational query has outlived four decades of fashion and will outlive the current one. Yet most practitioners plateau early: comfortable with SELECT, JOIN, and GROUP BY, and quietly exporting to a spreadsheet whenever the question gets structural.

The distance between that plateau and production-grade analytical SQL is not a hundred features. In my experience it is about seven patterns, each of which converts a whole category of "I'll just do it in Excel" into a query that runs in seconds, documents itself, and survives the next refresh. This article walks through them with the business question each one answers.

1. CTEs as paragraphs, not tricks

A common table expression is not an advanced feature; it is punctuation. A well-written analytical query reads like a short essay: each WITH block is a paragraph with a name, a single responsibility, and an output you could inspect on its own.

WITH orders_clean AS (          -- dedupe and standardize the raw feed
  ...
),
daily_revenue AS (              -- aggregate to the reporting grain
  ...
),
with_targets AS (               -- join the plan for variance
  ...
)
SELECT * FROM with_targets;

The discipline matters more than the syntax: name blocks after what they produce, keep each block at one grain, and never nest a subquery three levels deep when a named step would do. Six months later, the analyst who inherits your query (usually you) reconstructs the logic in one read. Readability is a data quality control, not a stylistic preference.

2. Window functions: aggregate without collapsing

The single highest-leverage concept in analytical SQL is the window function: the ability to compute an aggregate while keeping every row. Running totals, shares of category, gaps between events, moving averages, all of it flows from one idea.

SELECT
  order_date,
  customer_id,
  net_amount,
  SUM(net_amount) OVER (PARTITION BY customer_id
                        ORDER BY order_date)      AS customer_running_total,
  net_amount / SUM(net_amount) OVER (PARTITION BY order_date)
                                                  AS share_of_day
FROM orders_clean;

If you find yourself joining a table to an aggregated copy of itself, you almost always wanted a window. The mental unlock is that PARTITION BY answers "compared to what group" and ORDER BY answers "accumulated in what sequence." Every executive question of the form "versus its category" or "to date" is a window function wearing business clothes.

3. ROW_NUMBER as the deduplication scalpel

Real feeds contain duplicates: retried webhooks, re-sent files, double-fired events. The professional pattern is explicit and auditable:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_id
                            ORDER BY loaded_at DESC) AS rn
  FROM raw_orders
)
SELECT * FROM ranked WHERE rn = 1;

Note what this pattern forces you to decide out loud: what defines a duplicate (PARTITION BY) and which copy wins (ORDER BY). A SELECT DISTINCT hides both decisions; ROW_NUMBER documents them. When finance asks why yesterday's revenue changed, the answer is in the query, not in folklore.

"Every DISTINCT in a production query is an undocumented business rule. Windowed deduplication is the same rule with its reasoning attached."

Rubansi Vincent

4. The date spine: report what did not happen

Transactional tables only contain days on which something happened, and dashboards built directly on them silently skip the zeros: the store that sold nothing on Tuesday, the rep with no calls last week. Zeros are usually the finding. The remedy is a date spine, a generated calendar cross-joined to your reporting entities, with facts left-joined on:

WITH spine AS (
  SELECT d::date AS report_date
  FROM generate_series('2026-01-01'::date, CURRENT_DATE, '1 day') AS g(d)
)
SELECT s.report_date,
       st.store_id,
       COALESCE(SUM(o.net_amount), 0) AS revenue
FROM spine s
CROSS JOIN dim_store st
LEFT JOIN orders_clean o
  ON o.order_date = s.report_date AND o.store_id = st.store_id
GROUP BY 1, 2;

The same spine becomes your date dimension in the BI model, which is why analysts who learn this pattern in SQL rarely fight time-intelligence bugs in DAX later.

5. Anti-joins: the auditor's favorite query

Some of the most valuable queries return rows that should not exist: payments without orders, orders without customers, subscriptions active in billing but cancelled in the CRM. The anti-join is how reconciliation thinking becomes code:

SELECT p.payment_id, p.amount
FROM payments p
LEFT JOIN orders o USING (order_id)
WHERE o.order_id IS NULL;   -- money with no known cause

I keep a small suite of these as scheduled checks on every engagement. An anti-join that returns zero rows every morning is the cheapest audit opinion a business will ever buy; the morning it returns forty rows, it has paid for the entire analytics program.

6. The cohort query: one shape, many questions

Retention, repeat purchase, payback: the cohort analysis behind all of them is a single SQL shape. Assign each customer to a cohort with a windowed first-event date, measure activity in periods since that date, and pivot in the BI layer:

WITH first_order AS (
  SELECT customer_id,
         DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders_clean
  GROUP BY 1
)
SELECT f.cohort_month,
       DATE_TRUNC('month', o.order_date) AS activity_month,
       COUNT(DISTINCT o.customer_id)     AS active_customers
FROM orders_clean o
JOIN first_order f USING (customer_id)
GROUP BY 1, 2;

Once this shape is in muscle memory, half of growth analytics is parameter substitution: swap orders for logins, months for weeks, customers for accounts.

7. Query hygiene: the habits that compound

The patterns above fail without the surrounding discipline, which costs nothing and pays daily:

• State the grain in a comment at the top of every query. One row per what? Most wrong numbers are grain accidents.
• Filter early, aggregate late. Push WHERE clauses into the first CTE; the optimizer and your warehouse bill will both thank you.
• Never SELECT * in anything scheduled. Schema drift upstream becomes silent breakage downstream.
• Reconcile the result to a control total before publishing: total revenue from the query versus the ledger, row counts versus the source. Two lines of SQL, hours of credibility.
• Version control your queries. Analytical SQL is code; a query that only exists inside a dashboard tile is a liability with formatting.

"Amateurs optimize the query. Professionals optimize the next analyst's ability to trust it."

Rubansi Vincent

The compounding effect

None of these patterns is exotic, and that is precisely the point. A team whose analysts own these seven shapes answers structural questions in SQL, at the warehouse, at full data volume, with logic that reviews like prose. The alternative, and I have audited plenty of them, is a shadow economy of exported CSVs and fragile spreadsheet pivots, where every number is one paste error away from a bad decision.

Make the shift to data-centric decision-making

Strong SQL is not an engineering vanity; it is the substrate of trustworthy decisions. When your queries dedupe explicitly, report the zeros, reconcile to the ledger, and read like documentation, the numbers that reach your leadership meetings deserve the confidence placed in them.

If your team's analysis still detours through exports and manual pivots, I can help: I build analytical SQL layers on Postgres and BigQuery that your future analysts will thank you for, and I train small teams on exactly these patterns. Tell me the question your data will not answer cleanly, and I will come back with an approach, a timeline, and a flat quote. Decisions run on queries; make yours worth running.