Project: Analyse a Shop Database with SQL

Work through these questions in order. Try each one yourself in the query box before reading the solution: write a first attempt, run it, and compare.

1. How much revenue has the shop made?

Exclude cancelled orders.

SELECT SUM(oi.quantity * p.price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products    AS p  ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled';

2. Which products sell best?

SELECT p.name,
       SUM(oi.quantity)           AS units,
       SUM(oi.quantity * p.price) AS revenue
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
JOIN orders   AS o ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.name
ORDER BY revenue DESC;

Notice that the product with the most units is not the one with the most revenue.

3. How is revenue changing month by month?

WITH monthly AS (
    SELECT strftime('%Y-%m', o.order_date) AS month,
           SUM(oi.quantity * p.price)      AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products    AS p  ON p.product_id = oi.product_id
    WHERE o.status <> 'cancelled'
    GROUP BY month
)
SELECT month,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

4. Who are the best customers?

SELECT c.name,
       COUNT(DISTINCT o.order_id)  AS orders,
       SUM(oi.quantity * p.price)  AS revenue
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products    AS p  ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.name
ORDER BY revenue DESC;

COUNT(DISTINCT o.order_id) is needed because the join returns one row per item.

5. Which customers have never bought anything?

SELECT c.name, c.signup_date
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status <> 'cancelled'
);

SELECT p.category,
       SUM(oi.quantity * p.price) AS revenue,
       ROUND(100.0 * SUM(oi.quantity * p.price)
             / SUM(SUM(oi.quantity * p.price)) OVER (), 1) AS percent
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
JOIN orders   AS o ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY revenue DESC;

7. What was each customer’s largest order?

WITH order_totals AS (
    SELECT o.order_id,
           o.customer_id,
           o.order_date,
           SUM(oi.quantity * p.price) AS order_total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products    AS p  ON p.product_id = oi.product_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id, o.order_date
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS position
    FROM order_totals
)
SELECT c.name, r.order_id, r.order_date, r.order_total
FROM ranked AS r
JOIN customers AS c ON c.customer_id = r.customer_id
WHERE r.position = 1
ORDER BY r.order_total DESC;

8. How long do customers take to come back?

WITH sequenced AS (
    SELECT customer_id,
           order_date,
           LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order
    FROM orders
    WHERE status <> 'cancelled'
)
SELECT c.name,
       s.previous_order,
       s.order_date,
       CAST(julianday(s.order_date) - julianday(s.previous_order) AS INTEGER) AS days_between
FROM sequenced AS s
JOIN customers AS c ON c.customer_id = s.customer_id
WHERE s.previous_order IS NOT NULL
ORDER BY c.name, s.order_date;

Checklist for any analysis query

State the question in one sentence before writing SQL.
Decide what one row of the result represents.
Check the row count after each join.
Decide how to treat NULL and excluded statuses, and write it down.
Verify one result by hand with a simpler query.

What comes next

SQL retrieves and summarises data. The next course, Data Science with Python, takes that data further: statistics, predictive models, text analysis and neural networks.