Coding questions · LinkedIn

Identify Top Contributors by Recent Post Count

This question evaluates proficiency in SQL data aggregation and time-based event summarization, focusing on counting and grouping user-generated records to measure recent activity.

posts

+----+---------+---------------------+
| id | user_id | created_at          |
+----+---------+---------------------+
| 1  | 101     | 2023-09-01 10:00:00 |
| 2  | 102     | 2023-09-01 11:00:00 |
| 3  | 101     | 2023-09-02 09:30:00 |
| 4  | 103     | 2023-09-02 12:45:00 |
| 5  | 101     | 2023-09-03 14:20:00 |
+----+---------+---------------------+

Scenario

A social media platform wants to list how many posts each user created in the last 30 days to spot top contributors.

Question

Given table posts(id, user_id, created_at), write a SQL query that returns user_id and post_count for the past 30 days, ordered by post_count DESC.

Hints

Filter by created_at >= current_date - interval '30 days', GROUP BY user_id, ORDER BY count desc.