Quick summary
Summarize this blog with AI
A retail case with numbers you can verify
A retailer wants an eight-week performance review: which categories generate sales and margin, how weekly revenue changes, which products lead, how channels compare, where inventory needs attention, and what discount data can establish. This PostgreSQL 16 case uses fictional data with known answers. Run the setup and six queries in the same disposable learning session, then run the final ROLLBACK. All objects are temporary.
Agree on time and money before joining
The reporting cutoff is October 17, 2026. The sales period contains eight complete Monday-start weeks: August 17 inclusive through October 12 exclusive. The current partial week is excluded. Each item row is one product within an order; unit_price and unit_cost are per-unit, while line_discount is the total discount for the whole line. Revenue is quantity × unit price minus line discount. Margin subtracts quantity × unit cost. Currency, tax, shipping, and exchange rates are outside this fixture.
Only completed orders enter sales. A wholly refunded order is excluded. Partial refunds, separate refund dates, and revenue recognition would need a richer model; the chosen rule is not universal retail accounting. Product IDs and order IDs are unique in their dimensions, so each line joins to exactly one of each. Inventory is a dated snapshot and must not multiply the sales lines.
Build the complete fixture
BEGIN;
CREATE TEMP TABLE retail_products(product_id integer PRIMARY KEY,
name text,category text) ON COMMIT DROP;
INSERT INTO retail_products VALUES (101,'Book','Books'),
(102,'Guide','Books'),(201,'Game','Games');
CREATE TEMP TABLE retail_orders(order_id integer PRIMARY KEY,
order_date date,channel text,status text) ON COMMIT DROP;
INSERT INTO retail_orders VALUES
(501,'2026-08-18','web','completed'),
(502,'2026-09-01','store','completed'),
(503,'2026-10-06','web','completed'),
(504,'2026-10-08','web','refunded'),
(505,'2026-10-14','web','completed'),
(506,'2026-09-08','store','cancelled'),
(507,'2026-10-10','store','completed');
CREATE TEMP TABLE retail_items(order_id integer,product_id integer,
qty integer,unit_price numeric,unit_cost numeric,line_discount numeric,
PRIMARY KEY(order_id,product_id)) ON COMMIT DROP;
INSERT INTO retail_items VALUES
(501,101,2,10,6,0),(501,102,1,20,10,2),
(502,201,2,15,9,0),(503,101,1,10,6,0),
(504,101,999,10,6,0),(505,101,100,10,6,0),
(506,201,999,15,9,0),(507,101,1,10,6,0),(507,201,1,15,9,0);
CREATE TEMP TABLE retail_inventory(product_id integer,snapshot_date date,
on_hand integer,PRIMARY KEY(product_id,snapshot_date)) ON COMMIT DROP;
INSERT INTO retail_inventory VALUES
(101,'2026-10-16',3),(101,'2026-10-18',999),(201,'2026-10-10',0);
CREATE TEMP VIEW retail_params AS
SELECT DATE '2026-10-17' AS as_of,DATE '2026-08-17' AS start_week,
DATE '2026-10-12' AS end_week;
CREATE TEMP VIEW retail_fact AS
SELECT o.order_id,o.order_date,o.channel,p.product_id,p.name,p.category,
i.qty,i.line_discount,i.qty*i.unit_price-i.line_discount AS revenue,
i.qty*i.unit_cost AS cost
FROM pg_temp.retail_orders o
JOIN pg_temp.retail_items i USING(order_id)
JOIN pg_temp.retail_products p USING(product_id)
CROSS JOIN pg_temp.retail_params r
WHERE o.status='completed' AND o.order_date>=r.start_week
AND o.order_date<r.end_week;Orders 504 and 506 are status exclusions. Order 505 is in the partial current week and is also excluded, despite its large quantity. The clean fact has six item rows across four orders and revenue of 103. Those totals provide a quick reconciliation before answering any business question.
1. Category sales and margin
SELECT category,SUM(qty) AS units,COUNT(DISTINCT order_id) AS orders,
SUM(revenue) AS revenue,SUM(revenue-cost) AS margin
FROM pg_temp.retail_fact GROUP BY category ORDER BY category;category | units | orders | revenue | margin
Books | 5 | 3 | 58 | 24
Games | 3 | 2 | 45 | 18Category order counts sum to five while there are four distinct orders overall: order 507 contains both categories. Distinct order counts are not additive across categories. Revenue and margin do reconcile to 103 and 42 because each line belongs to one category.
2. Weekly revenue without skipping zero weeks
WITH weeks AS (
SELECT g::date AS week FROM pg_temp.retail_params p,
LATERAL generate_series(p.start_week::timestamp,
p.end_week::timestamp-INTERVAL '1 week',INTERVAL '1 week') g
), totals AS (
SELECT category,DATE_TRUNC('week',order_date)::date AS week,
SUM(revenue) AS revenue FROM pg_temp.retail_fact GROUP BY 1,2
), dense AS (
SELECT c.category,w.week,COALESCE(t.revenue,0) AS revenue
FROM (SELECT DISTINCT category FROM pg_temp.retail_products) c
CROSS JOIN weeks w LEFT JOIN totals t USING(category,week)
), compared AS (
SELECT *,LAG(revenue) OVER(PARTITION BY category ORDER BY week) AS prior
FROM dense
)
SELECT category,week,revenue,prior,revenue-prior AS change,
ROUND(100.0*(revenue-prior)/NULLIF(prior,0),2) AS growth_pct
FROM compared ORDER BY category,week;category | week | revenue | prior | change | growth_pct
Books | 2026-08-17 | 38 | NULL | NULL | NULL
Books | 2026-08-24 | 0 | 38 | -38 | -100.00
Books | 2026-08-31 | 0 | 0 | 0 | NULL
Books | 2026-09-07 | 0 | 0 | 0 | NULL
Books | 2026-09-14 | 0 | 0 | 0 | NULL
Books | 2026-09-21 | 0 | 0 | 0 | NULL
Books | 2026-09-28 | 0 | 0 | 0 | NULL
Books | 2026-10-05 | 20 | 0 | 20 | NULL
Games | 2026-08-17 | 0 | NULL | NULL | NULL
Games | 2026-08-24 | 0 | 0 | 0 | NULL
Games | 2026-08-31 | 30 | 0 | 30 | NULL
Games | 2026-09-07 | 0 | 30 | -30 | -100.00
Games | 2026-09-14 | 0 | 0 | 0 | NULL
Games | 2026-09-21 | 0 | 0 | 0 | NULL
Games | 2026-09-28 | 0 | 0 | 0 | NULL
Games | 2026-10-05 | 15 | 0 | 15 | NULLThe week spine makes LAG mean the immediately preceding calendar week. Without it, Games could compare October 5 with August 31. A zero prior denominator makes percentage growth undefined, so it remains NULL while the absolute change remains useful. The first week has no prior observation in this report. Zero sales assumes a complete feed; missing ingestion is not zero demand.
3. The top two products in each category
WITH totals AS (
SELECT category,product_id,name,SUM(revenue) AS revenue
FROM pg_temp.retail_fact GROUP BY category,product_id,name
), ranked AS (
SELECT *,ROW_NUMBER() OVER(
PARTITION BY category ORDER BY revenue DESC,product_id
) AS position FROM totals
)
SELECT category,position,product_id,name,revenue
FROM ranked WHERE position<=2 ORDER BY category,position;category | position | product_id | name | revenue
Books | 1 | 101 | Book | 40
Books | 2 | 102 | Guide | 18
Games | 1 | 201 | Game | 45ROW_NUMBER gives at most two rows per category and breaks revenue ties by product ID. If the business wants every product tied at second place, use a rank by revenue alone and explain that the output may contain more than two products.
4. Average order value by channel
WITH order_totals AS (
SELECT order_id,channel,SUM(revenue) AS revenue
FROM pg_temp.retail_fact GROUP BY order_id,channel
)
SELECT channel,COUNT(*) AS orders,SUM(revenue) AS revenue,
ROUND(AVG(revenue),2) AS average_order_value
FROM order_totals GROUP BY channel ORDER BY channel;channel | orders | revenue | average_order_value
store | 2 | 55 | 27.50
web | 2 | 48 | 24.00Average the order totals, not the line amounts. This fixture’s order totals are 38, 30, 10, and 25. Comparing channels also needs customer mix, acquisition costs, and uncertainty before making a business decision; four fictional orders establish the arithmetic only.
5. Recent sales and usable inventory evidence
WITH recent_sales AS (
SELECT f.product_id,SUM(f.qty) AS units
FROM pg_temp.retail_fact f CROSS JOIN pg_temp.retail_params p
WHERE f.order_date>=p.end_week-14 GROUP BY f.product_id
), latest_stock AS (
SELECT i.*,ROW_NUMBER() OVER(
PARTITION BY product_id ORDER BY snapshot_date DESC
) AS rn FROM pg_temp.retail_inventory i CROSS JOIN pg_temp.retail_params p
WHERE i.snapshot_date<p.as_of
)
SELECT pr.product_id,COALESCE(s.units,0) AS two_week_units,
i.snapshot_date,i.on_hand,
CASE WHEN i.product_id IS NULL THEN 'unknown'
WHEN i.snapshot_date<p.as_of-3 THEN 'stale'
ELSE 'fresh' END AS stock_status,
CASE WHEN i.snapshot_date>=p.as_of-3 AND s.units>0
THEN ROUND(2.0*i.on_hand/s.units,2) END AS weeks_at_recent_rate
FROM pg_temp.retail_products pr CROSS JOIN pg_temp.retail_params p
LEFT JOIN recent_sales s USING(product_id)
LEFT JOIN latest_stock i ON i.product_id=pr.product_id AND i.rn=1
ORDER BY pr.product_id;product_id | two_week_units | snapshot_date | on_hand | stock_status | weeks_at_recent_rate
101 | 2 | 2026-10-16 | 3 | fresh | 3.00
102 | 0 | NULL | NULL | unknown | NULL
201 | 1 | 2026-10-10 | 0 | stale | NULLSales use the last two completed weeks, September 28 through October 11. Inventory uses the latest snapshot strictly before the as-of date; the future October 18 snapshot cannot leak into the result. Fresh means no more than three calendar days old under this explicit policy. Missing and stale stock cannot support a reliable stockout conclusion. The rough coverage ratio is current stock divided by recent weekly sales; it is not a replenishment forecast and ignores lead time, seasonality, and incoming stock.
6. What the discount comparison actually says
SELECT category,line_discount>0 AS discounted,
SUM(revenue) AS revenue,SUM(revenue-cost) AS margin,
ROUND(100.0*SUM(revenue-cost)/NULLIF(SUM(revenue),0),2) AS margin_pct
FROM pg_temp.retail_fact GROUP BY category,line_discount>0
ORDER BY category,discounted;category | discounted | revenue | margin | margin_pct
Books | false | 40 | 16 | 40.00
Books | true | 18 | 8 | 44.44
Games | false | 45 | 18 | 40.00The discounted book line has a higher margin percentage in this fixture because it is a different product with different cost economics. This does not show that discounting caused a profit improvement. A causal claim needs an appropriate experiment or defensible comparison design, with quantities, baseline demand, costs, and customer selection accounted for.
Close with checked findings
A precise explanation is: “I fixed the reporting cutoff, reduced joins to the intended line grain, reconciled 103 in revenue, and used complete calendar weeks. I kept undefined growth and unknown inventory visible. I would investigate the stock feed before treating the Games zero as a current stockout.” These are findings from the fixture, not claims about a real retailer.
Check the assumptions that change the answer
Watch for counting line rows as orders, joining every inventory snapshot to every sale, comparing partial and complete weeks, treating absent inventory as zero, and interpreting discounted-versus-full-price averages as causal evidence. Validate refund policy and discount units with the schema owner. A syntactically valid query can still answer the wrong commercial question.
Finish and vary the fixture
ROLLBACK;Rerun the setup after changing one assumption at a time: add a revenue tie, remove all sales for a category, add a partial refund model, or supply a fresh Games stock snapshot. Predict the changed rows before executing. The last ROLLBACK removes this session’s setup. PostgreSQL references: generate_series, window functions, and date/time operations.
FAQ
Why retain zero-sales weeks? A complete time axis is necessary for calendar comparisons. Can this become a dashboard? Yes, after the accounting definitions, data completeness, freshness policy, and refresh cutoff are agreed. The fixture’s outputs are a regression baseline for those definitions.
