Лучшие покупатели

Последний вопрос: кто лучшие покупатели магазина? Магазин хочет поблагодарить их подарком и выбрать подарок по их спискам желаний. Для этого ему нужны покупатели, потратившие больше всех в оплаченных заказах, и число игр в списке желаний у каждого из них.

Складывать значения одной таблицы и считать строки другой в одном запросе — ловушка. Если соединить wishlist с orders и order_items, каждая позиция повторится столько раз, сколько у покупателя желаний, и SUM сложит её столько же раз. Поэтому сначала посчитайте итоги отдельно, а остальное добавьте потом. Для этого и нужен WITH: он даёт результату запроса имя, и основной запрос использует его как таблицу. Например, игры с лучшими оценками:

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;

Число желаний можно получить из коррелированного подзапроса в SELECT, который считает строки покупателя в wishlist, или из LEFT JOIN с COUNT(w.game_id). В любом случае покупатель с пустым списком желаний должен остаться в результате с 0, поэтому обычный JOIN со списком желаний не подойдёт.

Когда этот запрос заработает, проект готов, а вместе с ним и весь курс. Поздравляем! Вы прошли долгий путь с первого SELECT * FROM games;: только в этом проекте вы спроектировали таблицу, заполнили её и ответили на вопросы магазина с помощью соединений, группировки, подзапросов и WITH. Это тот SQL, которым каждый день пользуются программисты, аналитики и специалисты по данным.

Куда двигаться дальше? Несколько идей:

• Практикуйтесь на собственных данных. SQLite бесплатен и работает на любом компьютере. В бесплатной программе DB Browser for SQLite можно создать базу данных, импортировать таблицу, сохранённую в CSV, и задавать ей собственные вопросы.
• Другие базы данных. То, что вы выучили, работает и в PostgreSQL, MySQL и SQL Server, с небольшими отличиями: другие типы (VARCHAR, DATE, BOOLEAN), другие функции дат вместо strftime() и иначе записываемые автоматические ID. PostgreSQL — отличный следующий шаг.
• Оконные функции (OVER, ROW_NUMBER, RANK). Они вычисляют по строкам, не группируя их, например место каждого покупателя в рейтинге или нарастающий итог выручки.
• SQL в ваших программах. Приложения отправляют SQL в свою базу данных из кода: в Python — через модуль sqlite3, в JavaScript — через библиотеку вроде better-sqlite3 или pg для PostgreSQL. Значения от пользователей передаются как параметры и никогда не вставляются в текст SQL — это защищает базу данных от SQL-инъекций.

Удачи и приятных запросов!