The Customer Card

When a customer calls, the team wants to see everything about them at a glance, in one row: the customer's name, the name of the customer who recommended the shop to them as referrer, the number of their paid orders as paid_orders, the money they've spent in paid orders as spent, and the number of reviews they've written as reviews. The customer on the phone now has the email liam.t@mail.com; find them by it:

A customer whom nobody recommended still has a card, just with NULL as the referrer.

A tip: the orders, their items and the reviews all belong to the customer, but if you join them all to the customer at once, the rows multiply, as you saw among the join pitfalls. Each of the numbers can be worked out on its own.

That's the whole project. With joins and subqueries, you've answered questions that need several tables at once: who bought a game and hasn't reviewed it, who hasn't been back for a while, and everything about a customer in one row. All your queries so far have only read the data. In the next lesson, you'll learn to change it: to add rows, update them and delete them.