Как написать SQL запрос для подсчета лайков по фото и пользователю с использованием JOIN или вложенных запросов

Что проверяют

Базовую агрегацию: GROUP BY, разницу INNER и LEFT JOIN, поведение COUNT(*) и COUNT(колонка). Для Python-разработчика следом почти всегда идёт вопрос, как то же самое сделать в ORM и не получить N+1.

Как отвечать

Сначала уточните, что считаем: лайки каждого фото, лайки, которые получил автор, или лайки, которые поставил пользователь. Пусть есть таблицы photos(id, user_id) и likes(photo_id, user_id).

• JOIN с группировкой: SELECT p.id, p.user_id, COUNT(l.photo_id) FROM photos p LEFT JOIN likes l ON l.photo_id = p.id GROUP BY p.id, p.user_id. LEFT JOIN оставляет фото без лайков, а COUNT(l.photo_id) даёт им ноль;
• коррелированный подзапрос: (SELECT COUNT(*) FROM likes l WHERE l.photo_id = p.id) в списке SELECT. Здесь COUNT(*) корректен, ноль получится сам. Подзапрос выполняется для каждой строки, поэтому для страницы из 20 фото с индексом он хорош, а для всей таблицы агрегация через JOIN обычно быстрее. Спор решает EXPLAIN ANALYZE, а не вкус;
• по пользователю: сколько лайков получил автор, это JOIN лайков к фото и GROUP BY p.user_id. Сколько поставил сам, это GROUP BY l.user_id по одной таблице likes;
• индексы: по likes(photo_id) и уникальный по (photo_id, user_id), чтобы один человек не лайкнул фото дважды;
• ORM: в Django при related_name="likes" запрос Photo.objects.annotate(likes_count=Count("likes")) строит тот же LEFT JOIN с группировкой. В SQLAlchemy это select(Photo.id, func.count(Like.photo_id)).outerjoin(Like).group_by(Photo.id).

Где спотыкаются

Берут INNER JOIN, и фото без лайков пропадают. Или берут LEFT JOIN с COUNT(*), и у таких фото появляется один лайк.

Джойнят в одном запросе лайки и комментарии. Строки перемножаются, и счётчики раздуваются. Агрегировать каждую таблицу нужно отдельно, в подзапросе или CTE, и только потом соединять.

Считают в цикле: photo.likes.count() для каждого фото в шаблоне. Это N+1 запросов вместо одного.