Rank All The Things

Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases so he expects null ranking values for the records when customers are not yet part of the loyalty program.

Solution

WITH sales_with_membership AS (
    SELECT sales.customer_id,
           order_date,
           product_name,
           price,
           (
               CASE order_date >= join_date
                   WHEN TRUE THEN 'Y'
                   ELSE 'N'
                   END
               ) member
    FROM sales
         LEFT OUTER JOIN members ON sales.customer_id = members.customer_id
         INNER JOIN menu ON sales.product_id = menu.product_id
)
SELECT *,
       (
           CASE member
           WHEN 'N' THEN NULL
           ELSE
               DENSE_RANK() OVER (PARTITION BY customer_id, member ORDER BY order_date)
           END
        ) ranking
FROM sales_with_membership
ORDER BY customer_id, order_date;

Walkthrough

Most of the data remains the same from the previous bonus question, except for the addition of a new column that stores the ranking of the items per customer.

SELECT sales.customer_id,
        order_date,
        product_name,
        price,
        (
            CASE order_date >= join_date
                WHEN TRUE THEN 'Y'
                ELSE 'N'
            END
        ) member
FROM sales
        LEFT OUTER JOIN members ON sales.customer_id = members.customer_id
        INNER JOIN menu ON sales.product_id = menu.product_id;

Now, we rank the rows by partitioning over (customer_id, member).

WITH sales_with_membership AS (
    SELECT sales.customer_id,
           order_date,
           product_name,
           price,
           (
               CASE order_date >= join_date
                   WHEN TRUE THEN 'Y'
                   ELSE 'N'
                   END
               ) member
    FROM sales
         LEFT OUTER JOIN members ON sales.customer_id = members.customer_id
         INNER JOIN menu ON sales.product_id = menu.product_id
)
SELECT *,
       (
           CASE member
           WHEN 'N' THEN NULL
           ELSE
               DENSE_RANK() OVER (PARTITION BY customer_id, member ORDER BY order_date)
           END
        ) ranking
FROM sales_with_membership
ORDER BY customer_id, order_date;