lesson 75 Warehouse & reporting projects · advanced

Customer & product reporting contracts

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

the idea

A reusable report combines identity, totals, dates, and meaningful segmentation at one entity grain. Preserve entities with no sales when requested. These exercises define recency against 2025-04-01 rather than today, so the answer is reproducible.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  c.customer_id,
  c.name,
  COUNT(f.sale_key) AS sales_lines,
  COALESCE(SUM(f.sales),0) AS total_sales
FROM dim_customer c
LEFT JOIN fact_sales f ON f.customer_key=c.customer_key
GROUP BY c.customer_key,c.customer_id,c.name;

Watch for: These are report contracts, not inferred customer lifetime value. Segmentation thresholds are exercise-specific.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Start from the customer. Customers with no sales still belong on this report. The sales table alone cannot list them.

   step-1.sql Run

   sql›

   SELECT customer_key, customer_id, name
   FROM dim_customer;

2. 2

   Run the reference. A reusable report combines identity, totals, dates, and meaningful segmentation at one entity grain. Preserve entities with no sales when requested. These exercises define recency against 2025-04-01 rather than today, so the answer is reproducible.

   step-2.sql Run

   sql›

   SELECT
     c.customer_id,
     c.name,
     COUNT(f.sale_key) AS sales_lines,
     COALESCE(SUM(f.sales),0) AS total_sales
   FROM dim_customer c
   LEFT JOIN fact_sales f ON f.customer_key=c.customer_key
   GROUP BY c.customer_key,c.customer_id,c.name;