Velamuvi AIAcademy

Lesson 17 of 17 · Changing Data

Project: Analyse a Store Database

Put your SQL skills to work. Explore an online store's database and answer real business questions about revenue, best-selling products and top customers.

  • 11 min read
  • Beginner
  • Reviewed
In this lesson you will learn
  • How to explore a database you have never seen before
  • How to turn a business question into a query
  • How to combine joins, filters, grouping and sorting in one analysis
  • How to report findings clearly

The brief

You have just joined a small online shop that sells desk equipment. The owner asks you a simple question: "How is the business doing?"

Nobody hands an analyst a tidy answer. You are given a database and you have to find out. This project walks through that work, step by step, with a second practice database you have not used before.

Step 1: get to know the data

Start by looking at every table. A few rows of each is enough.

Try it yourselfSQL
SELECT * FROM customers LIMIT 3;
SELECT * FROM products LIMIT 3;
SELECT * FROM orders LIMIT 3;
SELECT * FROM order_items LIMIT 3;

There are four tables.

  • customers: who buys
  • products: what is sold, and at what price
  • orders: one row per order, with its date and status
  • order_items: the products in each order, and how many
customers          orders               order_items          products
---------          ------               -----------          --------
id  <------------  customer_id
name               id  <--------------  order_id
city               order_date           product_id  -------> id
joined             status               quantity             name
                                                             category
                                                             price

Next, find out how big each table is.

Try it yourselfSQL
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

UNION ALL is new: it stacks the results of several queries on top of each other. It is a neat way to put four counts in one table.

Step 2: check the values

Before trusting any column, look at what it contains.

Try it yourselfSQL
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;

This matters. Two orders were cancelled. If they were counted as sales, every number in the report would be too high. From here on, each revenue query leaves them out with WHERE o.status <> 'cancelled'.

Finding details like this is the real purpose of exploring. Most wrong analyses come from an assumption nobody checked.

Step 3: what is each order worth?

The price is in products, and the quantity is in order_items. An order's value is the price multiplied by the quantity, added up over its items.

Try it yourselfSQL
SELECT o.id AS order_id,
       c.name AS customer,
       o.order_date,
       SUM(p.price * i.quantity) AS order_total
FROM orders AS o
JOIN customers   AS c ON c.id = o.customer_id
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE o.status <> 'cancelled'
GROUP BY o.id, c.name, o.order_date
ORDER BY o.id;

Four tables, one query. Each JOIN follows a single foreign key.

Step 4: total revenue

Remove the grouping and you get one number for the whole shop.

Try it yourselfSQL
SELECT SUM(p.price * i.quantity) AS total_revenue,
       COUNT(DISTINCT o.id) AS orders
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE o.status <> 'cancelled';

Note COUNT(DISTINCT o.id). After joining to order_items, an order with three items appears on three rows. Counting distinct order ids avoids counting it three times.

Step 5: which products sell best?

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

Sorting by revenue and sorting by units give different winners. The headphones bring in the most money, while cheaper accessories sell just as many units. "Best-selling" is not a precise question until you say which you mean.

Step 6: revenue by category

Try it yourselfSQL
SELECT p.category,
       SUM(p.price * i.quantity) AS revenue
FROM order_items AS i
JOIN products AS p ON p.id = i.product_id
JOIN orders   AS o ON o.id = i.order_id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY revenue DESC;

Step 7: who are the best customers?

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

Two customers are tied on 360. The second sort column, c.name, decides their order, so the result is the same every time you run it.

Step 8: what is missing?

Good analysis also looks for what did not happen. Which customers have never placed an order? Which products have never been ordered? Both are left joins that keep the unmatched rows.

Try it yourselfSQL
SELECT c.name, c.city, c.joined
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

SELECT p.name, p.category, p.price
FROM products AS p
LEFT JOIN order_items AS i ON i.product_id = p.id
WHERE i.id IS NULL;

Step 9: is the business growing?

To see a trend, group by month. strftime('%Y-%m', date) turns a date such as 2026-03-15 into 2026-03.

Try it yourselfSQL
SELECT strftime('%Y-%m', o.order_date) AS month,
       COUNT(DISTINCT o.id) AS orders,
       SUM(p.price * i.quantity) AS revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE o.status <> 'cancelled'
GROUP BY month
ORDER BY month;

Step 10: report what you found

Queries are not the result. The owner wants conclusions. From the steps above:

  • The shop has taken 2,045 in revenue from 10 valid orders. Two more orders were cancelled.
  • Audio is the top category, closely followed by Displays. The noise-cancelling headphones are the biggest earner.
  • March was the best month by a wide margin, with roughly twice the revenue of January.
  • The best customer, Anita Rao, has ordered three times.
  • One customer has never ordered, and one product, the ring light, has never sold. Both are worth a follow-up.

Each line is one sentence, backed by a query anyone can rerun. That is what a good analysis looks like.

Make it your own

  • Which city brings in the most revenue?
  • What is the average value of an order?
  • Which customers ordered in more than one month?
  • How much revenue was lost to cancelled orders?
  • Open the SQL Playground and try your own questions. The same tables are available there.
Final challenge

Work out how much revenue each city has brought in, ignoring cancelled orders. Return one row per city with the columns city and revenue.

Your solutionSQL
-- Show the total revenue for each CITY, leaving out cancelled orders.
-- Columns: city, revenue
Knowledge check

After joining orders to order_items, why use COUNT(DISTINCT o.id) to count orders?

You have finished SQL for Beginners

You can now read a database you have never seen, pull out exactly the rows and columns you need, summarise them, combine tables and change data safely. Those skills carry over to every relational database in use today.

Where next?

Finished reading? Tick it off to track your progress.