Average reactions per post type
mediumAverage reactions per post type
Meta SQL Interview Question
Meta's feed ranking team wants to know which kinds of posts get the most engagement. Every reaction, such as a like or a love, is recorded against the post it was left on.
Find the average number of reactions per post for each post_type, as avg_reactions, rounded to 2 decimal places. Posts that received no reactions still count toward their type's average.
Asked of
- Data Analyst
- Product Analyst
- Data Scientist
- Analytics Engineer
postsTable12 rows
| Column Name | Type |
|---|---|
| post_id | BIGINT |
| user_id | BIGINT |
| post_type | VARCHAR |
| created_date | DATE |
reactionsTable18 rows
| Column Name | Type |
|---|---|
| reaction_id | BIGINT |
| post_id | BIGINT |
| reactor_id | BIGINT |
| reaction_type | VARCHAR |
| reacted_date | DATE |
postsExample Input
| post_id | user_id | post_type | created_date |
|---|---|---|---|
| 1 | 21 | photo | 2024-09-01 |
| 2 | 22 | video | 2024-09-01 |
| 3 | 21 | reel | 2024-09-02 |
| 4 | 23 | text | 2024-09-02 |
| 5 | 24 | photo | 2024-09-03 |
| 6 | 22 | photo | 2024-09-04 |
| 7 | 23 | video | 2024-09-04 |
| 8 | 24 | reel | 2024-09-06 |
reactionsExample Input
| reaction_id | post_id | reactor_id | reaction_type | reacted_date |
|---|---|---|---|---|
| 1 | 1 | 31 | like | 2024-09-01 |
| 2 | 1 | 32 | love | 2024-09-01 |
| 3 | 1 | 33 | like | 2024-09-02 |
| 4 | 2 | 31 | like | 2024-09-01 |
| 5 | 3 | 34 | haha | 2024-09-02 |
| 6 | 3 | 35 | like | 2024-09-02 |
| 7 | 3 | 31 | love | 2024-09-03 |
| 8 | 3 | 32 | like | 2024-09-03 |
| 9 | 5 | 33 | like | 2024-09-03 |
| 10 | 6 | 36 | like | 2024-09-04 |
| 11 | 7 | 31 | love | 2024-09-04 |
| 12 | 7 | 34 | like | 2024-09-05 |
| 13 | 8 | 35 | like | 2024-09-06 |
| 14 | 8 | 36 | haha | 2024-09-06 |
| 15 | 8 | 32 | like | 2024-09-07 |
Example Output
| post_type | avg_reactions |
|---|---|
| photo | 1.67 |
| reel | 3.5 |
| text | 0 |
| video | 1.5 |
Explanation
The three photo posts received 3, 1 and 1 reactions, which is 5 reactions across 3 posts, or 1.67. The only text post received no reactions at all, so text averages 0 instead of being left out.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Meta
Difficulty
medium
Topic
joins
Your query
Starting the SQL engine...