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:
5 different content items.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 placesThe 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:
The Results table is ordered by reaction_ratio in descending order, then by user_id in ascending order.