Identify Top Contributors by Recent Post Count
Company: LinkedIn
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Quick Answer: 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.