Case Study #1 — Danny’s Diner(8 week sql challenge)

6 min readAug 22, 2023

8 Week SQL Challenge

Introduction

Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry and ramen.

Danny’s Diner is in need of your assistance to help the restaurant stay afloat — the restaurant has captured some very basic data from their few months of operation but have no idea how to use their data to help them run the business.

Problem Statement

Danny wants to use the data to answer a few simple questions about his customers, especially about their visiting patterns, how much money they’ve spent and also which menu items are their favourite. Having this deeper connection with his customers will help him deliver a better and more personalised experience for his loyal customers.

He plans on using these insights to help him decide whether he should expand the existing customer loyalty program — additionally he needs help to generate some basic datasets so his team can easily inspect the data without needing to use SQL.

Danny has provided you with a sample of his overall customer data due to privacy issues — but he hopes that these examples are enough for you to write fully functioning SQL queries to help him answer his questions!

Danny has shared with you 3 key datasets for this case study:

sales
menu
members

You can inspect the entity relationship diagram and example data below.

Entity Relationship Diagram

TABLE INPUTS

CREATE TABLE sales (
  "customer_id" VARCHAR(1),
  "order_date" DATE,
  "product_id" INTEGER
);

INSERT INTO sales
  ("customer_id", "order_date", "product_id")
VALUES
  ('A', '2021-01-01', '1'),
  ('A', '2021-01-01', '2'),
  ('A', '2021-01-07', '2'),
  ('A', '2021-01-10', '3'),
  ('A', '2021-01-11', '3'),
  ('A', '2021-01-11', '3'),
  ('B', '2021-01-01', '2'),
  ('B', '2021-01-02', '2'),
  ('B', '2021-01-04', '1'),
  ('B', '2021-01-11', '1'),
  ('B', '2021-01-16', '3'),
  ('B', '2021-02-01', '3'),
  ('C', '2021-01-01', '3'),
  ('C', '2021-01-01', '3'),
  ('C', '2021-01-07', '3');

CREATE TABLE menu (
  "product_id" INTEGER,
  "product_name" VARCHAR(5),
  "price" INTEGER
);

INSERT INTO menu
  ("product_id", "product_name", "price")
VALUES
  ('1', 'sushi', '10'),
  ('2', 'curry', '15'),
  ('3', 'ramen', '12');

CREATE TABLE members (
  "customer_id" VARCHAR(1),
  "join_date" DATE
);

INSERT INTO members
  ("customer_id", "join_date")
VALUES
  ('A', '2021-01-07'),
  ('B', '2021-01-09');

CASE STUDY QUESTIONS

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

SELECT s.customer_id, SUM(m.price) AS total_amount_spent
FROM sales s
JOIN menu m ON s.product_id = m.product_id
GROUP BY s.customer_id;

2. How many days has each customer visited the restaurant?

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

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

SELECT s.customer_id, m.product_name AS first_item_purchased
FROM sales s
JOIN menu m ON s.product_id = m.product_id
WHERE s.order_date = (
  SELECT MIN(order_date)
  FROM sales
  WHERE customer_id = s.customer_id
)
GROUP BY s.customer_id, m.product_name;

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

SELECT m."product_name", COUNT(s."product_id") AS "total_purchases"
FROM sales s
JOIN menu m ON s."product_id" = m."product_id"
GROUP BY m."product_name"
ORDER BY "total_purchases" DESC
LIMIT 1;

5. Which item was the most popular for each customer?

SELECT s.customer_id, m.product_name AS most_popular_item, COUNT(*) AS purchase_count
FROM sales s
JOIN menu m ON s.product_id = m.product_id
GROUP BY s.customer_id, m.product_name
HAVING COUNT(*) = (
  SELECT MAX(purchase_count)
  FROM (
    SELECT customer_id, COUNT(*) AS purchase_count
    FROM sales
    GROUP BY customer_id, product_id
  ) AS subquery
  WHERE s.customer_id = subquery.customer_id
)
ORDER BY s.customer_id;

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

SELECT mem.customer_id, MIN(s.order_date) AS first_purchase_date, m.product_name AS first_purchase_item
FROM sales s
JOIN menu m ON s.product_id = m.product_id
JOIN members mem ON s.customer_id = mem.customer_id
WHERE s.order_date >= mem.join_date
GROUP BY mem.customer_id, m.product_name;

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

SELECT mem.customer_id, MAX(s.order_date) AS last_purchase_date, m.product_name AS last_purchase_item
FROM sales s
JOIN menu m ON s.product_id = m.product_id
JOIN members mem ON s.customer_id = mem.customer_id
WHERE s.order_date < mem.join_date
GROUP BY mem.customer_id, m.product_name;

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

SELECT mem.customer_id, COUNT(*) AS total_items, SUM(m.price) AS total_amount_spent
FROM sales s
JOIN menu m ON s.product_id = m.product_id
JOIN members mem ON s.customer_id = mem.customer_id
WHERE s.order_date < mem.join_date
GROUP BY mem.customer_id;

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

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

10. 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?

WITH CustomerPoints AS (
    SELECT
        s."customer_id",
        s."order_date",
        m."product_id",
        CASE
            WHEN s."order_date" >= mem."join_date" AND s."order_date" <= DATE(mem."join_date", '+7 days') THEN m."price" * 2
            ELSE m."price"
        END AS "earned_points"
    FROM
        sales s
    JOIN
        menu m ON s."product_id" = m."product_id"
    JOIN
        members mem ON s."customer_id" = mem."customer_id"
)
SELECT
    "customer_id",
    SUM("earned_points") AS "total_points"
FROM
    CustomerPoints
WHERE
    "customer_id" IN ('A', 'B') AND
    "order_date" >= '2021-01-01' AND "order_date" <= '2021-01-31'
GROUP BY
    "customer_id";

BONUS QUESTIONS

Recreate the table 1 output using the available data:

SELECT s.customer_id, s.order_date, m.product_name, m.price, CASE WHEN mem.customer_id IS NULL THEN 'N' ELSE 'Y' END AS member
FROM sales s
JOIN menu m ON s.product_id = m.product_id
LEFT JOIN members mem ON s.customer_id

2. Recreate the table 2 output using the available data:

SELECT s.customer_id, s.order_date, m.product_name, m.price, 
       CASE WHEN mem.customer_id IS NULL THEN 'N' ELSE 'Y' END AS member, 
       CASE WHEN mem.customer_id IS NULL THEN NULL ELSE 
            ROW_NUMBER() OVER (PARTITION BY s.customer_id, s.order_date, m.price 
                               ORDER BY s.order_date) 
       END AS ranking
FROM sales s
JOIN menu m ON s.product_id = m.product_id
LEFT JOIN members mem ON s.customer_id = mem.customer_id AND s.order_date >= mem.join_date
ORDER BY s.customer_id, s.order_date;