Question

What is the total items and amount spent for each member before they became a member?

Solution

WITH orders_by_customer_before_membership AS (
    SELECT sales.customer_id, order_date, product_id
    FROM sales
        INNER JOIN members m ON sales.customer_id = m.customer_id
    WHERE order_date < m.join_date
)
SELECT orders_by_customer_before_membership.customer_id,
       SUM(price),
       COUNT(*)
FROM orders_by_customer_before_membership
INNER JOIN menu ON menu.product_id = orders_by_customer_before_membership.product_id
GROUP BY customer_id;

Note: Since Customer C never became a member, we won’t include them in this calculation. But if we were asked to, we’d have to change the orders_by_customer_before_membership table to the following:

SELECT sales.customer_id, order_date, product_id
FROM sales
-    INNER JOIN members m ON sales.customer_id = m.customer_id
+    LEFT OUTER JOIN members m ON sales.customer_id = m.customer_id
- WHERE order_date < m.join_date
+ WHERE order_date < m.join_date OR m.join_date IS NULL

This would produce the following as the final result:

Walkthrough

Let’s start by building an intermediate table of all the sales records for customer before they became a member.

SELECT sales.customer_id, order_date, product_id, m.join_date
FROM sales
    INNER JOIN members m ON sales.customer_id = m.customer_id
WHERE order_date < m.join_date;

Now, the total number of items they purchased is the number of sales (i.e. COUNT of the rows), and the total amount they spent is the SUM of the prices of the items in the sales records.

WITH orders_by_customer_before_membership AS (
    SELECT sales.customer_id, order_date, product_id
    FROM sales
        INNER JOIN members m ON sales.customer_id = m.customer_id
    WHERE order_date < m.join_date
)
SELECT orders_by_customer_before_membership.customer_id,
       SUM(price),
       COUNT(*)
FROM orders_by_customer_before_membership
INNER JOIN menu ON menu.product_id = orders_by_customer_before_membership.product_id
GROUP BY customer_id;