analytics_queries.sql

-- 1. Total user count
SELECT COUNT(*) AS total_users FROM users;

-- 2. Total posts
SELECT COUNT(*) AS total_posts FROM posts;

-- 3. Most liked posts
SELECT p.post_id, p.content, COUNT(l.like_id) AS likes
FROM posts p
LEFT JOIN likes l ON p.post_id = l.post_id
GROUP BY p.post_id
ORDER BY likes DESC;

-- 4. Most commented posts
SELECT p.post_id, p.content, COUNT(c.comment_id) AS comments
FROM posts p
LEFT JOIN comments c ON p.post_id = c.post_id
GROUP BY p.post_id
ORDER BY comments DESC;

-- 5. Follower count per user
SELECT u.username, COUNT(f.follower_id) AS followers
FROM users u
LEFT JOIN follows f ON u.user_id = f.following_id
GROUP BY u.user_id
ORDER BY followers DESC;

-- 6. Following count per user
SELECT u.username, COUNT(f.following_id) AS following
FROM users u
LEFT JOIN follows f ON u.user_id = f.follower_id
GROUP BY u.user_id
ORDER BY following DESC;

-- 7. Engagement score (likes + comments)
SELECT p.post_id, p.content,
       COUNT(DISTINCT l.like_id) + COUNT(DISTINCT c.comment_id) AS engagement_score
FROM posts p
LEFT JOIN likes l ON p.post_id = l.post_id
LEFT JOIN comments c ON p.post_id = c.post_id
GROUP BY p.post_id
ORDER BY engagement_score DESC;

-- 8. Most active users (posts + likes + comments)
SELECT u.username,
       (SELECT COUNT(*) FROM posts WHERE user_id = u.user_id) +
       (SELECT COUNT(*) FROM likes WHERE user_id = u.user_id) +
       (SELECT COUNT(*) FROM comments WHERE user_id = u.user_id) AS activity_score
FROM users u
ORDER BY activity_score DESC;

-- 9. Mutual followers
SELECT u1.username AS user_a, u2.username AS user_b
FROM follows f1
JOIN follows f2 
    ON f1.follower_id = f2.following_id
   AND f1.following_id = f2.follower_id
JOIN users u1 ON u1.user_id = f1.follower_id
JOIN users u2 ON u2.user_id = f1.following_id;

-- 10. Users who never posted
SELECT username FROM users
WHERE user_id NOT IN (SELECT user_id FROM posts);

-- 11. Top commenters
SELECT u.username, COUNT(c.comment_id) AS comments_made
FROM comments c
JOIN users u ON u.user_id = c.user_id
GROUP BY u.username
ORDER BY comments_made DESC;

-- 12. Posts liked by each user
SELECT u.username, p.content
FROM likes l
JOIN users u ON u.user_id = l.user_id
JOIN posts p ON p.post_id = l.post_id
ORDER BY u.username;

-- 13. Comments per user
SELECT u.username, COUNT(c.comment_id) AS comments
FROM users u
LEFT JOIN comments c ON u.user_id = c.user_id
GROUP BY u.username;

-- 14. Users with no followers
SELECT username
FROM users
WHERE user_id NOT IN (SELECT following_id FROM follows);

-- 15. Ranking users by followers
SELECT username, followers,
       RANK() OVER (ORDER BY followers DESC) AS rank
FROM (
    SELECT u.username, COUNT(f.follower_id) AS followers
    FROM users u
    LEFT JOIN follows f ON u.user_id = f.following_id
    GROUP BY u.user_id
) ranked;

-- 16. Engagement received by users
SELECT u.username,
       COUNT(l.like_id) + COUNT(c.comment_id) AS engagement_received
FROM users u
LEFT JOIN posts p ON p.user_id = u.user_id
LEFT JOIN likes l ON l.post_id = p.post_id
LEFT JOIN comments c ON c.post_id = p.post_id
GROUP BY u.username
ORDER BY engagement_received DESC;

-- 17. Posts per user
SELECT u.username, COUNT(p.post_id) AS post_count
FROM users u
LEFT JOIN posts p ON u.user_id = p.user_id
GROUP BY u.username;

-- 18. Users who liked their own posts
SELECT u.username, p.post_id
FROM likes l
JOIN posts p ON p.post_id = l.post_id
JOIN users u ON u.user_id = l.user_id
WHERE p.user_id = l.user_id;

-- 19. Most liked user (likes received)
SELECT u.username, COUNT(l.like_id) AS total_likes_received
FROM users u
JOIN posts p ON p.user_id = u.user_id
LEFT JOIN likes l ON p.post_id = l.post_id
GROUP BY u.username
ORDER BY total_likes_received DESC;

-- 20. Post-to-follower ratio per user
SELECT u.username,
       COUNT(p.post_id) AS total_posts,
       COUNT(f.follower_id) AS followers,
       ROUND(COUNT(p.post_id)::decimal / NULLIF(COUNT(f.follower_id), 0), 2) AS ratio
FROM users u
LEFT JOIN posts p ON p.user_id = u.user_id
LEFT JOIN follows f ON f.following_id = u.user_id
GROUP BY u.user_id, u.username;