SQL Case Study 1: Tiny Shop Sales

View original link here by Kedeisha Bryan.

Creating databases and Tables:

CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    first_name varchar(100),
    last_name varchar(100),
    email varchar(100)
);

CREATE TABLE products (
    product_id integer PRIMARY KEY,
    product_name varchar(100),
    price decimal
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer_id integer,
    order_date date
);

CREATE TABLE order_items (
    order_id integer,
    product_id integer,
    quantity integer
);

INSERT INTO customers (customer_id, first_name, last_name, email) VALUES
(1, 'John', 'Doe', '[email protected]'),
(2, 'Jane', 'Smith', '[email protected]'),
(3, 'Bob', 'Johnson', '[email protected]'),
(4, 'Alice', 'Brown', '[email protected]'),
(5, 'Charlie', 'Davis', '[email protected]'),
(6, 'Eva', 'Fisher', '[email protected]'),
(7, 'George', 'Harris', '[email protected]'),
(8, 'Ivy', 'Jones', '[email protected]'),
(9, 'Kevin', 'Miller', '[email protected]'),
(10, 'Lily', 'Nelson', '[email protected]'),
(11, 'Oliver', 'Patterson', '[email protected]'),
(12, 'Quinn', 'Roberts', '[email protected]'),
(13, 'Sophia', 'Thomas', '[email protected]');

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Product A', 10.00),
(2, 'Product B', 15.00),
(3, 'Product C', 20.00),
(4, 'Product D', 25.00),
(5, 'Product E', 30.00),
(6, 'Product F', 35.00),
(7, 'Product G', 40.00),
(8, 'Product H', 45.00),
(9, 'Product I', 50.00),
(10, 'Product J', 55.00),
(11, 'Product K', 60.00),
(12, 'Product L', 65.00),
(13, 'Product M', 70.00);

INSERT INTO orders (order_id, customer_id, order_date) VALUES
(1, 1, '2023-05-01'),
(2, 2, '2023-05-02'),
(3, 3, '2023-05-03'),
(4, 1, '2023-05-04'),
(5, 2, '2023-05-05'),
(6, 3, '2023-05-06'),
(7, 4, '2023-05-07'),
(8, 5, '2023-05-08'),
(9, 6, '2023-05-09'),
(10, 7, '2023-05-10'),
(11, 8, '2023-05-11'),
(12, 9, '2023-05-12'),
(13, 10, '2023-05-13'),
(14, 11, '2023-05-14'),
(15, 12, '2023-05-15'),
(16, 13, '2023-05-16');

INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 1, 2),
(1, 2, 1),
(2, 2, 1),
(2, 3, 3),
(3, 1, 1),
(3, 3, 2),
(4, 2, 4),
(4, 3, 1),
(5, 1, 1),
(5, 3, 2),
(6, 2, 3),
(6, 1, 1),
(7, 4, 1),
(7, 5, 2),
(8, 6, 3),
(8, 7, 1),
(9, 8, 2),
(9, 9, 1),
(10, 10, 3),
(10, 11, 2),
(11, 12, 1),
(11, 13, 3),
(12, 4, 2),
(12, 5, 1),
(13, 6, 3),
(13, 7, 2),
(14, 8, 1),
(14, 9, 2),
(15, 10, 3),
(15, 11, 1),
(16, 12, 2),
(16, 13, 3);

SQL interactive playground >> View on DB Fiddle

---

Question 1: Which product has the highest price? Only return a single row.

Solution:

SELECT *
FROM products
ORDER BY 3 DESC
LIMIT 1;

Output:

Question 2: Which customer has made the most orders?

Solution:

SELECT customer_id, first_name, last_name, order_count
FROM (
    SELECT c.customer_id, c.first_name, c.last_name, COUNT(o.order_id) AS order_count, DENSE_RANK() OVER (ORDER BY COUNT(o.order_id) DESC) AS rank
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
) AS subquery
WHERE rank = 1;

Output:

Question 3: What’s the total revenue per product?

Solution:

SELECT p.product_id, p.product_name, SUM(quantity*price) as revenue 
FROM order_items o
INNER JOIN products p ON o.product_id = p.product_id
GROUP BY 1, 2
ORDER BY 2;

Alternate Solution (using CTE):

WITH product_revenue AS (
    SELECT o.product_id, SUM(o.quantity * p.price) AS revenue
    FROM order_items o
    INNER JOIN products p ON o.product_id = p.product_id
    GROUP BY o.product_id
)
SELECT p.product_id, p.product_name, revenue
FROM products p
INNER JOIN product_revenue pr ON p.product_id = pr.product_id;

Output:

Question 4: Find the day with the highest revenue.

Solution:

SELECT o.order_date, SUM(quantity*price) as revenue
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;

Output:

Question 5: Find the first order (by date) for each customer.

Solution:

SELECT c.customer_id, c.first_name, c.last_name, MIN(o.order_date) AS first_order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY 1,2,3
ORDER BY first_order_date;

Output:

Question 6: Find the top 3 customers who have ordered the most distinct products

Solution:

SELECT orders.customer_id, c.first_name, c.last_name, COUNT(DISTINCT order_items.product_id) AS distinct_product_nos
FROM orders
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN customers c ON orders.customer_id = c.customer_id
GROUP BY 1,2,3
ORDER BY distinct_product_nos DESC
LIMIT 3

Output:

Question 7: Which product has been bought the least in terms of quantity?

Solution:

SELECT product_id, product_name, total_qty
FROM (
	SELECT p.product_id, p.product_name, SUM(oi.quantity) as total_qty, DENSE_RANK() OVER (ORDER BY SUM(oi.quantity) ASC) AS rank
	FROM products p
	INNER JOIN order_items oi ON p.product_id = oi.product_id
	GROUP BY 1,2) product_qty_byrank
WHERE rank = 1
ORDER BY 1

Output:

Question 8: What is the median order total?

Solution (using CTE):

  WITH order_totals AS (
    SELECT o.order_id, SUM(p.price * oi.quantity) AS total_order_amount
    FROM orders o
    JOIN order_items oi ON  o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY o.order_id
  )
  SELECT ROUND(AVG(total_order_amount),2) AS total_median_order
  FROM (
    SELECT total_order_amount, ROW_NUMBER() OVER (ORDER BY total_order_amount) AS row_num, COUNT(*) OVER () AS total_rows
    FROM order_totals
  ) t
  WHERE row_num IN ((total_rows + 1) / 2, (total_rows + 2) / 2);

Output:

Question 9: For each order, determine if it was ‘Expensive’ (total over 300), ‘Affordable’ (total over 100), or ‘Cheap’.

Solution (using CTE):

WITH order_totals AS (
  SELECT o.order_id, SUM(p.price * oi.quantity) AS total_order_amount
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
  GROUP BY o.order_id
)

SELECT order_id, total_order_amount,
  CASE
    WHEN total_order_amount > 300 THEN 'Expensive'
    WHEN total_order_amount > 100 THEN 'Affordable'
    ELSE 'Cheap'
  END AS category
FROM order_totals
ORDER BY 1

Output:

Question 10: Find customers who have ordered the product with the highest price.

Solution (using CTE):

WITH product_highestprice AS (
SELECT *
FROM products
ORDER BY price DESC
LIMIT 1
)

SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN product_highestprice php ON oi.product_id = php.product_id

Alternate Solution:

SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
WHERE p.price = (
	SELECT MAX(price)
    FROM products
	);

Output: