Question

If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?

Solution

WITH sales_with_price AS (
    SELECT customer_id, order_date, m.product_id, product_name, price
    FROM sales
    INNER JOIN menu m ON sales.product_id = m.product_id
)
SELECT customer_id,
       SUM(
           CASE product_name
            WHEN 'sushi' THEN price * 10 * 2
            ELSE price * 10
            END
        ) AS total_points
FROM sales_with_price
GROUP BY customer_id;

Walkthrough

Let’s start with pulling in the amounts from the menu table next to our sales records.

SELECT customer_id, order_date, m.product_id, product_name, price
FROM sales
INNER JOIN menu m ON sales.product_id = m.product_id

Then we’ll SUM the transformed prices based on the values that product_name assumes:

WITH sales_with_price AS (
    SELECT customer_id, order_date, m.product_id, product_name, price
    FROM sales
    INNER JOIN menu m ON sales.product_id = m.product_id
)
SELECT customer_id,
       SUM(
           CASE product_name
            WHEN 'sushi' THEN price * 10 * 2
            ELSE price * 10
            END
        ) AS total_points
FROM sales_with_price
GROUP BY customer_id;