Combined Reaction VolumeCompany: Meta | Difficulty: medium

Meta logs user reactions (likes, loves, etc.) in two separate tables because Feed posts and Reels go through different content pipelines: post_reactions for Feed posts and reel_reactions for Reels. Because a Reel can be cross-posted to the Feed, the same underlying reaction sometimes gets logged in both tables. Retry logic can also occasionally insert the exact same reaction twice within a single table.

A reaction event is uniquely identified by the combination of user_id, content_id, and reaction_date — treat two rows as the same event whenever these three values match, regardless of reaction_id or reaction_type (even if one row has a NULL reaction_type).

Write a query to find, for each user, the number of distinct reaction events across both tables combined, counting only reactions with a reaction_date between 2024-01-01 and 2024-03-31 (inclusive). Only include users with at least 5 distinct reaction events in this range.

Output user_id and distinct_reaction_count. Order the results by distinct_reaction_count descending, then by user_id ascending.

Table Schema

Table	Column	Type
`post_reactions`	`reaction_id`	integer
`post_reactions`	`user_id`	integer
`post_reactions`	`content_id`	integer
`post_reactions`	`reaction_type`	string
`post_reactions`	`reaction_date`	date
`reel_reactions`	`reaction_id`	integer
`reel_reactions`	`user_id`	integer
`reel_reactions`	`content_id`	integer
`reel_reactions`	`reaction_type`	string
`reel_reactions`	`reaction_date`	date

Example Input: `post_reactions`

reaction_id	user_id	content_id	reaction_type	reaction_date
1	10	100	like	2024-01-05
2	10	101	love	2024-01-06
3	10	102	like	2024-01-07
4	10	103	wow	2024-01-08
5	20	200	like	2024-01-07

Example Input: `reel_reactions`

reaction_id	user_id	content_id	reaction_type	reaction_date
1	10	100	like	2024-01-05
2	10	104	haha	2024-01-09
3	20	201	wow	2024-01-08

Example Output Explanation

The row (user_id 10, content_id 100, reaction_date 2024-01-05) appears in both post_reactions and reel_reactions, but it represents a single underlying event, so it is counted once. After deduplication, user_id 10 has 5 distinct events (content_id 100, 101, 102, 103, and 104), meeting the minimum-5 threshold, so it appears in the output with distinct_reaction_count 5. user_id 20 has only 2 distinct events (content_id 200 and 201), so it falls below the threshold and does not appear in the output.

SchemaDROP TABLE IF EXISTS reel_reactions; DROP TABLE IF EXISTS post_reactions; CREATE TABLE post_reactions (reaction_id INT, user_id INT, content_id INT, reaction_type VARCHAR(255), reaction_date DATE); CREATE TABLE reel_reactions (reaction_id INT, user_id INT, content_id INT, reaction_type VARCHAR(255), reaction_date DATE); INSERT INTO post_reactions VALUES (1, 10, 100, 'like', '2024-01-05'), (2, 10, 101, 'love', '2024-01-06'), (3, 10, 102, 'like', '2024-01-07'), (4, 10, 103, 'wow', '2024-01-08'), (5, 20, 200, 'like', '2024-01-07'); INSERT INTO reel_reactions VALUES (1, 10, 100, 'like', '2024-01-05'), (2, 10, 104, 'haha', '2024-01-09'), (3, 20, 201, 'wow', '2024-01-08');← All SQL Challenges · Meta Questions · Learn SQL Free