User Interaction
ASKED IN INTERVIEWMeta
Problem Statement
Meta content creators need a daily summary of how their posts are performing. Interactions are defined as the sum of likes and comments.
Write an SQL query to find the total number of interactions for each post for every day that an interaction occurred.
Rules:
- An interaction is either a "Like" or a "Comment".
- Output columns: post_id, interaction_day, total_interactions.
- Only include posts and days where at least one interaction happened.
- Results must be sorted by interaction_day in ascending order, then post_id in ascending order.
Tests your understanding of
Union, Aggregation and Join
Input Tables
Posts
| post_id(INTEGER) | creator_id(INTEGER) | content(VARCHAR) |
|---|---|---|
| 1 | 101 | Hello World |
| 2 | 102 | Travel Vlog |
| 3 | 103 | Cooking Tips |
| 4 | 104 | SQL Tutorial |
| 5 | 105 | Funny Meme |
| 6 | 106 | Tech News |
Likes
| like_id(INTEGER) | post_id(INTEGER) | user_id(INTEGER) | created_at(TIMESTAMP) |
|---|---|---|---|
| 1 | 1 | 201 | 2026-02-10 10:00:00 |
| 2 | 1 | 202 | 2026-02-10 11:00:00 |
| 3 | 2 | 201 | 2026-02-10 12:00:00 |
| 4 | 1 | 203 | 2026-02-11 09:00:00 |
Comments
| comment_id(INTEGER) | post_id(INTEGER) | user_id(INTEGER) | created_at(TIMESTAMP) |
|---|---|---|---|
| 1 | 1 | 204 | 2026-02-10 10:30:00 |
| 2 | 2 | 205 | 2026-02-11 14:00:00 |
Expected Output
| post_id(INTEGER) | interaction_day(DATE) | total_interactions(INTEGER) |
|---|---|---|
| 1 | 2026-02-10 | 3 |
| 2 | 2026-02-10 | 1 |
| 1 | 2026-02-11 | 1 |
| 2 | 2026-02-11 | 1 |
Tags
MediumASKED IN INTERVIEWUnionAggregationJoin
10-15 min
74%