lesson 75 Warehouse & reporting projects · advanced

Customer & product reporting contracts

Produce customer and product reports at a defined level of detail.

the idea

A reporting contract fixes the grain, columns and rules of a report so every reader gets the same numbers: one row per customer (including customers with no sales), a defined reference date for recency, and explicit thresholds for segments.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Keep customers with no sales. LEFT JOIN from dim_customer to fact_sales still returns customers with zero lines—an inner join would drop them from a customer report.the pattern

   example.sql Run

   sql›

   SELECT c.customer_id, COUNT(s.sale_key) AS lines
   FROM dim_customer c
   LEFT JOIN fact_sales s ON s.customer_key = c.customer_key
   GROUP BY c.customer_id
   ORDER BY lines
   LIMIT 10;

2. 2

   Measure whole days between dates. julianday subtracts two dates and gives a whole-day count—the same idea recency_days uses with a fixed reference date.

   step-2.sql Run

   sql›

   SELECT julianday('2011-12-10') - julianday('2011-11-30') AS days;

common mistake

An inner join from customers to sales silently drops customers with no 2011 purchases; a customer report should keep them with zero sales.