lesson 88 Warehouse & reporting projects · advanced

Capstone: integrated customers to trusted sales

Combine integration and quality checks into trusted sales reporting.

the idea

The capstone chains the warehouse steps: integrate CRM and ERP customers under the contract (lesson 77), build quality-gated sales from Bronze (lesson 71), then report trusted sales at the integrated customer grain. Each step is materialized so it can be inspected and reconciled.

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

   Size the raw Bronze batch. bronze_lines and distinct customers describe December sales before quality gates or integration—your capstone checkpoints start here.the pattern

   example.sql Run

   sql›

   SELECT
     COUNT(*) AS bronze_lines,
     COUNT(DISTINCT TRIM(customer_id)) AS customers
   FROM bronze_sales;

2. 2

   Count CRM customers missing ERP. The integration contract keeps every keyed CRM row; without_erp shows how many have no matching ERP person.

   step-2.sql Run

   sql›

   WITH crm AS (
     SELECT customer_id, contact_name, crm_gender
     FROM (
       SELECT
         TRIM(customer_id) AS customer_id,
         TRIM(contact_name) AS contact_name,
         CASE UPPER(TRIM(gender))
           WHEN 'F' THEN 'Female'
           WHEN 'M' THEN 'Male'
         END AS crm_gender,
         ROW_NUMBER() OVER (PARTITION BY TRIM(customer_id) ORDER BY updated_at DESC, source_row DESC) AS rn
       FROM crm_people
       WHERE customer_id IS NOT NULL AND TRIM(customer_id) <> ''
     )
     WHERE rn = 1
   ), erp AS (
     SELECT
       CASE
         WHEN substr(TRIM(erp_id), 1, 3) = 'NAS'
           THEN substr(TRIM(erp_id), 4)
         ELSE TRIM(erp_id)
       END AS customer_id,
       CASE UPPER(TRIM(gender))
         WHEN 'FEMALE' THEN 'Female'
         WHEN 'MALE' THEN 'Male'
       END AS erp_gender,
       CASE
         WHEN date(birth_date, '+0 days') = birth_date
           AND birth_date <= '2011-12-31'
           THEN birth_date
       END AS birth_date
     FROM erp_people
   ), loc AS (
     SELECT REPLACE(TRIM(erp_id), '-', '') AS customer_id, k.country
     FROM erp_locations e
     LEFT JOIN country_codes k ON k.code = UPPER(TRIM(e.country_code))
   )
   SELECT
     COUNT(*) AS crm_customers,
     SUM(erp.customer_id IS NULL) AS without_erp
   FROM crm
   LEFT JOIN erp ON erp.customer_id = crm.customer_id;

3. 3

   Map location codes to countries. erp_locations joins country_codes so integrated customers get a resolved country, not raw ISO text alone.

   step-3.sql Run

   sql›

   SELECT k.country, COUNT(*) AS locations
   FROM erp_locations e
   LEFT JOIN country_codes k ON k.code = UPPER(TRIM(e.country_code))
   GROUP BY k.country
   ORDER BY locations DESC;

common mistake

Joining Bronze sales to integrated customers before applying the quality gates lets invalid lines into the report.