Question

In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi - how many points do customer A and B have at the end of January?

Solution

WITH sales_annotated_with_first_week_check AS (
    SELECT sales.customer_id,
           order_date,
           menu.product_id,
           product_name,
           price,
           join_date,
           ((sales.order_date < members.join_date + INTERVAL '7 DAYS') AND (sales.order_date >= members.join_date)) is_within_first_week
    FROM sales
         LEFT OUTER JOIN members ON sales.customer_id = members.customer_id
         INNER JOIN menu ON sales.product_id = menu.product_id
    WHERE order_date <= '2021-01-31' AND sales.customer_id IN ('A', 'B')
),
sales_with_points_multiplier AS (
    SELECT customer_id,
           order_date,
           product_name,
           price,
           join_date,
           (
               CASE is_within_first_week
                   WHEN TRUE THEN 2
                   ELSE (
                       CASE product_name
                           WHEN 'sushi' THEN 2
                           ELSE 1
                           END
                       )
                   END
               ) points_multiplier
    FROM sales_annotated_with_first_week_check
)
SELECT customer_id, SUM(price * points_multiplier * 10) AS total_points
FROM sales_with_points_multiplier
GROUP BY customer_id;
customer_idtotal_points
A1370
B820

Walkthrough

Let’s break this down into multiple sub-problems.

First, we’ll build an intermediate table that just stores whether the sales orders that the customer made are within the first week of them becoming a member (i.e. whether the new membership 2x point bonus applies).

While we’re there, we’ll also retain sales records only up to January 2021 and for customers A, and B.

SELECT sales.customer_id,
       order_date,
       menu.product_id,
       product_name,
       price,
       members.join_date,
       ((sales.order_date < members.join_date + INTERVAL '7 DAYS') AND (sales.order_date >= members.join_date)) is_within_first_week
FROM sales
    LEFT OUTER JOIN members ON sales.customer_id = members.customer_id
    INNER JOIN menu ON sales.product_id = menu.product_id
WHERE order_date <= '2021-01-31' AND sales.customer_id IN ('A', 'B');
customer_idorder_dateproduct_idproduct_namepricejoin_dateis_within_first_week
A2021-01-103ramen122021-01-07true
A2021-01-113ramen122021-01-07true
A2021-01-113ramen122021-01-07true
A2021-01-012curry152021-01-07false
A2021-01-072curry152021-01-07true
A2021-01-011sushi102021-01-07false
B2021-01-163ramen122021-01-09false
B2021-01-012curry152021-01-09false
B2021-01-022curry152021-01-09false
B2021-01-041sushi102021-01-09false
B2021-01-111sushi102021-01-09true

Next, we’ll determine a points multiplier for these sales based on whether the customer bought Sushi or the order was made within the first week of gaining membership.

WITH sales_annotated_with_first_week_check AS (
    SELECT sales.customer_id,
           order_date,
           menu.product_id,
           product_name,
           price,
           members.join_date,
           ((sales.order_date < members.join_date + INTERVAL '7 DAYS') AND (sales.order_date >= members.join_date)) is_within_first_week
    FROM sales
        LEFT OUTER JOIN members ON sales.customer_id = members.customer_id
        INNER JOIN menu ON sales.product_id = menu.product_id
    WHERE order_date <= '2021-01-31' AND sales.customer_id IN ('A', 'B')
)
SELECT customer_id,
       order_date,
       product_name,
       price,
       join_date,
       (
        CASE is_within_first_week
            WHEN TRUE THEN 2
            ELSE (
                CASE product_name
                    WHEN 'sushi' THEN 2
                    ELSE 1
                END
            )
        END
        ) points_multiplier

FROM sales_annotated_with_first_week_check;
customer_idorder_dateproduct_namepricejoin_datepoints_multiplier
A2021-01-10ramen122021-01-072
A2021-01-11ramen122021-01-072
A2021-01-11ramen122021-01-072
A2021-01-01curry152021-01-071
A2021-01-07curry152021-01-072
A2021-01-01sushi102021-01-072
B2021-01-16ramen122021-01-091
B2021-01-01curry152021-01-091
B2021-01-02curry152021-01-091
B2021-01-04sushi102021-01-092
B2021-01-11sushi102021-01-092

Finally, we’ll SUM up price * points_multiplier * 10 for these sales records and report it as the total points.

WITH sales_annotated_with_first_week_check AS (
    SELECT sales.customer_id,
           order_date,
           menu.product_id,
           product_name,
           price,
           join_date,
           ((sales.order_date < members.join_date + INTERVAL '7 DAYS') AND (sales.order_date >= members.join_date)) is_within_first_week
    FROM sales
         LEFT OUTER JOIN members ON sales.customer_id = members.customer_id
         INNER JOIN menu ON sales.product_id = menu.product_id
    WHERE order_date <= '2021-01-31' AND sales.customer_id IN ('A', 'B')
),
sales_with_points_multiplier AS (
    SELECT customer_id,
           order_date,
           product_name,
           price,
           join_date,
           (
               CASE is_within_first_week
                   WHEN TRUE THEN 2
                   ELSE (
                       CASE product_name
                           WHEN 'sushi' THEN 2
                           ELSE 1
                           END
                       )
                   END
               ) points_multiplier
    FROM sales_annotated_with_first_week_check
)
SELECT customer_id, SUM(price * points_multiplier * 10) AS total_points
FROM sales_with_points_multiplier
GROUP BY customer_id;
customer_idtotal_points
A1370
B820