lesson 49 Advanced product analysis · advanced

Capstone: customer insights

Bring filtering, joins and aggregation together for customer analysis.

the idea

A customer report combines what you have learned: join lines to orders and products, aggregate net sales at the customer or category grain, and rank the totals. Decide the grain first, then the measures, then the ranking.

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

   Count orders versus line items. Run this. After joining items to orders, COUNT(*) is lines while COUNT(DISTINCT order_id) is invoices—use the distinct count when you mean orders.the pattern

   example.sql Run

   sql›

   SELECT
     o.customer_id,
     COUNT(DISTINCT o.order_id) AS orders,
     COUNT(*) AS lines
   FROM orders o
   JOIN order_items i ON i.order_id = o.order_id
   WHERE o.customer_id IS NOT NULL AND o.order_type = 'sale'
   GROUP BY o.customer_id
   ORDER BY orders DESC
   LIMIT 10;

2. 2

   Sum revenue per customer. Run this. quantity × unit_price rolled up by customer_id is the net sales number you will rank in the capstone tasks.

   step-2.sql Run

   sql›

   SELECT
     o.customer_id,
     ROUND(SUM(i.quantity * i.unit_price), 2) AS net_sales
   FROM orders o
   JOIN order_items i ON i.order_id = o.order_id
   WHERE o.customer_id IS NOT NULL
   GROUP BY o.customer_id
   ORDER BY net_sales DESC
   LIMIT 10;

common mistake

Counting order lines instead of distinct orders inflates the order count for customers who buy many items per invoice.