8 Week SQL Challenge - Case Study 1

Starting this new year, I am looking at getting involved with challenges. And for accountability, use Hashnode to share my work publicly. Without much ado, I am sharing answers to questions on Case Study #1 - Danny's Diner in the 8 Weel SQL Challenge by Danny's Diner

What is the total amount each customer spent at the restaurant?

SELECT 
    sales.customer_id,
    SUM(menu.price)
FROM sales
INNER JOIN menu
    ON sales.product_id = menu.product_id
GROUP BY customer_id;

How many days has each customer visited the restaurant?

SELECT 
    customer_id,
    COUNT(DISTINCT order_date) AS days_visited
FROM sales
GROUP BY customer_id;

What was the first item from the menu purchased by each customer?

-- Return each customer and the date of their first order
WITH first_customer_order_date AS (
    SELECT
        customer_id,
        MIN(order_date) AS first_date
        FROM sales
        GROUP BY customer_id
)

SELECT
    sales.customer_id,
    sales.order_date,
    menu.product_name
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN first_customer_order_date
ON sales.customer_id = first_customer_order_date.customer_id
AND sales.order_date = first_customer_order_date.first_date
ORDER BY sales.customer_id;

What is the most purchased item on the menu and how many times was it purchased by all customers?

SELECT 
    menu.product_name,
    COUNT(*) AS purchase_count
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
GROUP BY menu.product_name
ORDER BY purchase_count DESC
LIMIT 1;

Which item was the most popular for each customer?

-- Return the number of items purchased by each customer
WITH item_counts AS (
    SELECT
        sales.customer_id,
        menu.product_name,
        COUNT(*) AS total_purchase
    FROM sales
    INNER JOIN menu
    ON sales.product_id = menu.product_id
    GROUP BY sales.customer_id, menu.product_name
),
-- Return the most purchased item for each customer
max_counts AS (
    SELECT 
        customer_id,
        MAX(total_purchase) AS max_purchase
    FROM item_counts
    GROUP BY customer_id
)

SELECT 
    ic.customer_id,
    ic.product_name,
    ic.total_purchase
FROM item_counts ic
INNER JOIN max_counts mc
ON ic.customer_id = mc.customer_id
AND ic.total_purchase = mc.max_purchase
ORDER BY ic.customer_id;

Which item was purchased first by the customer after they became a member?

-- Return the customer id and date for the first order after customer became a member
WITH first_order_after_join AS (
    SELECT 
        sales.customer_id,
        MIN(sales.order_date) AS first_order_date
    FROM members
    INNER JOIN sales
    ON members.customer_id = sales.customer_id
    WHERE sales.order_date >= members.join_date
    GROUP BY sales.customer_id
)

SELECT 
    sales.customer_id,
    sales.order_date,
    menu.product_name
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN first_order_after_join
ON sales.customer_id = first_order_after_join.customer_id
AND sales.order_date = first_order_after_join.first_order_date;

Which item was purchased just before the customer became a member?

-- All orders for each customer before they became members
WITH order_b4_membership AS (
    SELECT 
        members.customer_id,
        MAX(sales.order_date) AS last_purchase_b4_membership
    FROM sales
    INNER JOIN members
    ON sales.customer_id = members.customer_id
    WHERE sales.order_date < members.join_date
    GROUP BY members.customer_id
)

SELECT
    sales.customer_id,
    sales.order_date,
    menu.product_name
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN order_b4_membership
ON sales.customer_id = order_b4_membership.customer_id
AND sales.order_date = order_b4_membership.last_purchase_b4_membership;

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

WITH orders_b4_membership AS (
    SELECT 
        members.customer_id,
        sales.order_date
    FROM sales
    INNER JOIN members
    ON sales.customer_id = members.customer_id
    WHERE sales.order_date < members.join_date
)

SELECT 
    sales.customer_id,
    COUNT(*) AS total_number_of_items,
    SUM(menu.price) AS total_cost_of_items
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN orders_b4_membership
ON sales.customer_id = orders_b4_membership.customer_id
AND sales.order_date = orders_b4_membership.order_date
GROUP BY sales.customer_id;

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

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

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?

-- Using the DATE_ADD function, you add 6 days to the join date to get the first week
SELECT 
    sales.customer_id,
    SUM(
        CASE 
            WHEN sales.order_date BETWEEN members.join_date AND DATE_ADD(members.join_date, INTERVAL 6 DAY) THEN menu.price * 20
            WHEN menu.product_name = 'sushi' THEN menu.price * 20
            ELSE menu.price * 10
        END 
    ) AS total_points
FROM sales
INNER JOIN menu
ON sales.product_id = menu.product_id
INNER JOIN members
ON sales.customer_id = members.customer_id
WHERE sales.order_date <= '2021-01-31'
GROUP BY sales.customer_id
ORDER BY sales.customer_id;

Hope to see you in the second case: 8 Week SQL Challenge - Case Study 2 (Pizza Runner)