Inventory Turnover: SQL Case Study with 8 Approaches

Inventory turnover tells a retailer or manufacturer how many times it sells through its stock in a period. Too low means cash tied up in slow stock; too high can mean stockouts. The SQL draws on three tables at different grains (daily sales, month-end snapshots and a cost history), which makes it a strong interview case for joins, date logic and query performance. This case study computes it correctly and then covers eight related techniques.

The business question

“How quickly are we selling through our inventory, by product, and is it better than last year?” Operations uses turnover to set reorder points, finance to judge working capital, and category managers to spot dead stock.

The definition used here:

• Turnover = cost of goods sold (COGS) in the period ÷ average inventory value in the period. Both are at cost: mixing selling price in the numerator with cost in the denominator inflates turnover by the margin.
• COGS = units sold × the unit cost valid on the sale date, from a type 2 cost history.
• Inventory value at a snapshot = units on hand × the unit cost valid on the snapshot date.
• Average inventory for a quarter = the average of four snapshots: the opening snapshot (the previous year-end or quarter-end) and the three month-ends.
• Missing snapshot rows mean zero stock for products that were already launched: the warehouse system writes no row for an empty bin. Products not yet launched are excluded until their launch.
• Days of inventory (DIO) = days in the period ÷ turnover: roughly how many days current stock would last.
• Year-over-year: Q1 2026 compared with Q1 2025.

Schema and sample data

CREATE TABLE products (
  product_id  TEXT PRIMARY KEY,
  category    TEXT NOT NULL,
  launch_date DATE NOT NULL
);

CREATE TABLE product_costs (            -- type 2 slowly changing dimension
  product_id TEXT NOT NULL REFERENCES products,
  unit_cost  NUMERIC(10,2) NOT NULL,
  valid_from DATE NOT NULL,
  valid_to   DATE                       -- exclusive; NULL = current
);

CREATE TABLE inventory_snapshots (      -- month-end units on hand; no row = empty
  snapshot_date DATE NOT NULL,
  product_id    TEXT NOT NULL REFERENCES products,
  on_hand_units INT NOT NULL,
  PRIMARY KEY (snapshot_date, product_id)
);

CREATE TABLE sales (
  sale_date  DATE NOT NULL,
  product_id TEXT NOT NULL REFERENCES products,
  units      INT NOT NULL
);

INSERT INTO products VALUES ('A', 'fast', '2020-01-01'), ('B', 'slow', '2020-01-01'), ('C', 'new', '2026-01-01');

INSERT INTO product_costs VALUES
  ('A', 5.00,  '2020-01-01', '2026-01-15'),
  ('A', 5.50,  '2026-01-15', NULL),          -- supplier price rise mid-January
  ('B', 20.00, '2020-01-01', NULL),
  ('C', 8.00,  '2026-01-01', NULL);

INSERT INTO inventory_snapshots VALUES
  ('2024-12-31','A',100), ('2025-01-31','A',80),                         ('2025-03-31','A',120),
  ('2024-12-31','B',200), ('2025-01-31','B',190), ('2025-02-28','B',185), ('2025-03-31','B',180),
  ('2025-12-31','A',90),  ('2026-01-31','A',60),  ('2026-02-28','A',70),  ('2026-03-31','A',100),
  ('2025-12-31','B',150), ('2026-01-31','B',140), ('2026-02-28','B',160), ('2026-03-31','B',150),
                          ('2026-01-31','C',50),  ('2026-02-28','C',40),  ('2026-03-31','C',30);
-- A has no row on 2025-02-28: it was out of stock. C launched on 2026-01-01.

INSERT INTO sales VALUES
  ('2025-01-15','A',120), ('2025-02-15','A',150), ('2025-03-15','A',130),
  ('2025-01-15','B',10),  ('2025-02-15','B',5),   ('2025-03-15','B',5),
  ('2026-01-10','A',80),  ('2026-01-20','A',80),  ('2026-02-15','A',140), ('2026-03-15','A',170),
  ('2026-01-15','B',10),  ('2026-02-15','B',12),  ('2026-03-15','B',10),
  ('2026-02-15','C',10),  ('2026-03-15','C',10);

Core solution

Build the full product × snapshot-date grid, fill missing snapshots with zero, value everything at the cost valid on the date, and divide.

WITH periods AS (
  SELECT * FROM (VALUES
    ('2025-Q1', DATE '2025-01-01', DATE '2025-04-01', DATE '2024-12-31'),
    ('2026-Q1', DATE '2026-01-01', DATE '2026-04-01', DATE '2025-12-31')
  ) AS t(period, start_date, end_date, opening_date)
),
snapshot_dates AS (                       -- opening date plus each month-end in the period
  SELECT p.period, p.opening_date AS snapshot_date FROM periods p
  UNION ALL
  SELECT p.period, (m + INTERVAL '1 month' - INTERVAL '1 day')::date
  FROM periods p
  CROSS JOIN generate_series(p.start_date, p.end_date - 1, INTERVAL '1 month') AS m
),
grid AS (                                 -- every launched product at every snapshot date
  SELECT d.period, d.snapshot_date, pr.product_id
  FROM snapshot_dates d
  CROSS JOIN products pr
  WHERE pr.launch_date <= d.snapshot_date
),
inventory AS (
  SELECT g.period, g.product_id,
         AVG(COALESCE(s.on_hand_units, 0) * c.unit_cost) AS avg_inventory_value
  FROM grid g
  LEFT JOIN inventory_snapshots s
         ON s.snapshot_date = g.snapshot_date AND s.product_id = g.product_id
  JOIN product_costs c
    ON c.product_id = g.product_id
   AND c.valid_from <= g.snapshot_date AND (c.valid_to IS NULL OR c.valid_to > g.snapshot_date)
  GROUP BY g.period, g.product_id
),
cogs AS (
  SELECT p.period, s.product_id, SUM(s.units * c.unit_cost) AS cogs
  FROM sales s
  JOIN periods p ON s.sale_date >= p.start_date AND s.sale_date < p.end_date
  JOIN product_costs c
    ON c.product_id = s.product_id
   AND c.valid_from <= s.sale_date AND (c.valid_to IS NULL OR c.valid_to > s.sale_date)
  GROUP BY p.period, s.product_id
)
SELECT i.period, i.product_id,
       COALESCE(c.cogs, 0)                                       AS cogs,
       ROUND(i.avg_inventory_value, 2)                           AS avg_inventory,
       ROUND(COALESCE(c.cogs, 0) / NULLIF(i.avg_inventory_value, 0), 2) AS turnover,
       ROUND(90 / NULLIF(COALESCE(c.cogs, 0) / NULLIF(i.avg_inventory_value, 0), 0), 1) AS days_of_inventory
FROM inventory i
LEFT JOIN cogs c USING (period, product_id)
ORDER BY i.period, i.product_id;

period	product_id	cogs	avg_inventory	turnover	days_of_inventory
2025-Q1	A	2000.00	375.00	5.33	16.9
2025-Q1	B	400.00	3775.00	0.11	849.4
2026-Q1	A	2545.00	428.75	5.94	15.2
2026-Q1	B	640.00	3000.00	0.21	421.9
2026-Q1	C	160.00	320.00	0.50	180.0

The days figure uses 90 days for both quarters for simplicity; Q1 has 90 days in both 2025 and 2026, but in general use the actual days (end_date - start_date). Product A turns over five to six times a quarter (stock lasts about two weeks); product B turns over well under once (stock lasts more than a year). Product C is averaged over its three month-end snapshots only, because the grid leaves out dates before its launch; including a zero for 31 December would halve its turnover, so how a launch quarter is treated is a judgement to agree with finance.

Approach: cross joins to build the date-product grid

Why it matters. A CROSS JOIN returns every combination of rows from two inputs: the Cartesian product. Usually it is a bug (a missing join condition multiplies rows), but here it is the tool: the snapshot table has no row for product A on 28 February 2025, and averaging only the rows that exist would ignore the stockout and overstate average inventory.

SELECT d.snapshot_date, p.product_id,
       s.on_hand_units                AS raw_units,
       COALESCE(s.on_hand_units, 0)   AS units_filled
FROM (VALUES (DATE '2024-12-31'), (DATE '2025-01-31'), (DATE '2025-02-28'), (DATE '2025-03-31')) AS d(snapshot_date)
CROSS JOIN (SELECT product_id FROM products WHERE product_id IN ('A', 'B')) p
LEFT JOIN inventory_snapshots s
       ON s.snapshot_date = d.snapshot_date AND s.product_id = p.product_id
ORDER BY p.product_id, d.snapshot_date;

snapshot_date	product_id	raw_units	units_filled
2024-12-31	A	100	100
2025-01-31	A	80	80
2025-02-28	A	NULL	0
2025-03-31	A	120	120
2024-12-31	B	200	200
2025-01-31	B	190	190
2025-02-28	B	185	185
2025-03-31	B	180	180

Compare the two averages for product A in Q1 2025:

SELECT ROUND(AVG(on_hand_units), 1)                      AS avg_existing_rows_only,
       ROUND(SUM(on_hand_units) / 4.0, 1)                AS avg_with_stockout_as_zero
FROM inventory_snapshots
WHERE product_id = 'A' AND snapshot_date BETWEEN DATE '2024-12-31' AND DATE '2025-03-31';

avg_existing_rows_only	avg_with_stockout_as_zero
100.0	75.0

Ignoring the stockout makes average inventory a third higher (100 instead of 75), and turnover a quarter lower. The grid pattern (dates × entities, then LEFT JOIN the facts) is the standard way to make absent rows explicit; the same idea builds a calendar of all days for every store, or all hours for every sensor.

Pitfalls. Size the grid before running it: 365 days × 100,000 products is 36.5 million rows. Filter the entity side (only launched, not discontinued products) and the date side before crossing. An accidental Cartesian product shows up as a row count equal to the product of the input sizes; a quick check of COUNT(*) after each join catches it. In old comma-join syntax, FROM a, b with the WHERE condition forgotten is the classic source.

Approach: slowly changing dimension queries for point-in-time cost

Why it matters. Product A’s unit cost rose from 5.00 to 5.50 on 15 January 2026. Valuing every sale at the current cost (5.50) overstates COGS for the sale on 10 January; valuing at the first cost understates later sales. A type 2 history keeps one row per version with a validity range, and each fact joins to the version valid on its own date.

SELECT s.sale_date, s.product_id, s.units,
       c.unit_cost                      AS cost_at_sale_date,
       cur.unit_cost                    AS current_cost,
       s.units * c.unit_cost            AS cogs_point_in_time,
       s.units * cur.unit_cost          AS cogs_current_cost
FROM sales s
JOIN product_costs c
  ON c.product_id = s.product_id
 AND c.valid_from <= s.sale_date AND (c.valid_to IS NULL OR c.valid_to > s.sale_date)
JOIN product_costs cur
  ON cur.product_id = s.product_id AND cur.valid_to IS NULL
WHERE s.product_id = 'A' AND s.sale_date >= DATE '2025-12-01'
ORDER BY s.sale_date;

sale_date	product_id	units	cost_at_sale_date	current_cost	cogs_point_in_time	cogs_current_cost
2026-01-10	A	80	5.00	5.50	400.00	440.00
2026-01-20	A	80	5.50	5.50	440.00	440.00
2026-02-15	A	140	5.50	5.50	770.00	770.00
2026-03-15	A	170	5.50	5.50	935.00	935.00

Only the 10 January sale differs here, but on a price history with many changes the current-cost shortcut distorts every past period. Two data-quality checks keep point-in-time joins honest: every fact must match exactly one version (no gaps), and versions must not overlap.

SELECT product_id, valid_from, valid_to,
       LEAD(valid_from) OVER (PARTITION BY product_id ORDER BY valid_from) AS next_valid_from,
       CASE
         WHEN LEAD(valid_from) OVER w IS NULL AND valid_to IS NULL          THEN 'current'
         WHEN LEAD(valid_from) OVER w = valid_to                            THEN 'contiguous'
         WHEN LEAD(valid_from) OVER w > valid_to                            THEN 'GAP'
         ELSE 'OVERLAP'
       END AS check_result
FROM product_costs
WINDOW w AS (PARTITION BY product_id ORDER BY valid_from)
ORDER BY product_id, valid_from;

product_id	valid_from	valid_to	next_valid_from	check_result
A	2020-01-01	2026-01-15	2026-01-15	contiguous
A	2026-01-15	NULL	NULL	current
B	2020-01-01	NULL	NULL	current
C	2026-01-01	NULL	NULL	current

Pitfalls. Mixing inclusive and exclusive end dates causes a sale on the change date to match two versions (doubling COGS) or none (dropping it). BETWEEN valid_from AND valid_to is inclusive at both ends, so it double-matches on the boundary when valid_to equals the next valid_from. Late-arriving cost corrections require deciding whether past reports are restated, which is a business decision; record it.

Approach: LAG() and LEAD() for stock movement

Why it matters. Snapshots tell you levels; operations also needs movement: how much stock changed since the last snapshot, how much was received (stock change plus sales), and when the next snapshot shows a restock. LAG(x) reads the previous row’s value within the window, LEAD(x) the next one.

WITH filled AS (
  SELECT d.snapshot_date, p.product_id, COALESCE(s.on_hand_units, 0) AS on_hand
  FROM (VALUES (DATE '2025-12-31'), (DATE '2026-01-31'), (DATE '2026-02-28'), (DATE '2026-03-31')) AS d(snapshot_date)
  CROSS JOIN (VALUES ('A'), ('B')) AS p(product_id)
  LEFT JOIN inventory_snapshots s ON s.snapshot_date = d.snapshot_date AND s.product_id = p.product_id
),
monthly_sales AS (
  SELECT product_id, (date_trunc('month', sale_date) + INTERVAL '1 month - 1 day')::date AS month_end,
         SUM(units) AS units_sold
  FROM sales GROUP BY 1, 2
)
SELECT f.product_id, f.snapshot_date, f.on_hand,
       LAG(f.on_hand)  OVER w                              AS previous_on_hand,
       f.on_hand - LAG(f.on_hand) OVER w                   AS net_change,
       COALESCE(ms.units_sold, 0)                          AS units_sold,
       f.on_hand - LAG(f.on_hand) OVER w + COALESCE(ms.units_sold, 0) AS implied_receipts,
       LEAD(f.on_hand) OVER w                              AS next_on_hand
FROM filled f
LEFT JOIN monthly_sales ms ON ms.product_id = f.product_id AND ms.month_end = f.snapshot_date
WINDOW w AS (PARTITION BY f.product_id ORDER BY f.snapshot_date)
ORDER BY f.product_id, f.snapshot_date;

product_id	snapshot_date	on_hand	previous_on_hand	net_change	units_sold	implied_receipts	next_on_hand
A	2025-12-31	90	NULL	NULL	0	NULL	60
A	2026-01-31	60	90	-30	160	130	70
A	2026-02-28	70	60	10	140	150	100
A	2026-03-31	100	70	30	170	200	NULL
B	2025-12-31	150	NULL	NULL	0	NULL	140
B	2026-01-31	140	150	-10	10	0	160
B	2026-02-28	160	140	20	12	32	150
B	2026-03-31	150	160	-10	10	0	NULL

Implied receipts (closing − opening + sold) reconstruct the deliveries that the snapshots imply: product A received 130 units in January 2026. A negative value would mean stock disappeared beyond recorded sales (shrinkage, damage or a counting error), which is exactly what an inventory audit looks for. The first row of each product has no previous value, so LAG returns NULL; give a default with LAG(x, 1, 0) only when “zero before the first row” is actually true.

Pitfalls. LAG takes the previous row, not the previous month; fill the grid first, as here, or a missing month makes LAG compare January with March. Partition by product, or one product’s last row bleeds into the next product’s first.

Approach: conditional aggregation for opening, closing and averages

Why it matters. Many inventory reports need several figures from one table in one row: opening stock, closing stock, the average and the count of stockout months. Conditional aggregation computes each with its own condition (FILTER (WHERE ...) in PostgreSQL, SUM(CASE WHEN ... THEN ... END) everywhere) in a single pass.

WITH grid AS (
  SELECT d.snapshot_date, p.product_id, COALESCE(s.on_hand_units, 0) AS on_hand
  FROM (VALUES (DATE '2025-12-31'), (DATE '2026-01-31'), (DATE '2026-02-28'), (DATE '2026-03-31')) d(snapshot_date)
  CROSS JOIN products p
  LEFT JOIN inventory_snapshots s ON s.snapshot_date = d.snapshot_date AND s.product_id = p.product_id
  WHERE p.launch_date <= d.snapshot_date
)
SELECT product_id,
       MAX(on_hand) FILTER (WHERE snapshot_date = DATE '2025-12-31')  AS opening_units,
       MAX(on_hand) FILTER (WHERE snapshot_date = DATE '2026-03-31')  AS closing_units,
       ROUND(AVG(on_hand), 1)                                          AS avg_units,
       COUNT(*) FILTER (WHERE on_hand = 0)                             AS zero_stock_snapshots,
       SUM(CASE WHEN snapshot_date > DATE '2025-12-31' THEN on_hand ELSE 0 END) AS sum_month_end_units
FROM grid
GROUP BY product_id
ORDER BY product_id;

product_id	opening_units	closing_units	avg_units	zero_stock_snapshots	sum_month_end_units
A	90	100	80.0	0	230
B	150	150	150.0	0	450
C	NULL	30	40.0	0	120

Product C has no opening value (NULL) because it did not exist on 31 December; the FILTER found no row, which is different from a zero. Conditional aggregation also produces the unit-based turnover for both years side by side, a common pivoted layout:

SELECT product_id,
       SUM(units) FILTER (WHERE sale_date >= DATE '2025-01-01' AND sale_date < DATE '2025-04-01') AS q1_2025_units,
       SUM(units) FILTER (WHERE sale_date >= DATE '2026-01-01' AND sale_date < DATE '2026-04-01') AS q1_2026_units
FROM sales
GROUP BY product_id
ORDER BY product_id;

product_id	q1_2025_units	q1_2026_units
A	400	470
B	20	32
C	NULL	20

Pitfalls. COUNT(*) FILTER (...) and SUM(CASE ... ELSE 0 END) return 0 for no matches, while SUM(x) FILTER (...), MAX and AVG return NULL; pick deliberately. AVG(CASE WHEN cond THEN x END) averages only matching rows, but AVG(CASE WHEN cond THEN x ELSE 0 END) counts non-matching rows as zeros, a frequent silent error.

Approach: year-over-year growth

Why it matters. Inventory follows seasons, so this quarter is compared with the same quarter last year rather than with last quarter. Year-over-year (YoY) growth = (this year − last year) / last year.

Compute the metric per product and quarter, then align each row with the same quarter a year earlier. With a contiguous quarterly series, LAG(x, 4) reaches back four quarters; with only the two Q1 rows here, a self-join on year - 1 is more robust and works for any gaps:

WITH quarterly_cogs AS (
  SELECT s.product_id,
         EXTRACT(YEAR FROM s.sale_date)::int    AS yr,
         EXTRACT(QUARTER FROM s.sale_date)::int AS qtr,
         SUM(s.units * c.unit_cost)             AS cogs,
         SUM(s.units)                           AS units
  FROM sales s
  JOIN product_costs c
    ON c.product_id = s.product_id
   AND c.valid_from <= s.sale_date AND (c.valid_to IS NULL OR c.valid_to > s.sale_date)
  GROUP BY 1, 2, 3
)
SELECT cur.product_id, cur.yr, cur.qtr,
       cur.cogs, prev.cogs AS cogs_last_year,
       ROUND(100.0 * (cur.cogs - prev.cogs) / NULLIF(prev.cogs, 0), 1)    AS cogs_yoy_pct,
       ROUND(100.0 * (cur.units - prev.units) / NULLIF(prev.units, 0), 1) AS units_yoy_pct
FROM quarterly_cogs cur
LEFT JOIN quarterly_cogs prev
       ON prev.product_id = cur.product_id AND prev.qtr = cur.qtr AND prev.yr = cur.yr - 1
WHERE cur.yr = 2026
ORDER BY cur.product_id;

product_id	yr	qtr	cogs	cogs_last_year	cogs_yoy_pct	units_yoy_pct
A	2026	1	2545.00	2000.00	27.3	17.5
B	2026	1	640.00	400.00	60.0	60.0
C	2026	1	160.00	NULL	NULL	NULL

Product A’s COGS grew faster than its units (27.3% against 17.5%) partly because of the cost increase; separating volume and price effects like this is a standard follow-up. Product C has no prior year, so growth is NULL, not infinite or zero; show it as “new”.

For turnover itself, divide the two quarterly turnovers from the core solution:

WITH t AS (
  SELECT * FROM (VALUES ('A', 2025, 5.33), ('A', 2026, 5.94), ('B', 2025, 0.11), ('B', 2026, 0.21)) v(product_id, yr, turnover)
)
SELECT cur.product_id, prev.turnover AS turnover_2025, cur.turnover AS turnover_2026,
       ROUND(cur.turnover - prev.turnover, 2)                         AS change_in_turns,
       ROUND(100.0 * (cur.turnover - prev.turnover) / prev.turnover, 1) AS yoy_pct
FROM t cur JOIN t prev ON prev.product_id = cur.product_id AND prev.yr = cur.yr - 1
ORDER BY cur.product_id;

product_id	turnover_2025	turnover_2026	change_in_turns	yoy_pct
A	5.33	5.94	0.61	11.4
B	0.11	0.21	0.10	90.9

For ratios like turnover, quoting the change in turns (B went from 0.11 to 0.21) is often clearer than a percentage, because percentage changes of small ratios look dramatic. Pitfalls: align by period, not by row position, when quarters can be missing; account for calendar differences (an extra week in a retail calendar, a leap day); and use the same definitions in both years, which is harder than it sounds after a cost-method change.

Approach: avoiding full table scans on snapshot tables

Why it matters. Snapshot tables grow by one row per product per day or month forever. A query for one quarter should not read ten years. Two features help: a sargable date filter, and an index type suited to append-only, time-ordered data.

A synthetic daily snapshot table of about 1.1 million rows, inserted in date order:

CREATE TABLE snapshots_big AS
SELECT d::date AS snapshot_date, p AS product_id, ((p * 13 + EXTRACT(DOY FROM d)::int) % 200) AS on_hand
FROM generate_series(DATE '2023-01-01', DATE '2026-03-31', INTERVAL '1 day') AS d
CROSS JOIN generate_series(1, 920) AS p
ORDER BY snapshot_date, product_id;          -- physical order follows the date, as in a daily load
VACUUM ANALYZE snapshots_big;

A function on the date column forces every row to be read:

SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT AVG(on_hand) FROM snapshots_big
WHERE EXTRACT(YEAR FROM snapshot_date) = 2026 AND EXTRACT(QUARTER FROM snapshot_date) = 1;

Aggregate (actual rows=1 loops=1)
  ->  Seq Scan on snapshots_big (actual rows=82800 loops=1)
        Filter: ((EXTRACT(year FROM snapshot_date) = '2026'::numeric) AND (EXTRACT(quarter FROM snapshot_date) = '1'::numeric))
        Rows Removed by Filter: 1008320

A BRIN (block range) index stores only the minimum and maximum date for each range of table pages. Because rows were inserted in date order, each block range covers a narrow date span, so the index is tiny and lets PostgreSQL skip almost every block for a date-range query:

CREATE INDEX snapshots_big_brin ON snapshots_big USING brin (snapshot_date);
SET max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT AVG(on_hand) FROM snapshots_big
WHERE snapshot_date >= DATE '2026-01-01' AND snapshot_date < DATE '2026-04-01';

Aggregate (actual rows=1 loops=1)
  ->  Bitmap Heap Scan on snapshots_big (actual rows=82800 loops=1)
        Recheck Cond: ((snapshot_date >= '2026-01-01'::date) AND (snapshot_date < '2026-04-01'::date))
        Rows Removed by Index Recheck: 13760
        Heap Blocks: lossy=576
        ->  Bitmap Index Scan on snapshots_big_brin (actual rows=5760 loops=1)
              Index Cond: ((snapshot_date >= '2026-01-01'::date) AND (snapshot_date < '2026-04-01'::date))

The table is now read through a bitmap of 576 block ranges’ worth of pages (Heap Blocks: lossy=576) instead of every page. “Lossy” means BRIN only knows which block ranges might contain matching dates, so every row in those blocks is rechecked, and 13,760 rows just outside the range are discarded. That is the trade: a slightly imprecise index that is tiny. The size difference against a B-tree on the same column is the reason to choose BRIN for this pattern:

CREATE INDEX snapshots_big_btree ON snapshots_big (snapshot_date);
SELECT pg_size_pretty(pg_relation_size('snapshots_big'))       AS table_size,
       pg_size_pretty(pg_relation_size('snapshots_big_brin'))  AS brin_size,
       pg_size_pretty(pg_relation_size('snapshots_big_btree')) AS btree_size;

table_size	brin_size	btree_size
47 MB	24 kB	7440 kB

Ways to avoid full scans, in order of preference.

• Sargable predicates: compare the raw column with constants; no EXTRACT, casts or functions on the column.
• Partitioning by month or year, so whole partitions are skipped (partition pruning) and old data can be detached cheaply.
• BRIN for large, append-only tables whose physical order follows the filter column; useless if rows are updated or inserted out of order.
• B-tree for selective lookups (one product’s history).
• Summary tables: a monthly snapshot table answers turnover without touching daily rows.
• In warehouses, the equivalent of BRIN is built in: per-file or per-micro-partition min/max metadata lets the engine skip data when the table is clustered by date.

Approach: index selectivity and cardinality

Why it matters. An index helps only when a predicate is selective: it matches a small fraction of rows. Selectivity depends on the column’s cardinality (number of distinct values) and on how values are distributed. A filter on a column with five values, each a fifth of the table, matches 20% of rows, and fetching 20% of a table row by row through an index (random page reads) is often slower than scanning it, unless the reads can be batched.

A stock-movements table of 30,000 rows with a low-cardinality warehouse_id (5 values) and a high-cardinality product_id (3,000 values). The table is small enough that ANALYZE reads every row, so the statistics shown are exact:

CREATE TABLE movements AS
SELECT g AS movement_id,
       (g % 5) + 1                       AS warehouse_id,
       (g * 7) % 3000 + 1                AS product_id,
       CASE WHEN g % 100 = 0 THEN 'adjustment' ELSE 'sale' END AS movement_type,
       (g % 20) + 1                      AS units
FROM generate_series(1, 30000) AS g;
CREATE INDEX movements_wh   ON movements (warehouse_id);
CREATE INDEX movements_prod ON movements (product_id);
CREATE INDEX movements_type ON movements (movement_type);
VACUUM ANALYZE movements;

SELECT attname, n_distinct, most_common_vals::text AS most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'movements' AND attname IN ('warehouse_id', 'product_id', 'movement_type')
ORDER BY attname;

attname	n_distinct	most_common_vals	correlation
movement_type	2	{sale,adjustment}	0.98010087
product_id	3000	{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100}	0.014387256
warehouse_id	5	{1,2,3,4,5}	0.19999999

n_distinct is the planner’s distinct count (a negative value means a fraction of the row count, so −0.1 means “10% of rows are distinct”). The planner uses these numbers to estimate how many rows each filter returns, and from that whether an index is worth using. Compare a 20% filter on the warehouse with a one-in-3,000 filter on the product:

EXPLAIN SELECT SUM(units) FROM movements WHERE warehouse_id = 3;

Aggregate  (cost=353.79..353.80 rows=1 width=8)
  ->  Bitmap Heap Scan on movements  (cost=70.79..338.79 rows=6000 width=4)
        Recheck Cond: (warehouse_id = 3)
        ->  Bitmap Index Scan on movements_wh  (cost=0.00..69.29 rows=6000 width=0)
              Index Cond: (warehouse_id = 3)

EXPLAIN SELECT SUM(units) FROM movements WHERE product_id = 42;

Aggregate  (cost=37.69..37.70 rows=1 width=8)
  ->  Bitmap Heap Scan on movements  (cost=4.37..37.66 rows=10 width=4)
        Recheck Cond: (product_id = 42)
        ->  Bitmap Index Scan on movements_prod  (cost=0.00..4.36 rows=10 width=0)
              Index Cond: (product_id = 42)

Low cardinality is not automatically bad. movement_type has only two values, but they are skewed: adjustments are 1% of rows. The most-common-values list tells the planner this, so the rare value uses the index and the common value does not:

EXPLAIN SELECT COUNT(*) FROM movements WHERE movement_type = 'adjustment';

Aggregate  (cost=10.29..10.30 rows=1 width=8)
  ->  Index Only Scan using movements_type on movements  (cost=0.29..9.54 rows=300 width=0)
        Index Cond: (movement_type = 'adjustment'::text)

EXPLAIN SELECT COUNT(*) FROM movements WHERE movement_type = 'sale';

Aggregate  (cost=642.25..642.26 rows=1 width=8)
  ->  Seq Scan on movements  (cost=0.00..568.00 rows=29700 width=0)
        Filter: (movement_type = 'sale'::text)

Both use the index here, but in different ways. For product_id = 42 the planner expects 10 rows, so the cost is a few page reads. For warehouse_id = 3 it expects 6,000 rows (20%) and chooses a bitmap scan: it collects all matching row locations from the index first, then reads the table pages in physical order, each at most once. That avoids random I/O, which is why a bitmap scan can beat a sequential scan even at 20% on a small table; on a large table, or at higher selectivity, the planner switches to a sequential scan, as the movement_type = 'sale' example above shows. Note the estimated cost: the warehouse query costs about ten times the product query for the same index type.

Design lessons.

• Index high-cardinality, selective columns used in filters and joins (product, order, customer ids).
• For skewed low-cardinality columns, a partial index on the rare value (WHERE movement_type = 'adjustment') is smaller and just as useful.
• In a composite index, a low-cardinality column can still be useful as the leading column when every query filters on it (warehouse first, then product).
• Bitmap indexes, common in warehouse engines and Oracle, are designed for low-cardinality columns combined with AND/OR; PostgreSQL builds bitmaps at query time from B-trees instead (the Bitmap Index Scan above).

Approach: cardinality estimation and correlated columns

Why it matters. The planner chooses join order, join algorithm and memory use from its row estimates. Bad estimates cause most “it was fast yesterday” plan regressions. A classic cause is correlated columns: the planner assumes conditions on two columns are independent and multiplies their selectivities, which badly underestimates rows when one column determines the other.

In the movements table, each product lives in exactly one warehouse in a real stock system. Build that dependency explicitly:

CREATE TABLE stock AS
SELECT g AS stock_id,
       (g % 3000) + 1        AS product_id,
       ((g % 3000) % 5) + 1  AS warehouse_id,   -- determined by product_id
       (g % 40) + 1          AS units
FROM generate_series(1, 30000) AS g;
ANALYZE stock;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT COUNT(*) FROM stock WHERE product_id = 42 AND warehouse_id = 2;

Aggregate  (cost=642.00..642.01 rows=1 width=8) (actual rows=1 loops=1)
  ->  Seq Scan on stock  (cost=0.00..642.00 rows=2 width=0) (actual rows=10 loops=1)
        Filter: ((product_id = 42) AND (warehouse_id = 2))
        Rows Removed by Filter: 29990

Compare rows= in the estimate with actual rows=: the planner multiplied the selectivity of product_id = 42 (10 rows of 30,000) by that of warehouse_id = 2 (one fifth) and expected 2 rows, while all 10 matched, because product 42 is always in warehouse 2. Extended statistics teach the planner the dependency:

CREATE STATISTICS stock_prod_wh (dependencies) ON product_id, warehouse_id FROM stock;
ANALYZE stock;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT COUNT(*) FROM stock WHERE product_id = 42 AND warehouse_id = 2;

Aggregate  (cost=642.02..642.03 rows=1 width=8) (actual rows=1 loops=1)
  ->  Seq Scan on stock  (cost=0.00..642.00 rows=10 width=0) (actual rows=10 loops=1)
        Filter: ((product_id = 42) AND (warehouse_id = 2))
        Rows Removed by Filter: 29990

On a toy query the plan does not change, but in a join chain a 5× underestimate per condition compounds: an estimate of 2 rows can make the planner pick a nested loop that then runs thousands of times. Sources of bad estimates, and their fixes:

Cause	Symptom	Fix
Stale statistics after a big load	Estimates far below actual on recent dates	ANALYZE after loads; autovacuum tuning
Correlated columns	Underestimate when filtering on both	CREATE STATISTICS (dependencies) or (mcv)
Expressions on columns	Default guess (often a fixed fraction)	Statistics on the expression, or a stored column
Skewed values not in the MCV list	Wrong plan for rare or hot values	Raise the column’s statistics target
Values beyond the last ANALYZE	New dates estimated as nearly empty	Analyze more often on append-only tables

EXPLAIN ANALYZE is the tool: find the lowest plan node where estimated and actual rows diverge by an order of magnitude, and fix the statistics for that node’s condition.

Interview tips

How it is asked. “Calculate inventory turnover by product for last quarter”, “days of inventory by category”, “which products have more than 180 days of stock”, “compare turnover with last year”, or a schema with snapshots that have missing rows.

What a strong answer includes.

1. The formula with both sides at cost, and the averaging rule for inventory.
2. Treatment of missing snapshots (zero stock versus missing data) using a product × date grid.
3. Point-in-time cost joins for COGS and for inventory value.
4. YoY comparison by period alignment, with NULL for new products.
5. Performance sense: sargable date filters, BRIN or partitions for snapshots, and checking estimates in plans.

Mistakes candidates make.

• Revenue in the numerator and cost in the denominator.
• Averaging only the snapshot rows that exist, which hides stockouts.
• Valuing all history at the current cost.
• Using LAG on a series with missing months.
• Expecting an index on a five-value column to speed up a 20% filter.
• Blaming the planner instead of reading estimated versus actual rows.