Project 2: Sales Analytics Reporting Layer

A finance lead walks over and asks six questions. How much did we sell each month, and is it growing? What sells best? Who are our best customers? Do people come back? What is about to run out? Why are two reports giving different totals?

You will answer all of them from the course shop database, then do the part that separates a pile of queries from a reporting layer: package the answers so they stay correct, stay fast and can be handed to analysts safely.

Requirements#

1. One agreed definition of "revenue": the sum of quantity * unit_price on non-cancelled orders. It must live in exactly one place.
2. Reports for: monthly revenue with growth, best-selling products, top product per category, customer value and segments, repeat purchase rate, cohort retention, and low stock.
3. Everything analysts need is exposed through views. Analysts cannot touch the base tables.
4. The monthly revenue report is stored and refreshed, not recomputed on every read.
5. Every headline number has an automated check.
6. A short written findings file states what the data says, in plain language.

Load the course schema from Module 1 first. All numbers below are for the unmodified seed data.

Step 1: Agree on the definition#

Start here, because everything else depends on it. The recurring rule is "ignore cancelled orders", and it has been copied into nearly every query in this course. That is exactly how two reports end up disagreeing.

Create the single source of truth: a view that returns one row per order line, with the rule applied once.

CREATE VIEW v_revenue_lines AS
SELECT o.order_id,
       o.customer_id,
       o.order_date,
       oi.product_id,
       oi.quantity,
       oi.quantity * oi.unit_price AS line_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled';

Check it before anything else. Total revenue must be 3139.50, from 13 orders, with an average order value of 241.50. Cancelled order 5 (349.00) must not appear.

SELECT SUM(line_revenue)               AS revenue,
       COUNT(DISTINCT order_id)        AS orders,
       ROUND(SUM(line_revenue) / COUNT(DISTINCT order_id), 2) AS avg_order_value
FROM v_revenue_lines;
--  revenue | orders | avg_order_value
--  3139.50 |     13 |          241.50

Every later report selects from this view. When the business changes the rule (say, pending orders should not count either), you change one line.

Step 2: Monthly revenue and growth#

Build v_monthly_revenue with month, revenue, order count and month-over-month percentage change. Use DATE_TRUNC, LAG and a CTE. Round the percentage to one decimal.

Expected:

   month    | revenue | orders | pct_change
------------+---------+--------+------------
 2024-02-01 |  439.00 |      3 |
 2024-03-01 |  501.50 |      2 |       14.2
 2024-04-01 | 1229.50 |      3 |      145.2
 2024-05-01 |  710.00 |      3 |      -42.3
 2024-06-01 |  259.50 |      2 |      -63.5

Two things to think about. First, the first month has no previous month, so its growth is NULL, and that is the honest answer. Second, June's drop looks alarming. Look at the data before drawing conclusions: the last order is dated 14 June, so June may be a partial month. A good analyst flags that in the findings.

Step 3: Products#

Best sellers by units and by revenue. They disagree, which is the interesting part.

            name             | revenue | units
-----------------------------+---------+-------
 27-inch Monitor             |  867.00 |     3
 Standing Desk               |  499.00 |     1
 Noise-Cancelling Headphones |  398.00 |     2
 Ergonomic Chair             |  349.00 |     1
 Mechanical Keyboard         |  267.00 |     3
 Wireless Mouse              |  206.50 |     7
 USB-C Hub                   |  180.00 |     4
 Laptop Stand                |  170.00 |     5
 Desk Lamp                   |  144.00 |     6
 HD Webcam                   |   59.00 |     1

The Wireless Mouse sells the most units (7) and ranks sixth by revenue. Cable Organizer has never sold, so it appears in neither list. Write a version that includes it with zero units, using a LEFT JOIN from products, and explain when each version is the right one to show.

Top product per category by revenue, with ROW_NUMBER over a grouped result. Expected winners: Mechanical Keyboard (Accessories), Noise-Cancelling Headphones (Audio), 27-inch Monitor (Displays), Standing Desk (Furniture), Desk Lamp (Office), HD Webcam (Video).

Products bought together. Self-join order_items on the same order with a.product_id < b.product_id to avoid pairing a product with itself and counting each pair twice. Two pairs appear at least twice in non-cancelled orders: Laptop Stand with Desk Lamp, and Mechanical Keyboard with Wireless Mouse.

Step 4: Customers#

Build v_customer_value: customer, number of orders, lifetime spend, average order value and a spend quartile from NTILE(4).

   full_name   | orders |  total | avg_order | quartile
---------------+--------+--------+-----------+----------
 Farhan Ali    |      2 | 882.00 |    441.00 |        1
 Aisha Khan    |      3 | 726.00 |    242.00 |        1
 Grace Lin     |      1 | 607.50 |    607.50 |        2
 Ben Carter    |      2 | 322.00 |    161.00 |        2
 Chloe Dubois  |      2 | 269.50 |    134.75 |        3
 Elena Petrova |      1 | 167.50 |    167.50 |        3
 Hiro Tanaka   |      1 |  83.00 |     83.00 |        4
 Isla Murphy   |      1 |  82.00 |     82.00 |        4

Only eight customers appear, because Diego Alvarez (only a cancelled order) and Jon Smith (no orders) have no revenue. Decide whether the view should include them as zero-spend customers, and write the version that does. The answer depends on who will read it: a marketing list of "everyone we could email" wants them, a revenue ranking does not.

Repeat purchase rate. Of the 8 customers who bought, how many bought more than once? Aisha, Ben, Chloe and Farhan did, so the rate is 4 of 8, or 50.0%.

Revenue by country. India leads with 1608.00 across 5 orders, then USA with 929.50 across 3. How many customers does India's figure rest on, and what does that say about trusting a country-level trend?

Step 5: Cohorts#

A cohort groups customers by the month of their first purchase, then asks how many came back in a later month. It answers "do people return?" far better than an overall average.

 cohort  | cohort_size | returned_later
---------+-------------+----------------
 2024-02 |           3 |              3
 2024-03 |           1 |              0
 2024-04 |           2 |              1
 2024-05 |           1 |              0
 2024-06 |           1 |              0

Build it in two CTEs: one to find each customer's first order month with MIN, the second to join back to all their orders and count those in a strictly later month. The February cohort (Aisha, Ben, Chloe) all returned. Later cohorts have had less time to come back, so their numbers are not directly comparable. State that caveat in your findings, or the chart will mislead.

Step 6: Stock risk#

List products with stock below 20: HD Webcam (0), Standing Desk (5), Ergonomic Chair (8), 27-inch Monitor (18). Then add context so the list is actionable. For each, show units sold in the data and compute a rough "weeks of cover". Think about what a fair denominator is, and be honest in the findings that five months of data is thin for forecasting.

Notice that the HD Webcam has zero stock yet an order for it exists in the data. A good analyst asks whether that is a data problem.

Step 7: Package it#

Views for analysts. You already have v_revenue_lines, v_monthly_revenue, v_customer_value. Add v_product_performance and v_low_stock.

A materialized view for the expensive report. Store the monthly revenue and refresh it without blocking readers:

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT * FROM v_monthly_revenue;

CREATE UNIQUE INDEX ON mv_monthly_revenue (month);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

Insert a new order, confirm the materialized view is stale, refresh, and confirm it catches up. Decide how often the refresh should run and write that down.

Access. Create a role analyst that can SELECT from every v_* view and mv_monthly_revenue and nothing else. Prove with failing statements that it cannot read orders or customers directly, and cannot write anywhere. Remember that views run with their owner's permissions by default, which is what lets the analyst read through the view without table rights.

Step 8: Checks#

Write one assertion script. Every check returns a row only when something is wrong:

• Revenue from v_revenue_lines equals revenue summed from v_monthly_revenue (3139.50).
• No cancelled order appears in v_revenue_lines.
• Customer lifetime values add up to total revenue.
• No order line has a non-positive quantity or price.
• mv_monthly_revenue matches v_monthly_revenue (flags a stale refresh).

Then break something on purpose, such as changing the status filter in the view, and watch the checks catch it.

Step 9: The findings file#

Write FINDINGS.md, half a page, for a non-technical reader. Include the headline numbers (3139.50 revenue, 13 orders, 241.50 average order value, 7.1% of orders cancelled), what is growing or shrinking and your caution about June, which customers and products matter most, the repeat rate and what you can and cannot conclude from it, stock to reorder, and every assumption you made about the data.

The findings are the deliverable the finance lead reads. The SQL is how you earned them.

Definition of done#

• One view defines revenue and every report uses it
• All seven reports match the expected figures
• A materialized view with a unique index refreshes concurrently
• The analyst role reads only the reporting objects
• The check script passes, and fails when you break the revenue rule
• FINDINGS.md states conclusions, caveats and assumptions

Practice#

1. Scale it. Generate 1 million orders with generate_series, rerun the monthly revenue report with EXPLAIN ANALYZE, then add the indexes that help and compare plans.
2. Add a v_customer_segments view labeling customers 'new', 'active', 'lapsed' or 'never_purchased' based on days since last order relative to a fixed reference date.
3. Compute a 3-month moving average of revenue and explain why it behaves strangely at the start of the series.
4. Write a "basket size" report: average units per order, and the distribution of order sizes. Which orders pull the average up?
5. Change the revenue rule to exclude pending orders as well. Edit one view, refresh the materialized view, rerun the checks, and list every number in the findings that changes.