如何在MySQL中检测Instagram数据库中的机器人用户

要找出点赞了平台所有照片的机器人用户，核心逻辑是统计每个用户点赞的唯一照片数量，判断该数量是否等于平台总照片数。

原SQL的问题 #

你写的SQL错误地将photos.user_id（发布照片的用户）与likes.user_id（点赞的用户）做关联，最终统计的是“发照片用户的发图数量”和“该用户自己的点赞数量”，完全偏离了“用户点赞所有照片”的分析目标。

正确的SQL语句 #

-- 先计算平台总照片数量
WITH total_photos AS (
    SELECT COUNT(id) AS total FROM photos
)
-- 统计每个用户点赞的唯一照片数，并筛选出等于总照片数的用户
SELECT 
    u.id AS user_id,
    u.username,
    COUNT(DISTINCT l.photo_id) AS liked_photo_count,
    tp.total AS total_photos_on_platform
FROM likes l
JOIN users u ON l.user_id = u.id
CROSS JOIN total_photos tp
GROUP BY u.id, u.username, tp.total
HAVING COUNT(DISTINCT l.photo_id) = tp.total;

语句说明 #

1. total_photos 公共表表达式（CTE）用于计算平台上所有照片的总数，作为判断的基准值。
2. 从likes表出发，统计每个用户点赞的唯一照片ID数量（用DISTINCT避免同一用户重复点赞同一张照片的干扰）。
3. 通过CROSS JOIN将总照片数关联到每一行，方便直接对比。
4. HAVING子句筛选出点赞数等于总照片数的用户，这类用户即为疑似机器人。

内容的提问来源于stack exchange，提问作者Prakhar Verma