Objective

Write a SQL query to calculate the number of days between each user's first and last post in 2021 for users who posted at least twice during that year.

Table Schema:

• posts

Task Requirements:

• Filter only posts from the year 2021
• Include only users who posted at least twice in 2021
• Calculate the number of days between each user's first and last post
• Return the user ID and the days between their posts
  
  

Output columns: user_id, days_between

SELECT 
    user_id,
    MAX(post_date::DATE) - MIN(post_date::DATE) AS days_between
FROM posts
WHERE EXTRACT(YEAR FROM post_date) = 2021
GROUP BY user_id
HAVING COUNT(post_id) >= 2
ORDER BY user_id;

Explanation

Step 1: Filter Posts from 2021

Use EXTRACT(YEAR FROM post_date) = 2021 to include only posts from 2021.

WHERE EXTRACT(YEAR FROM post_date) = 2021

Step 2: Group by User

Group by user_id to aggregate each user's posting activity independently.

Step 3: Filter Users with At Least 2 Posts

Use HAVING COUNT(post_id) >= 2 to exclude users who posted only once.

HAVING COUNT(post_id) >= 2

Step 4: Calculate Days Between First and Last Post

Subtract MIN(post_date) from MAX(post_date) (both cast to DATE) to get the number of days between the earliest and latest post.

MAX(post_date::DATE) - MIN(post_date::DATE) AS days_between