SQL Basics · Lesson 9 of 9

Project: A Sales Report

The shop owner asks: who are our best customers? Answering takes everything from this course in one query.

Think about what the answer needs:

  • The customer's name is in customers.
  • What they bought is in orders, which only has a quantity.
  • The price of each product is in products.

So all three tables must be joined. The money spent on one order line is orders.quantity * products.price. Adding that up per customer needs SUM and GROUP BY.

Build it in steps

Experienced people do not write a query like this in one go. They grow it, running it after every step:

  1. Join the three tables and select the customer name, the quantity and the price. You should get 14 rows, one per order.
  2. Replace quantity and price with the calculation orders.quantity * products.price.
  3. Wrap the calculation in SUM( ), give it an alias and add GROUP BY customers.name. Now there is one row per customer.
  4. Add a COUNT(*) to see how many orders each customer placed.
  5. Sort by the total, highest first, and keep the top three.

The order of the parts

With everything in play, the full order of a query is:

SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT

When the checks pass you have finished the course. Your certificate is on the course page.

Tables in the practice database
TableColumns
customersid, name, city, joined
productsid, name, category, price, stock
ordersid, customer_id, product_id, quantity, ordered

Every run starts from a fresh copy of the database, so nothing you do can break it.

Your Task

  1. Write a query that returns the three customers who spent the most.
  2. Return three columns: the customer's name, the number of orders as orders, and the money spent as total (quantity times price, added up, rounded to 2 decimals).
  3. Sort by total, highest first.
  • The query joins the three tables and uses GROUP BY
  • The result has the columns name, orders and total
  • Exactly three customers are returned
  • They are the three biggest spenders, with the right order counts and totals, highest first