3808. Find Emotionally Consistent Users - SQL

The problem

Table: reactions

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| user_id      | int     |
| content_id   | int     |
| reaction     | varchar |
+--------------+---------+
(user_id, content_id) is the primary key (unique value) for this table.
Each row represents a reaction given by a user to a piece of content.

Write a solution to identify emotionally consistent users based on the following requirements:

For each user, count the total number of reactions they have given.
Only include users who have reacted to at least 5 different content items.
A user is considered emotionally consistent if at least 60% of their reactions are of the same type.

Return the result table ordered by reaction_ratio in descending order and then by user_id in ascending order.

Note:

reaction_ratio should be rounded to 2 decimal places

The result format is in the following example.

Example:

Input:

reactions table:

+---------+------------+----------+
| user_id | content_id | reaction |
+---------+------------+----------+
| 1       | 101        | like     |
| 1       | 102        | like     |
| 1       | 103        | like     |
| 1       | 104        | wow      |
| 1       | 105        | like     |
| 2       | 201        | like     |
| 2       | 202        | wow      |
| 2       | 203        | sad      |
| 2       | 204        | like     |
| 2       | 205        | wow      |
| 3       | 301        | love     |
| 3       | 302        | love     |
| 3       | 303        | love     |
| 3       | 304        | love     |
| 3       | 305        | love     |
+---------+------------+----------+

Output:

+---------+-------------------+----------------+
| user_id | dominant_reaction | reaction_ratio |
+---------+-------------------+----------------+
| 3       | love              | 1.00           |
| 1       | like              | 0.80           |
+---------+-------------------+----------------+

Explanation:

User 1:
- Total reactions = 5
- like appears 4 times
- reaction_ratio = 4 / 5 = 0.80
- Meets the 60% consistency requirement
User 2:
- Total reactions = 5
- Most frequent reaction appears only 2 times
- reaction_ratio = 2 / 5 = 0.40
- Does not meet the consistency requirement
User 3:
- Total reactions = 5
- 'love' appears 5 times
- reaction_ratio = 5 / 5 = 1.00
- Meets the consistency requirement

The Results table is ordered by reaction_ratio in descending order, then by user_id in ascending order.

Approach overview

Problem Overview: You get a table of user emotion records and a table of users. Return the users whose emotion records meet the consistency condition in the statement, along with their names. The problem reduces to aggregating each user's rows and joining the result to the Users table.

Approach 1: Row-by-Row Filtering with Correlated Subquery (O(n * m) time, O(1) extra space)

For each user in the Users table, run a correlated subquery that scans the Emotions table for that user's rows and checks the condition directly. This works because each check is self-contained. The cost is that the inner scan repeats for every user, so you pay for the Emotions table once per user. Use this only to confirm your understanding of the condition before optimizing.

Approach 2: GROUP BY Aggregation with JOIN (O(n log n) time with sorting or O(n) with hashing, O(k) space)

Group the Emotions table by user_id and compute the statistics the condition needs, such as COUNT(*), MIN, MAX, or COUNT(DISTINCT ...). Put the threshold check in a HAVING clause so only qualifying users remain. Then JOIN that result to the Users table on user_id to attach names. The database makes a single pass over Emotions to build the groups, which is why this beats the correlated approach. In Pandas, the same idea is groupby('user_id').agg(...) followed by a boolean filter and merge.

Approach 3: Window Functions for Per-Row Context (O(n log n) time, O(n) space)

If the consistency condition depends on the order of a user's emotion records, such as consecutive entries or streaks, use window functions like LAG or ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ...). Compute the per-row comparison, then aggregate those flags by user. This adds a sort per partition, so it costs more than plain grouping. Choose it only when the condition needs row order.

Recommended for interviews: Interviewers expect the GROUP BY plus JOIN approach. The correlated subquery shows you understand the condition, but it rescans data for every user. The grouped version shows you can reduce the problem to one aggregation pass and one join, which is the skill they are testing. Mention the window-function version only if the condition depends on order.

Related topics: database, aggregation, join.

Solution

Thinking

We must count reactions per user, find the dominant type and its ratio, and drop users with fewer than 5 reactions or a ratio below 0.60. Repeated scans of the raw table recompute the same aggregates.

Several reaction types of one user must feed both the maximum count and the total, then we look up which type attains that maximum.

Group by (user_id,reaction) into t, then aggregate t by user into s with the max count and ratio filter.

Join s back to t on the user, keep rows whose count equals the maximum, and sort by ratio then id. Grouping before the join avoids filtering the detail rows over and over.

We first count the number of each reaction for every user and record it in a temporary table t. Then, based on the temporary table t, we calculate the maximum reaction count and total reaction count for each user, compute the reaction ratio, and filter out the users who meet the conditions, recording them in a temporary table s. Finally, we join the temporary tables s and t to find the dominant reaction for each user and sort the results as required.

MySQL

# Write your MySQL query statement below
WITH
    t AS (
        SELECT
            user_id,
            reaction,
            COUNT(1) cnt
        FROM reactions
        GROUP BY 1, 2
    ),
    s AS (
        SELECT
            user_id,
            MAX(cnt) mx_cnt,
            ROUND(MAX(cnt) / SUM(cnt), 2) reaction_ratio
        FROM t
        GROUP BY 1
        HAVING 5 * MAX(cnt) >= 3 * SUM(cnt) AND SUM(cnt) >= 5
    )
SELECT user_id, reaction dominant_reaction, reaction_ratio
FROM
    s
    JOIN t USING (user_id)
WHERE cnt = mx_cnt
ORDER BY 3 DESC, 1;

Pandas

import pandas as pd
from decimal import Decimal, ROUND_HALF_UP


def find_emotionally_consistent_users(reactions: pd.DataFrame) -> pd.DataFrame:
    t = reactions.groupby(["user_id", "reaction"]).size().reset_index(name="cnt")

    s = (
        t.groupby("user_id")
        .agg(mx_cnt=("cnt", "max"), total_cnt=("cnt", "sum"))
        .reset_index()
    )

    s["reaction_ratio"] = (
        s["mx_cnt"]
        .div(s["total_cnt"])
        .apply(
            lambda x: float(
                Decimal(str(x)).quantize(Decimal("0.00"), rounding=ROUND_HALF_UP)
            )
        )
    )

    s = s[(5 * s["mx_cnt"] >= 3 * s["total_cnt"]) & (s["total_cnt"] >= 5)]

    merged = pd.merge(
        s[["user_id", "mx_cnt", "reaction_ratio"]],
        t,
        left_on=["user_id", "mx_cnt"],
        right_on=["user_id", "cnt"],
    )

    result = (
        merged[["user_id", "reaction", "reaction_ratio"]]
        .rename(columns={"reaction": "dominant_reaction"})
        .sort_values(by=["reaction_ratio", "user_id"], ascending=[False, True])
        .reset_index(drop=True)
    )

    return result

Try this approach in the editor

Complexity analysis

Approach	Time	Space	When to Use
Correlated subquery per user	O(n * m)	O(1) extra	Quick check of the condition on small data
GROUP BY with HAVING + JOIN	O(n) with hashing, O(n log n) with sorting	O(k) for k users	Default choice for aggregate-based conditions
Window functions + aggregation	O(n log n)	O(n)	Condition depends on row order or consecutive entries

Frequently asked questions

Is Find Emotionally Consistent Users easy or hard?

It is a medium-difficulty problem with an acceptance rate around 54 percent. The logic is straightforward once you see that the task is a grouped aggregation followed by a join. Most difficulty comes from reading the consistency condition correctly and writing the HAVING clause to match it.

Find Emotionally Consistent Users Python/Java solution

In Python with Pandas, group the emotions DataFrame by user_id, aggregate the required statistics, filter with a boolean mask, and merge with the users DataFrame on user_id. In Java or other languages, the usual approach is a hash map from user_id to aggregate values, then a lookup for each user. The SQL GROUP BY and JOIN solution is the most direct form for this platform.

How to solve Find Emotionally Consistent Users in O(n)?

Group the Emotions rows by user_id in a single pass and compute the required aggregates, such as counts or min and max values. Filter the groups with HAVING, then join the surviving user_ids to the Users table. Hash aggregation keeps the work linear in the number of rows.

What is the best approach for Find Emotionally Consistent Users?

The best approach is GROUP BY aggregation with a HAVING filter, joined to the Users table on user_id. It makes one pass over the Emotions table to compute per-user statistics, then attaches names with a single join. Use window functions only when the condition depends on row order.

Is Find Emotionally Consistent Users asked at Google/Amazon/Meta?

This problem is tagged as a general SQL question on LeetCode, and it appears in database interview rounds at large tech companies. Companies that test SQL aggregation, such as Amazon, Meta, and Google, commonly use this style of GROUP BY and JOIN question. Check the company tags on your platform for the latest reports.

What data structure is used in Find Emotionally Consistent Users?

The core structure is a relational table with grouping, which the database implements with a hash table or sorted groups internally. In Pandas, the equivalent is a groupby object built on hash-based grouping. You do not write the hash table yourself in SQL.

What is the time complexity of Find Emotionally Consistent Users?

The optimal solution runs in O(n) time with hash-based grouping, or O(n log n) if the database sorts to group rows. Here n is the number of emotion records. The correlated subquery approach is O(n * m) because it rescans records for each user.

Find Emotionally Consistent Users solution and explanation