SQL MINI PROJECT — DANNY’S DINER 🍜

Create the Database

Start by creating a new database for the project.

CREATE DATABASE diner;

Create the Tables

The project consists of three tables: Sales, Menu, and Members. Each table captures a different aspect of the restaurant’s operations, as described below.

After creating the database, create the following three tables:

• Sales — Stores customer purchase transactions.
• Menu — Contains the restaurant’s menu items and their prices.
• Members — Stores customer membership information, including their join dates.

Sales 💲

• customer_id (CHAR(1)): Unique identifier for each customer.
• order_date (DATE): Date on which the customer placed the order.
• product_id (INT): Identifier of the menu item purchased.

Menu 📜

• product_id (INT): Unique identifier for each menu item.
• product_name (VARCHAR(20)): Name of the menu item.
• price (INT): Price of the menu item.

Members 👥

• customer_id (CHAR(1)): Unique identifier for each customer enrolled in the loyalty program.
• join_date (DATE): Date on which the customer became a loyalty program member.

Understanding the Relationships Between the Tables

1. Sales ↔ Menu (One-to-Many Relationship)

The **Sales table and the `Menu** table are linked through theproduct_id` column.

• Menu.product_id is the Primary Key.
• Sales.product_id acts as a Foreign Key that references Menu.product_id.

This forms a one-to-many (1) relationship because:

• One menu item (for example, ramen) can appear in many sales records.
• Every sales record corresponds to exactly one menu item.

This relationship enables us to retrieve additional product details, such as the product name and price, for every purchase.

2. Members ↔ Sales (One-to-Many Relationship)

The **Members table is linked to the `Sales** table through thecustomer_id` column.

Although customer_id is not explicitly declared as a primary key in the dataset, it uniquely identifies each member and serves as the linking column between the two tables.

This relationship is also one-to-many (1) because:

• One customer can place multiple orders.
• Each order belongs to a single customer.

This relationship allows us to analyze customer purchases before and after they joined the loyalty program.

Now that we’ve explored the dataset and its relationships, let’s put our SQL skills to the test by solving a series of business questions 📃

Question 1: 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
ORDER BY s.customer_id;

Explanation

• **JOIN** combines the sales and menu tables using the common column product_id, allowing us to access the price of each purchased item.
• **SUM(m.price)** calculates the total amount spent by each customer.
• **GROUP BY customer_id** groups all purchases belonging to the same customer so that the total is calculated separately for each one.
• **ORDER BY customer_id** sorts the output alphabetically by customer ID for better readability.

Question 2: How many days has each customer visited the restaurant?

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

Explanation

• **COUNT(DISTINCT order_date)** counts only the unique dates on which a customer placed an order, ensuring multiple purchases on the same day are treated as a single visit.
• **GROUP BY customer_id** groups the records by customer so the visit count is calculated individually for each customer.
• **ORDER BY customer_id** sorts the results alphabetically by customer ID.

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

WITH first_purchase AS (
    SELECT
        s.customer_id,
        s.order_date,
        m.product_name,
        DENSE_RANK() OVER (
            PARTITION BY s.customer_id
            ORDER BY s.order_date
        ) AS purchase_rank
    FROM sales s
    JOIN menu m
        ON s.product_id = m.product_id
)

SELECT
    customer_id,
    product_name
FROM first_purchase
WHERE purchase_rank = 1
ORDER BY customer_id;

Explanation

• **JOIN** combines the sales and menu tables to retrieve the product name corresponding to each purchase.
• **DENSE_RANK()** assigns a rank to each purchase based on the order_date for every customer.
• **PARTITION BY customer_id** restarts the ranking for each customer independently.
• **ORDER BY order_date** ranks purchases from the earliest to the latest date.
• **WHERE purchase_rank = 1** filters the results to include only the first purchase(s) for each customer.
• We use **DENSE_RANK() instead of `ROW_NUMBER()` because Customer A purchased both sushi and curry** on the same day. Since the dataset does not contain a timestamp or order ID, both items are considered first purchases.

Question 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(*) 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;

Explanation

• **JOIN** combines the sales and menu tables using product_id, allowing us to retrieve the name of each purchased item.
• **COUNT(*)** counts the total number of purchases for each menu item.
• **GROUP BY product_name** groups all purchases of the same menu item together.
• **ORDER BY total_purchases DESC** sorts the items from the highest purchase count to the lowest.
• **LIMIT 1** returns only the most purchased menu item.

Question 5: Which item was the most popular for each customer?

WITH item_count AS (
    SELECT
        s.customer_id,
        m.product_name,
        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
),
ranked_items AS (
    SELECT
        customer_id,
        product_name,
        purchase_count,
        DENSE_RANK() OVER (
            PARTITION BY customer_id
            ORDER BY purchase_count DESC
        ) AS purchase_rank
    FROM item_count
)

SELECT
    customer_id,
    product_name,
    purchase_count
FROM ranked_items
WHERE purchase_rank = 1
ORDER BY customer_id;

Explanation

• **JOIN** combines the sales and menu tables to retrieve the name of each purchased item.
• **COUNT(*)** calculates how many times each customer purchased each menu item.
• **GROUP BY customer_id, product_name** groups the purchases by customer and menu item.
• **DENSE_RANK()** ranks the menu items for each customer based on the number of purchases.
• **PARTITION BY customer_id** ensures the ranking starts over for each customer.
• **ORDER BY purchase_count DESC** ranks the most frequently purchased items first.
• **WHERE purchase_rank = 1** filters the results to return only the most popular item(s) for each customer.
• We use **DENSE_RANK() because Customer B purchased sushi, curry, and ramen** the same number of times. Using ROW_NUMBER() would return only one of these items, whereas DENSE_RANK() correctly returns all tied items.

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

WITH first_member_purchase AS (
    SELECT
        s.customer_id,
        s.order_date,
        mnu.product_name,
        DENSE_RANK() OVER (
            PARTITION BY s.customer_id
            ORDER BY s.order_date
        ) AS purchase_rank
    FROM sales s
    JOIN members mem
        ON s.customer_id = mem.customer_id
    JOIN menu mnu
        ON s.product_id = mnu.product_id
    WHERE s.order_date >= mem.join_date
)
SELECT
    customer_id,
    order_date,
    product_name
FROM first_member_purchase
WHERE purchase_rank = 1
ORDER BY customer_id;

Explanation

• **JOIN** combines the sales, members, and menu tables to access the customer's join date and the corresponding product name.
• **WHERE s.order_date >= mem.join_date** filters the dataset to include only purchases made on or after the customer became a member.
• **DENSE_RANK()** assigns a chronological rank to each qualifying purchase for every customer.
• **PARTITION BY customer_id** ensures the ranking is calculated independently for each customer.
• **ORDER BY order_date** ranks purchases from the earliest to the latest after the membership date.
• **WHERE purchase_rank = 1** returns only the first purchase(s) made after becoming a member.

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

WITH last_pre_member_purchase AS (
    SELECT
        s.customer_id,
        s.order_date,
        mnu.product_name,
        DENSE_RANK() OVER (
            PARTITION BY s.customer_id
            ORDER BY s.order_date DESC
        ) AS purchase_rank
    FROM sales s
    JOIN members mem
        ON s.customer_id = mem.customer_id
    JOIN menu mnu
        ON s.product_id = mnu.product_id
    WHERE s.order_date < mem.join_date
)

SELECT
    customer_id,
    order_date,
    product_name
FROM last_pre_member_purchase
WHERE purchase_rank = 1
ORDER BY customer_id;

Explanation

• **JOIN** combines the sales, members, and menu tables to retrieve each customer's membership date and the corresponding menu item.
• **WHERE s.order_date < mem.join_date** filters the dataset to include only purchases made before the customer became a member.
• **DENSE_RANK()** ranks purchases in reverse chronological order for each customer.
• **PARTITION BY customer_id** ensures that each customer's purchases are ranked independently.
• **ORDER BY order_date DESC ranks the most recent purchase before membership as 1**.
• **WHERE purchase_rank = 1** returns the last purchase(s) made before joining the loyalty program.
• We use **DENSE_RANK()** to account for the possibility of multiple purchases on the same last pre-membership date.

Question 8: What is the total number of items and the total amount spent by each customer before they became a member?

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

Explanation

• **JOIN** combines the sales, members, and menu tables to retrieve each customer's membership date and the price of each purchased item.
• **WHERE s.order_date < mem.join_date** filters the records to include only purchases made before the customer became a member.
• **COUNT(*)** calculates the total number of items purchased before membership.
• **SUM(m.price)** computes the total amount spent on those purchases.
• **GROUP BY customer_id** groups the data by customer so that the totals are calculated individually.
• **ORDER BY customer_id** sorts the results alphabetically by customer ID.

Question 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 * 20
            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
ORDER BY s.customer_id;

Explanation

• **JOIN** combines the sales and menu tables so that each purchase includes the corresponding product name and price.
• **CASE** applies the loyalty points rule:
• If the purchased item is sushi, the customer earns 20 points per $1 spent.
• Otherwise, the customer earns the standard 10 points per $1 spent.
• **SUM()** adds the points earned from all purchases made by each customer.
• **GROUP BY customer_id** calculates the total points separately for each customer.
• **ORDER BY customer_id** sorts the results alphabetically by customer ID.

Question 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 Customers A and B have at the end of January?

SELECT
    s.customer_id,
    SUM(
        CASE
            WHEN s.order_date BETWEEN mem.join_date
                                 AND DATE_ADD(mem.join_date, INTERVAL 6 DAY)
                THEN m.price * 20
            WHEN m.product_name = 'sushi'
                THEN m.price * 20
            ELSE m.price * 10
        END
    ) AS total_points
FROM sales s
JOIN members mem
    ON s.customer_id = mem.customer_id
JOIN menu m
    ON s.product_id = m.product_id
WHERE s.order_date <= '2021-01-31'
GROUP BY s.customer_id
ORDER BY s.customer_id;

Explanation

• **JOIN** combines the sales, members, and menu tables to access the purchase details, membership date, and menu prices.
• **WHERE s.order_date <= '2021-01-31'** ensures that only purchases made up to the end of January are considered.
• The **CASE** statement applies the loyalty program rules in order:
• If the purchase falls within the first week of membership (join date through six days later), the customer earns 20 points per $1 on every item.
• Otherwise, if the purchased item is sushi, the customer also earns 20 points per $1.
• All remaining purchases earn the standard 10 points per $1.
• **SUM()** calculates the total points earned by each customer.
• **GROUP BY customer_id** aggregates the points for each customer.

Happy learning! ⭐