SQL分析学校用户活跃度和发帖数量

假设有以下表格：

users 表格：包含用户信息，其中 school 表示所在学校，gender 表示性别，created_at 表示用户创建时间。

posts 表格：包含帖子信息，其中 user_id 表示发帖用户的 id，created_at 表示帖子创建时间。

首先需要计算每个学校每种性别的用户数：

SELECT school, gender, COUNT(*) AS user_count 
FROM users 
GROUP BY school, gender;

然后计算每个用户的活跃天数：

SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS active_days 
FROM posts 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
GROUP BY user_id;

接着将上面的查询结果与 users 表格连接起来，得到每个用户所在学校和性别：

SELECT u.school, u.gender, p.active_days 
FROM users u 
JOIN (
    SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS active_days 
    FROM posts 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
    GROUP BY user_id
) p ON u.id = p.user_id;

最后可以在上面的查询结果上计算每个学校每种性别的用户的平均活跃天数和平均发帖数量：

SELECT school, gender, 
    AVG(active_days) AS avg_active_days, 
    AVG(posts_count) AS avg_posts_count 
FROM (
    SELECT u.school, u.gender, p.active_days, COUNT(*) AS posts_count 
    FROM users u 
    JOIN (
        SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS active_days 
        FROM posts 
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
        GROUP BY user_id
    ) p ON u.id = p.user_id 
    JOIN posts ON u.id = posts.user_id 
    WHERE posts.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
    GROUP BY u.school, u.gender, p.active_days
) t 
GROUP BY school, gender;