The Best Customers

The last question: who are the shop's best customers? The shop wants to thank them with a gift, and it wants to pick the gift from their wishlists. So it needs the customers who have spent the most in paid orders, and how many games each of them has on the wishlist.

Adding up the values of one table and counting the rows of another in the same query is a trap. If you joined wishlist to orders and order_items, each item would repeat once for every wish of the customer, and SUM would add it up that many times. So first work out the totals on their own, and only then add the rest. That's what WITH is for: it gives a query's result a name, and the main query uses it like a table. For example, the best-rated games:

WITH ratings AS (
    SELECT game_id, AVG(stars) AS average
    FROM reviews
    GROUP BY game_id
)
SELECT g.title, ROUND(r.average, 1) AS average
FROM ratings AS r
JOIN games AS g ON g.id = r.game_id
ORDER BY r.average DESC, g.title
LIMIT 3;

The number of wishes can come from a correlated subquery in SELECT, which counts the customer's rows in wishlist, or from a LEFT JOIN with COUNT(w.game_id). Either way, a customer with an empty wishlist must stay in the list with 0, so a plain JOIN with the wishlist won't do.

Once this query works, the project is done, and so is the whole course. Congratulations! You've come a long way since your first SELECT * FROM games;: in this project alone, you've designed a table, filled it, and answered the shop's questions with joins, grouping, subqueries and WITH. That's the SQL that programmers, analysts and data scientists use every day.

Where to go next? A few ideas:

• Practice on data of your own. SQLite is free and runs on any computer. In the free program DB Browser for SQLite, you can create a database, import a spreadsheet saved as CSV, and ask it your own questions.
• Other databases. What you've learned works in PostgreSQL, MySQL and SQL Server too, with small differences: other types (VARCHAR, DATE, BOOLEAN), other date functions instead of strftime(), and automatic IDs written differently. PostgreSQL is a great next step.
• Window functions (OVER, ROW_NUMBER, RANK). They calculate across rows without grouping them, for example the rank of each customer, or a running total of revenue.
• SQL in your programs. Apps send SQL to their database from code: in Python with the module sqlite3, in JavaScript with a library such as better-sqlite3 or pg for PostgreSQL. Values from users go in as parameters, never pasted into the text of the SQL, which protects the database from SQL injection.

Good luck, and enjoy your queries!