Meta's "Combined Reaction Volume" SQL Question, Explained Simply

#database #interview #sql #tutorial

Your Feed post gets turned into a Reel. Somebody watches it and taps like. Does the database record that as one like, or two?

That messy little detail is the whole point of a Meta SQL interview question called Combined Reaction Volume. It looks like a basic counting problem. It isn't. And if you rush it, you'll get a number that's technically correct and completely wrong at the same time.

The setup

Meta keeps reactions to Feed posts and Reels in two separate tables, because the two products are built by separate teams:

post_reactions — likes, loves, and other reactions on regular Feed posts
reel_reactions — the same kinds of reactions, but on Reels

Here's the catch. A Reel can get cross-posted to the Feed. When that happens, one single tap of the like button can end up logged in both tables. On top of that, retry logic sometimes fires twice and inserts the exact same row into one table by mistake.

So the real question isn't "how many rows are there." It's "how many real reactions happened," once you strip out the copies.

What counts as "the same reaction"

The question defines a reaction event as a match on three things: user_id, content_id, and reaction_date. If those three line up between two rows — even across the two different tables, even if one row has a NULL reaction type — you treat them as one event, not two.

The task: for each user, count their distinct reaction events between January 1 and March 31, 2024, and only keep users with at least 5.

Where people go wrong

The instinct is to smash both tables together and count. Something like this:

-- Looks right. Silently double-counts.
SELECT user_id, COUNT(*) AS reaction_count
FROM (
    SELECT user_id, content_id, reaction_date FROM post_reactions
    UNION ALL
    SELECT user_id, content_id, reaction_date FROM reel_reactions
) combined
GROUP BY user_id;

UNION ALL stacks every row from both tables on top of each other, duplicates included. Run this against the sample data and user 10 comes back with 6 reactions. The real number is 5. That one cross-posted like got counted twice, and the query has no idea it did anything wrong.

Solving it, step by step

Step 1 — Cut each table down to just what defines an event.
Drop reaction_id and reaction_type, they're not part of what makes an event unique. Keep only user_id, content_id, and reaction_date, and filter both tables to the Q1 2024 range.

SELECT user_id, content_id, reaction_date
FROM post_reactions
WHERE reaction_date BETWEEN '2024-01-01' AND '2024-03-31'

Step 2 — Combine the two tables with UNION, not UNION ALL.
This is the actual trick. Plain UNION automatically drops exact duplicate rows, whether they came from the same table twice or from two different tables. Swap UNION ALL for UNION and the double-counted like disappears on its own.

...
UNION
SELECT user_id, content_id, reaction_date
FROM reel_reactions
WHERE reaction_date BETWEEN '2024-01-01' AND '2024-03-31'

Step 3 — Count what's left, per user.
Wrap the combined result in a subquery and group by user_id.

SELECT user_id, COUNT(*) AS distinct_reaction_count
FROM (...) combined_reactions
GROUP BY user_id

Step 4 — Apply the 5-event minimum, then sort.
HAVING filters on the group total, not on individual rows, so it comes after GROUP BY. Sort by count descending, and by user_id ascending to keep ties in a fixed order.

HAVING COUNT(*) >= 5
ORDER BY distinct_reaction_count DESC, user_id ASC

Put it all together:

SELECT
    user_id,
    COUNT(*) AS distinct_reaction_count
FROM (
    SELECT user_id, content_id, reaction_date
    FROM post_reactions
    WHERE reaction_date BETWEEN '2024-01-01' AND '2024-03-31'
    UNION
    SELECT user_id, content_id, reaction_date
    FROM reel_reactions
    WHERE reaction_date BETWEEN '2024-01-01' AND '2024-03-31'
) combined_reactions
GROUP BY user_id
HAVING COUNT(*) >= 5
ORDER BY distinct_reaction_count DESC, user_id ASC;

Why this one matters

Most people learn UNION as "the thing that stacks two tables together" and UNION ALL as "the faster version of the same thing." This question flips that around. UNION isn't just a stacking tool, it's a deduplication tool, and knowing when you actually want duplicates removed versus when you don't is the real skill being tested here, not the syntax.

Practice these types of questions on DataCurlew.

DEV Community