Next steps: analytics, data modelling and practice

Analytical query patterns built from everything before, star schemas and slowly changing dimensions, and how to keep improving after the course ends.

Analytical patterns worth knowing

-- share of the whole, without joining back to a totals subquery
SELECT category,
       SUM(revenue) AS revenue,
       ROUND(100.0 * SUM(revenue) / SUM(SUM(revenue)) OVER (), 1) AS pct_of_total
FROM sales
GROUP BY category
ORDER BY revenue DESC;

-- month-over-month change
WITH m AS (
  SELECT date_trunc('month', placed_at) AS month, SUM(total) AS revenue
  FROM orders GROUP BY 1
)
SELECT month,
       revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM m ORDER BY month;

-- cohort retention: the month of first purchase, then activity per month
WITH first_seen AS (
  SELECT customer_id, MIN(date_trunc('month', placed_at)) AS cohort
  FROM orders GROUP BY customer_id
)
SELECT f.cohort,
       date_trunc('month', o.placed_at) AS month,
       COUNT(DISTINCT o.customer_id) AS active
FROM orders o
JOIN first_seen f ON f.customer_id = o.customer_id
GROUP BY 1, 2
ORDER BY 1, 2;

-- funnel with every step visible, including the ones with no events
SELECT COUNT(*) FILTER (WHERE viewed) AS viewed,
       COUNT(*) FILTER (WHERE added)  AS added,
       COUNT(*) FILTER (WHERE paid)   AS paid
FROM sessions;

Pattern	Technique	Typical mistake
Percentage of total	A window aggregate over the grouped result	Joining a totals subquery back in, which multiplies rows
Running total	`SUM(x) OVER (ORDER BY d)`	Forgetting `PARTITION BY` and summing across unrelated entities
Month-over-month	`LAG` inside a CTE	Dividing by zero in the first period — wrap the denominator in `NULLIF`
Cohort retention	`MIN()` to define the cohort, then join	Counting rows instead of `DISTINCT` customers
Funnel	`COUNT(*) FILTER (WHERE ...)` or `SUM(CASE WHEN ...)`	Inner joins that silently drop the steps nobody reached
Top per group	`ROW_NUMBER() OVER (PARTITION BY ...)`	Using `RANK()`, which repeats values on ties and returns more rows than wanted

Star schemas and changing dimensions

Analytical schemas are shaped differently from transactional ones. A star schema puts a few large fact tables in the middle — one row per event, with numeric measures and foreign keys — and surrounds them with denormalised dimension tables for date, customer, product and geography.

CREATE TABLE fact_orders (              -- one row per order line
  order_id     BIGINT,
  date_key     INT      REFERENCES dim_date (date_key),
  customer_key BIGINT   REFERENCES dim_customer (customer_key),
  product_key  BIGINT   REFERENCES dim_product (product_key),
  quantity     INT      NOT NULL,
  net_amount   NUMERIC(12,2) NOT NULL
);

CREATE TABLE dim_customer (             -- current attributes, one row per customer
  customer_key BIGINT PRIMARY KEY,      -- warehouse key
  customer_id  BIGINT NOT NULL,         -- operational key
  name         TEXT,
  segment      TEXT,
  valid_from   DATE NOT NULL DEFAULT CURRENT_DATE,
  valid_to     DATE,                    -- NULL means still current
  is_current   BOOLEAN NOT NULL DEFAULT TRUE
);

💡

Dimensions change: a customer moves from small to enterprise. Type 1 overwrites the row and rewrites history; Type 2 adds a new row with valid_from and valid_to so old facts keep the segment that was true when they happened. Decide deliberately — the distinction cannot be reconstructed later.

How to keep improving

-- a self-check exercise list: write each of these against your own data
-- 1. monthly revenue with month-over-month change
-- 2. the top 3 products per category by revenue, in one query
-- 3. customers who bought in two consecutive months
-- 4. a funnel over an events table with no inner joins
-- 5. the same report as a materialised view refreshed nightly
-- 6. the query plan for number 2, and one change that makes it faster

Rebuild one report you already own as a single query, then compare every number with the old version until they match.
Load a public dataset into a scratch database and answer ten questions using GROUP BY, a CTE and a window function each.
Read your engine's own documentation for window functions, date arithmetic and EXPLAIN — it is more accurate than any tutorial, including this one.
Keep a file of queries you had to think about. That file becomes your reference, and writing it down is how the lesson sticks.
Pick one slow query you actually run, read its plan, change exactly one thing, and measure again.

FAQ

Where should analytics queries run?

Against a read replica or a warehouse, not the primary database serving users. One unindexed aggregation on the primary can hold locks and slow down every checkout.

Do I still need transactions if I mostly write reports?

On the loading side, yes. A report built from a half-loaded batch is worse than a slow report, so load in a single transaction or publish by swapping a view only once the load is complete.

Portable SQL across database engines Query tuning and execution plans

Last refreshed 2026-09-18.

Next steps: analytics, data modelling and practice

Analytical patterns worth knowing

Star schemas and changing dimensions

How to keep improving

FAQ

Related