tools evolve

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');

Table relationships

Questions

  1. What is the total amount each customer spent at the restaurant?
  2. How many days has each customer visited the restaurant?
  3. What was the first item from the menu purchased by each customer?
  4. What is the most purchased item on the menu and how many times was it purchased by all customers?
  5. Which item was the most popular for each customer?
  6. Which item was purchased first by the customer after they became a member?
  7. Which item was purchased just before the customer became a member?
  8. What is the total items and amount spent for each member before they became a member?
  9. If each $1 spent equates to 10 points and sushi has a 2x points multiplier - how many points would each customer have?
  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
  11. how many points do customer A and B have at the end of January?
SELECT*
FROM members;
2 records
customer_id join_date
A 2021-01-07
B 2021-01-09 
SELECT*
FROM menu;
3 records
product_id product_name price
1 sushi 10
2 curry 15
3 ramen 12 
SELECT*
FROM sales;
Displaying records 1 - 10
customer_id order_date product_id
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

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

  • The query should join the sales and menu tables on the product_id since we need to have price and customer_id in one table
  • The SUM() function will be used to calculate the total amount spent by each customer.
  • We also need to group the results by customer_id.
SELECT 
    customer_id,
    SUM(price) AS total_spent
FROM sales AS s 
JOIN menu AS m 
ON s.product_id = m.product_id 
GROUP BY customer_id;
3 records
customer_id total_spent
A 76
B 74
C 36

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

  • we need to Grab the customer ID, and then count the Distinct dates available
  • Two columns are created, customer ID, and Days_visited, to display our info
  • We group the count By Customer ID since we want to see how many total days each Customer visited
SELECT 
    customer_id,
    COUNT(DISTINCT order_date) AS days_visited
FROM sales s
GROUP BY customer_id;
3 records
customer_id days_visited
A 4
B 6
C 2

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

this one is interesting ,but the following is how I attempted it

  • firstly i tried looking at the minimum dates for each customer as that becomes the first time or date he/she made an order.
SELECT customer_id,MIN(order_date) AS subquery
FROM sales
GROUP BY customer_id;
3 records
customer_id subquery
A 2021-01-01
B 2021-01-01
C 2021-01-01
  • next up I established my main columns as customer id, and Product name, and order date
  • Then I have to join the sales and menu tables with their corresponding Key
 SELECT 
        s.customer_id, 
        m.product_name, 
        s.order_date,
        ROW_NUMBER() OVER (PARTITION BY s.customer_id, s.order_date, m.product_name ORDER BY s.order_date) AS rn
    FROM sales s
    JOIN menu m ON s.product_id = m.product_id
   ;
Displaying records 1 - 10
customer_id product_name order_date rn
A curry 2021-01-01 1
A sushi 2021-01-01 1
A curry 2021-01-07 1
A ramen 2021-01-10 1
A ramen 2021-01-11 1
A ramen 2021-01-11 2
B curry 2021-01-01 1
B curry 2021-01-02 1
B sushi 2021-01-04 1
B sushi 2021-01-11 1

The above query gives ranks according to customer_id,order_date and product_name

  • however the dates are not exactly what we want as we require dates to be initial dates for each customer hence i joined the above queries in a WHERE clause
SELECT 
        s.customer_id, 
        m.product_name, 
        s.order_date,
        ROW_NUMBER() OVER (PARTITION BY s.customer_id, s.order_date, m.product_name ORDER BY s.order_date) AS rn
FROM sales AS s
JOIN menu m ON s.product_id = m.product_id
WHERE s.order_date IN (
        SELECT MIN(order_date)
        FROM sales
        GROUP BY customer_id
    );
5 records
customer_id product_name order_date rn
A curry 2021-01-01 1
A sushi 2021-01-01 1
B curry 2021-01-01 1
C ramen 2021-01-01 1
C ramen 2021-01-01 2

At this point ,it is easier to grab our answer as Customer A made a first order of curry and sushi, while B first ordered curry and C ordered ramen

  • we can bundle all this up in one long query for reporting
SELECT 
    customer_id, 
    GROUP_CONCAT(product_name, ', ') AS ordered_items,
    order_date
FROM (
    SELECT 
        s.customer_id, 
        m.product_name, 
        s.order_date,
        ROW_NUMBER() OVER (PARTITION BY s.customer_id, s.order_date, m.product_name ORDER BY s.order_date) AS rn
    FROM sales s
    JOIN menu m ON s.product_id = m.product_id
    WHERE s.order_date IN (
        SELECT MIN(order_date)
        FROM sales
        GROUP BY customer_id
    )
) AS subquery
WHERE rn = 1
GROUP BY customer_id, order_date
ORDER BY customer_id, order_date;
3 records
customer_id ordered_items order_date
A curry, sushi 2021-01-01
B curry 2021-01-01
C ramen 2021-01-01

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

  • first we determine the most purchased that has the greatest number of counts
SELECT
        m.product_name,
        COUNT(s.product_id) AS number_purchased
FROM sales s
JOIN menu m ON s.product_id = m.product_id
GROUP BY m.product_name
3 records
product_name number_purchased
curry 4
ramen 8
sushi 3

secondly we need to grab the results of the most frequent by also including CTEs in our query

WITH purchases AS (
    SELECT
        m.product_name,
        COUNT(s.product_id) AS number_purchased
    FROM sales s
    JOIN menu m ON s.product_id = m.product_id
    GROUP BY m.product_name
)
    
SELECT
        product_name
FROM purchases p
WHERE number_purchased = (
        SELECT MAX(number_purchased)
        FROM purchases p);
1 records
product_name
ramen
  • now let us bundle it all up
WITH purchases AS (
    SELECT
        m.product_name,
        COUNT(s.product_id) AS number_purchased
    FROM sales s
    JOIN menu m ON s.product_id = m.product_id
    GROUP BY m.product_name
),
most AS (
    SELECT
        product_name
    FROM purchases p
    WHERE number_purchased = (
        SELECT MAX(number_purchased)
        FROM purchases p
    )
)
SELECT 
    product_name,
    COUNT(order_date) AS total_orders,
    customer_id AS customer
FROM most
JOIN sales s
GROUP BY customer_id, product_name
3 records
product_name total_orders customer
ramen 6 A
ramen 6 B
ramen 3 C
  • We establish how many of each product was purchased in our first CTE
  • We then use a second CTE to define the product that was purchased the most
  • We then join the Sales table and our CTE data to get our final results
  • We then GROUP BY customer and product so we get a total count that each customer purchased said item
  • We alias our columns to make them more readable

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

WITH purchases AS (
      SELECT 
          sales.customer_id, 
          menu.product_name, 
          COUNT(sales.product_id) as number_purchased,
          RANK() OVER (PARTITION BY sales.customer_id ORDER BY COUNT(sales.product_id) DESC) AS ranking
      FROM sales
      JOIN menu ON sales.product_id = menu.product_id
      GROUP BY sales.customer_id, menu.product_name
)

SELECT 
customer_id, 
product_name, 
number_purchased
FROM purchases
WHERE ranking = 1;
5 records
customer_id product_name number_purchased
A ramen 3
B sushi 2
B ramen 2
B curry 2
C ramen 3
  • We create a CTE (common table expression) which is like a temp table By beginning with a WITH statement
  • Instead of establishing our columns, we use the CTE to establish the data we need for our answer
  • We count each product and how many times it was purchased
  • We use the RANK function to establish a list of Order Count from most to least (DESC)
  • We PARTITION by customer_id so each customer has their own temp list of Ranked purchases
  • We join the Sales and menu tables so we can get the product names for our table
  • used common KEY to JOIN your tables
  • We then group the data we established in our CTE by Customer, and product
  • NOW we establish our columns as Customer_id, product name, and number purchased.
  • We then use a WHERE statement to only show the top ranking purchased for each customer

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

SELECT 
    customer_id,
    product_name,
    order_date
FROM (
    SELECT 
        mem.customer_id,
        m.product_name, 
        s.order_date,
        ROW_NUMBER() OVER (PARTITION BY mem.customer_id ORDER BY s.order_date) AS rn
    FROM members mem
    JOIN sales s  
        ON s.customer_id = mem.customer_id
    JOIN menu m
        ON m.product_id = s.product_id
    WHERE s.order_date > mem.join_date
) AS a
WHERE rn = 1;
2 records
customer_id product_name order_date
A ramen 2021-01-10
B sushi 2021-01-11
  • We first call the columns we will need for our question
  • We use a subquery to specify each piece of data and give it a row number
  • We partition by Customer_id so row numbers start over between each customer
  • we join all our tables we need for the data
  • Then we use or Row Number to only call the first row from each customer.
  • Notice customer C doesnt show up because they never became a Member

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

SELECT 
    customer_id,
    product_name,
    order_date
FROM (
    SELECT 
        mem.customer_id,
        m.product_name, 
        s.order_date,
        ROW_NUMBER() OVER (PARTITION BY mem.customer_id ORDER BY s.order_date desc) AS rn
    FROM members mem
    JOIN sales s  
        ON s.customer_id = mem.customer_id
    JOIN menu m
        ON m.product_id = s.product_id
    WHERE s.order_date < mem.join_date
) AS a
WHERE rn = 1;
2 records
customer_id product_name order_date
A sushi 2021-01-01
B sushi 2021-01-04
  • This is almost identical to the last question so we just have to alter our last query
  • We need do still partition by customer, but this time switch order dates to desc so we find the first date JUST BEFORE they were a member. not the lowest date before.
  • We later the WHERE filter to find orders before join date instead of after
  • You can order however you want here.

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

SELECT 
    s.customer_id, 
    COUNT(s.product_id) as num_items, 
    SUM(m.price) as 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 s.customer_id;
2 records
customer_id num_items spent
A 2 25
B 3 40
  • We use COUNT to add up the amount of products purchased
  • SUM price is used to add up the total spent
  • We join all Tables through their common KEYS
  • We then use WHERE to filter the results so only orders BEFORE they joined are included
  • Finally we group by Customer ID so the amounts added up our Per customer
    • Customer C never became a member so is not included in the results

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 product_name = 'sushi' THEN price * 2 
    ELSE price END) * 10) AS total_points
FROM sales s
JOIN menu m 
    ON s.product_id = m.product_id
JOIN members mem
    ON mem.customer_id = s.customer_id
GROUP BY s.customer_id HAVING COUNT(join_date) <> 0;
2 records
customer_id total_points
A 860
B 940
  • We use CASE to set up statements that WHEN the product is sushi each $1 is worth 2 Points
  • We use the ELSE statement to say in all other cases it is just worth the menu price
  • We wrap up the CASE statment by multiplying those points we just got by 10
  • The results are then added together with that first SUM() to become total_points
  • Join menu since you need it for product names to pick out which one is ‘sushi’ or not
  • Group by Customer so their total points are added up in the results
  • We take any customer out that is not a member by using HAVING COUNT of JOIN DATE not be 0

10.In the first week after a customer joins the program (including theirjoin 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 purchases AS (
    SELECT 
        s.customer_id,
        m.product_name,
        m.price,
        s.order_date,
        mem.join_date,
        (
            CASE
                WHEN product_name = 'sushi' THEN m.price * 2
                WHEN s.order_date BETWEEN mem.join_date AND date(mem.join_date, '+6 days') THEN m.price * 2
                ELSE m.price
            END
        ) * 10 AS 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
    p.customer_id,
    SUM(points) AS total_points
FROM purchases p
WHERE order_date < (SELECT date(join_date, 'start of month', '+1 month', '-1 day') FROM members)
GROUP BY p.customer_id;
2 records
customer_id total_points
A 1370
B 820
  • First we have to use a CTE to establish our CASE when we get points * 2
  • We save the 10x for the end of our case so we do it once and not for every calculation
  • We have to join all the existing Tables to Our CTE
  • We now have to draw the date we established and set up our results table
  • We use a subquery to select the start of the month and end of the month to count the correct orders
  • Group by Customer_id so all the points are added per customer
  • Again customer C never became a member so they are not included in the results