**Schema (PostgreSQL v13)**

    CREATE SCHEMA dannys_diner;
    SET search_path = dannys_diner;
    
    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');

---


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

    SELECT sa.customer_id, COUNT(sa.product_id), SUM(me.price)
    FROM dannys_diner.sales AS sa
    JOIN dannys_diner.menu AS me
    ON sa.product_id=me.product_id
    GROUP BY sa.customer_id;

| customer_id | count | sum |
| ----------- | ----- | --- |
| B           | 6     | 74  |
| C           | 3     | 36  |
| A           | 6     | 76  |

---

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


    SELECT customer_id, COUNT(DISTINCT order_date) AS number_of_days
        FROM dannys_diner.sales
        GROUP BY customer_id;


| customer_id | number_of_days |
| ----------- | -------------- |
| A           | 4              |
| B           | 6              |
| C           | 2              |

---


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


    WITH final AS (
    SELECT s.customer_id, m.product_name, s.order_date,
    RANK() OVER (
      PARTITION BY customer_id
       ORDER BY order_date ASC
      ) ranking
    FROM dannys_diner.sales as s
    JOIN dannys_diner.menu as m
    ON s.product_id=m.product_id)
    
    SELECT *
    FROM final
    WHERE ranking=1;



| customer_id | product_name | order_date               | ranking |
| ----------- | ------------ | ------------------------ | ------- |
| A           | curry        | 2021-01-01T00:00:00.000Z | 1       |
| A           | sushi        | 2021-01-01T00:00:00.000Z | 1       |
| B           | curry        | 2021-01-01T00:00:00.000Z | 1       |
| C           | ramen        | 2021-01-01T00:00:00.000Z | 1       |
| C           | ramen        | 2021-01-01T00:00:00.000Z | 1       |

---



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


    SELECT s.product_id, m.product_name, COUNT(s.product_id)
    FROM dannys_diner.sales as s
    JOIN dannys_diner.menu as m
    ON s.product_id=m.product_id
    GROUP BY s.product_id, m.product_name
    Order BY COUNT(s.product_id) DESC
    LIMIT 1;


| product_id | product_name | count |
| ---------- | ------------ | ----- |
| 3          | ramen        | 8     |

---


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


    WITH final AS (
    SELECT s.customer_id, m.product_name, COUNT(s.product_id) AS total_ordered,
    RANK () OVER (
      PARTITION BY customer_id
      ORDER BY COUNT(s.product_id) DESC
      ) AS ranking
    FROM dannys_diner.sales as s
    JOIN dannys_diner.menu as m
    ON s.product_id=m.product_id
    GROUP BY s.customer_id, m.product_name)
    
    SELECT customer_id, product_name, total_ordered
    FROM final
    WHERE ranking=1;



| customer_id | product_name | total_ordered |
| ----------- | ------------ | ------------- |
| A           | ramen        | 3             |
| B           | ramen        | 2             |
| B           | curry        | 2             |
| B           | sushi        | 2             |
| C           | ramen        | 3             |

---


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


    WITH temp AS (
    SELECT s.customer_id, s.order_date, s.product_id, mem.join_date,
    RANK () OVER (
      PARTITION BY s.customer_id
      ORDER BY order_date
      ) AS rank
    FROM dannys_diner.sales AS s
    LEFT JOIN dannys_diner.members AS mem
    ON s.customer_id=mem.customer_id
    WHERE s.order_date >= mem.join_date)
    
    SELECT t.customer_id, m.product_name
    FROM temp AS t
    JOIN dannys_diner.menu AS m
    ON t.product_id=m.product_id
    WHERE rank = 1;

| customer_id | product_name |
| ----------- | ------------ |
| B           | sushi        |
| A           | curry        |

---


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


    WITH temp AS (
    	SELECT s.customer_id, s.product_id, s.order_date, mem.join_date,
      	RANK() OVER (
      		PARTITION BY s.customer_id
      		ORDER BY s.order_date DESC
    		) AS ranking
    	FROM dannys_diner.sales AS s
    	LEFT JOIN dannys_diner.members AS mem
    	ON s.customer_id=mem.customer_id
    	WHERE s.order_date < mem.join_date
    	GROUP BY s.customer_id, s.product_id, s.order_date, mem.join_date
    	ORDER BY s.customer_id, s.order_date DESC
    )
    
    SELECT t.customer_id, m.product_name AS ordered_just_before_membership
    FROM temp AS t
    JOIN dannys_diner.menu AS m
    ON t.product_id=m.product_id
    WHERE ranking= 1
    GROUP BY t.customer_id, m.product_name
    ORDER BY t.customer_id;

| customer_id | ordered_just_before_membership |
| ----------- | ------------------------------ |
| A           | curry                          |
| A           | sushi                          |
| B           | sushi                          |

---


-- 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 total_items, SUM(m.price) AS amount_spent
    FROM dannys_diner.sales AS s
    LEFT JOIN dannys_diner.members AS mem
    ON s.customer_id=mem.customer_id
    JOIN dannys_diner.menu AS 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;


| customer_id | total_items | amount_spent |
| ----------- | ----------- | ------------ |
| A           | 2           | 25           |
| B           | 3           | 40           |

---

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


    WITH temp AS
    (SELECT s.customer_id, s.product_id, m.price,
    	CASE WHEN s.product_id = 1 THEN (m.price * 2)*10
        ELSE m.price *10 END AS points
    FROM dannys_diner.sales AS s
    JOIN dannys_diner.menu AS m
    ON s.product_id=m.product_id)
    
    SELECT customer_id, SUM(points) AS points
    FROM temp
    GROUP BY customer_id
    ORDER BY customer_id;


| customer_id | points |
| ----------- | ------ |
| A           | 860    |
| B           | 940    |
| C           | 360    |

---

-- 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 temp AS (
        SELECT s.customer_id, s.order_date, mem.join_date, s.product_id, m.product_name, m.price,
        	CASE WHEN s.product_id = 1 THEN (m.price * 2)* 10
        	ELSE m.price * 10 END AS points
        FROM dannys_diner.sales AS s
        JOIN dannys_diner.menu AS m
        ON s.product_id=m.product_id
        JOIN dannys_diner.members AS mem
        ON s.customer_id=mem.customer_id
        WHERE s.order_date >= mem.join_date),
        
      temp2 AS ( 
        SELECT customer_id, order_date, join_date + INTERVAL '6 day' AS bonus_window, product_id, points
        FROM temp
        WHERE order_date <= '2021-01-31'),
        
      temp3 AS (
       	SELECT *,
        CASE 
        	WHEN product_id = 1 THEN points * 1
        	WHEN order_date BETWEEN order_date AND bonus_window
        	THEN points*2
        	ELSE points END as total_points
        FROM temp2)
        
    SELECT customer_id, SUM(total_points) AS total_january_points
    FROM temp3
    GROUP BY customer_id
    ORDER BY customer_id;


| customer_id | total_january_points |
| ----------- | -------------------- |
| A           | 1020                 |
| B           | 320                  |

---