find-emotionally-consistent-users

java

python3

python

javascript

typescript

csharp

golang

kotlin

swift

rust

ruby

dart

scala

elixir

erlang

racket

cangjie

bash

mysql

mssql

postgresql

oraclesql

pythondata

Database

Table: <code>reactions</code>

<pre>
+--------------+---------+
| 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.
</pre>

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

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

Return the result table ordered by <code>reaction_ratio</code> in descending order and then by <code>user_id</code> in ascending order.

Note:

<ul>
	<li><code>reaction_ratio</code>&nbsp;should be rounded to <code>2</code> decimal places</li>
</ul>

The result format is in the following example.

&nbsp;
Example:

<div class="example-block">
Input:

reactions table:

<pre class="example-io">
+---------+------------+----------+
| 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 |
+---------+------------+----------+
</pre>

Output:

<pre class="example-io">
+---------+-------------------+----------------+
| user_id | dominant_reaction | reaction_ratio |
+---------+-------------------+----------------+
| 3 | love | 1.00 |
| 1 | like | 0.80 |
+---------+-------------------+----------------+
</pre>

Explanation:

<ul>
	<li>User 1:

	<ul>
		<li>Total reactions = 5</li>
		<li>like&nbsp;appears 4 times</li>
		<li>reaction_ratio = 4 / 5 = 0.80</li>
		<li>Meets the 60% consistency requirement</li>
	</ul>
	</li>
	<li>User 2:
	<ul>
		<li>Total reactions = 5</li>
		<li>Most frequent reaction appears only 2 times</li>
		<li>reaction_ratio = 2 / 5 = 0.40</li>
		<li>Does not meet the consistency requirement</li>
	</ul>
	</li>
	<li>User 3:
	<ul>
		<li>Total reactions = 5</li>
		<li>&#39;love&#39; appears 5 times</li>
		<li>reaction_ratio = 5 / 5 = 1.00</li>
		<li>Meets the consistency requirement</li>
	</ul>
	</li>
</ul>

The Results table is ordered by reaction_ratio in descending order, then by user_id in ascending order.
</div>

find_emotionally_consistent_users

Find Emotionally Consistent Users

表：<code>reactions</code>

<pre>
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| user_id | int |
| content_id | int |
| reaction | varchar |
+--------------+---------+
(user_id, content_id) 是这张表的主键（值互不相同）。
每一行代表用户对某条内容的反应。
</pre>

根据以下要求编写一个解决方案，以识别 情绪一致的用户：

<ul>
	<li>为每个用户统计他们发送的总反应次数。</li>
	<li>仅包含 至少对 <code>5</code> 个不同内容项 作出反应的用户。</li>
	<li>如果用户 至少 <code>60%</code> 的反应属于 同一种类型，则认为其 情绪一致。</li>
</ul>

返回结果表按&nbsp;<code>reaction_ratio</code> 降序&nbsp;排序，然后按&nbsp;<code>user_id</code> 升序&nbsp;排序。

注意：

<ul>
	<li><code>reaction_ratio</code>&nbsp;应该舍入到&nbsp;<code>2</code>&nbsp;位小数</li>
</ul>

结果格式如下所示。

&nbsp;

示例：

<div class="example-block">
输入：

reactions 表：

<pre class="example-io">
+---------+------------+----------+
| 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 |
+---------+------------+----------+
</pre>

输出：

<pre class="example-io">
+---------+-------------------+----------------+
| user_id | dominant_reaction | reaction_ratio |
+---------+-------------------+----------------+
| 3 | love | 1.00 |
| 1 | like | 0.80 |
+---------+-------------------+----------------+
</pre>

解释：

<ul>
	<li>用户 1：

	<ul>
		<li>总反应数&nbsp;= 5</li>
		<li>'like' 出现了 4 次</li>
		<li>reaction_ratio = 4 / 5 = 0.80</li>
		<li>满足 60% 一致的要求</li>
	</ul>
	</li>
	<li>用户 2：
	<ul>
		<li>总反应数 = 5</li>
		<li>出现最多的反应只出现了 2 次</li>
		<li>reaction_ratio = 2 / 5 = 0.40</li>
		<li>不满足一致的要求</li>
	</ul>
	</li>
	<li>用户 3：
	<ul>
		<li>总反应数 = 5</li>
		<li>'love' 出现了 5 次</li>
		<li>reaction_ratio = 5 / 5 = 1.00</li>
		<li>满足一致的要求</li>
	</ul>
	</li>
</ul>

结果表按 reaction_ratio 降序排序，然后按 user_id 升序排序。
</div>