Objective

Write a SQL query to create a histogram of user posting activity for 2024.

Table Schema:

• user_posts

Task Requirements:

• Filter only posts created in the year 2024
• Count posts per user, then count users per post count
• Sort by post count in ascending order
  
  

Output columns: post_bucket, users_num

SELECT 
    post_bucket,
    COUNT(user_id) AS users_num
FROM (
    SELECT 
        user_id,
        COUNT(post_id) AS post_bucket
    FROM user_posts
    WHERE EXTRACT(YEAR FROM post_date) = 2024
    GROUP BY user_id
) AS user_post_counts
GROUP BY post_bucket
ORDER BY post_bucket ASC;

Explanation

Step 1: Understanding the Histogram Concept

Each bucket represents a specific number of posts, and we count how many users fall into each bucket - a two-level aggregation problem.

Step 2: Filtering Posts from 2024

WHERE EXTRACT(YEAR FROM post_date) = 2024

Filters the user_posts table to only include posts created during 2024.

Step 3: Inner Query - Counting Posts Per User

SELECT user_id, COUNT(post_id) AS post_bucket
FROM user_posts
GROUP BY user_id

First aggregation: groups all posts by user_id and counts how many posts each user created in 2024.

Step 4: Outer Query - Creating the Histogram

SELECT post_bucket, COUNT(user_id) AS users_num
GROUP BY post_bucket

Second aggregation: groups users by their post count and tallies how many users created that exact number of posts.

Step 5: Ordering the Results

ORDER BY post_bucket ASC

Sorts by post count ascending to display a histogram from least to most active users.