+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| song_id | int |
| day | date |
+-------------+---------+
This table may contain duplicates (In other words, there is no primary key for this table in SQL).
Each row of this table indicates that the user user_id listened to the song song_id on the day day.
Table: Friendship
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
In SQL,(user1_id, user2_id) is the primary key for this table.
Each row of this table indicates that the users user1_id and user2_id are friends.
Note that user1_id < user2_id.
Recommend friends to Leetcodify users. We recommend user x to user y if:
Users x and y are not friends, and
Users x and y listened to the same three or more different songs on the same day.
Note that friend recommendations are unidirectional, meaning if user x and user y should be recommended to each other, the result table should have both user x recommended to user y and user y recommended to user x. Also, note that the result table should not contain duplicates (i.e., user y should not be recommended to user x multiple times.).
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Listens table:
+---------+---------+------------+
| user_id | song_id | day |
+---------+---------+------------+
| 1 | 10 | 2021-03-15 |
| 1 | 11 | 2021-03-15 |
| 1 | 12 | 2021-03-15 |
| 2 | 10 | 2021-03-15 |
| 2 | 11 | 2021-03-15 |
| 2 | 12 | 2021-03-15 |
| 3 | 10 | 2021-03-15 |
| 3 | 11 | 2021-03-15 |
| 3 | 12 | 2021-03-15 |
| 4 | 10 | 2021-03-15 |
| 4 | 11 | 2021-03-15 |
| 4 | 13 | 2021-03-15 |
| 5 | 10 | 2021-03-16 |
| 5 | 11 | 2021-03-16 |
| 5 | 12 | 2021-03-16 |
+---------+---------+------------+
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
+----------+----------+
Output:
+---------+----------------+
| user_id | recommended_id |
+---------+----------------+
| 1 | 3 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
+---------+----------------+
Explanation:
Users 1 and 2 listened to songs 10, 11, and 12 on the same day, but they are already friends.
Users 1 and 3 listened to songs 10, 11, and 12 on the same day. Since they are not friends, we recommend them to each other.
Users 1 and 4 did not listen to the same three songs.
Users 1 and 5 listened to songs 10, 11, and 12, but on different days.
Similarly, we can see that users 2 and 3 listened to songs 10, 11, and 12 on the same day and are not friends, so we recommend them to each other.
# Write your MySQL query statement below
WITH L AS (
SELECT DISTINCT user_id, song_id, day
FROM Listens
),
Pairs AS (
SELECT
a.user_id AS user_id,
b.user_id AS recommended_id
FROM L a
JOIN L b
ON a.day = b.day
AND a.song_id = b.song_id
AND a.user_id <> b.user_id
GROUP BY a.user_id, b.user_id, a.day
HAVING COUNT(*) >= 3
)
SELECT DISTINCT p.user_id, p.recommended_id
FROM Pairs p
WHERE NOT EXISTS (
SELECT 1
FROM Friendship f
WHERE f.user1_id = LEAST(p.user_id, p.recommended_id)
AND f.user2_id = GREATEST(p.user_id, p.recommended_id)
);
复制代码
题目大意
给用户 y 推荐用户 x,需要同时满足两个条件:
两人不是好友。
两人在同一天听过至少 3 首相同的不同歌曲。
推荐是单向的:如果 x 和 y 互相符合条件,结果里要有 (x→y) 和 (y→x) 两行,同一对不能重复出现。
解法思路
第一步:去重(CTE L)
sql
SELECT DISTINCT user_id, song_id, day FROM Listens
第二步:自连接找出"同一天听同一首歌"的用户对(CTE Pairs)
sql
FROM L a
JOIN L b
ON a.day = b.day
AND a.song_id = b.song_id
AND a.user_id <> b.user_id
a.day = b.day 且 a.song_id = b.song_id:两人同一天听了同一首歌。
a.user_id <> b.user_id:排除用户和自己配对。
自连接会同时产生 (1,3) 和 (3,1),正好满足"双向推荐"的要求,不用额外处理。
第三步:按天分组,统计共同歌曲数
sql
GROUP BY a.user_id, b.user_id, a.day
HAVING COUNT(*) >= 3
第四步:排除已经是好友的用户对
sql
WHERE NOT EXISTS (
SELECT 1 FROM Friendship f
WHERE f.user1_id = LEAST(p.user_id, p.recommended_id)
AND f.user2_id = GREATEST(p.user_id, p.recommended_id)
)
1919. Leetcodify Similar Friends
Hard
Topics
SQL Schema
Pandas Schema
Table: Listens
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| song_id | int |
| day | date |
+-------------+---------+
This table may contain duplicate rows.
Each row of this table indicates that the user user_id listened to the song song_id on the day day.
Table: Friendship
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
(user1_id, user2_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the users user1_id and user2_id are friends.
Note that user1_id < user2_id.
Write a solution to report the similar friends of Leetcodify users. A user x and user y are similar friends if:
Users x and y are friends, and
Users x and y listened to the same three or more different songs on the same day.
Return the result table in any order. Note that you must return the similar pairs of friends the same way they were represented in the input (i.e., always user1_id < user2_id).
The result format is in the following example.
Example 1:
Input:
Listens table:
+---------+---------+------------+
| user_id | song_id | day |
+---------+---------+------------+
| 1 | 10 | 2021-03-15 |
| 1 | 11 | 2021-03-15 |
| 1 | 12 | 2021-03-15 |
| 2 | 10 | 2021-03-15 |
| 2 | 11 | 2021-03-15 |
| 2 | 12 | 2021-03-15 |
| 3 | 10 | 2021-03-15 |
| 3 | 11 | 2021-03-15 |
| 3 | 12 | 2021-03-15 |
| 4 | 10 | 2021-03-15 |
| 4 | 11 | 2021-03-15 |
| 4 | 13 | 2021-03-15 |
| 5 | 10 | 2021-03-16 |
| 5 | 11 | 2021-03-16 |
| 5 | 12 | 2021-03-16 |
+---------+---------+------------+
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 2 | 4 |
| 2 | 5 |
+----------+----------+
Output:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
+----------+----------+
Explanation:
Users 1 and 2 are friends, and they listened to songs 10, 11, and 12 on the same day. They are similar friends.
Users 1 and 3 listened to songs 10, 11, and 12 on the same day, but they are not friends.
Users 2 and 4 are friends, but they did not listen to the same three different songs.
Users 2 and 5 are friends and listened to songs 10, 11, and 12, but they did not listen to them on the same day.
第二步:好友对分别连接两人的收听记录
sql
FROM Friendship f
JOIN L a ON a.user_id = f.user1_id
JOIN L b ON b.user_id = f.user2_id
AND b.day = a.day
AND b.song_id = a.song_id
a 是 user1_id 的收听记录,b 是 user2_id 的收听记录。
连接条件要求 day 和 song_id 都相同,所以每个结果行代表"两人同一天都听过的一首歌"。
第三步:按天分组,统计共同歌曲数
sql
GROUP BY f.user1_id, f.user2_id, a.day
HAVING COUNT(*) >= 3
+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
+----------------+----------+
user_id is the column of unique values for this table.
Each row contains information about the signup time for the user with ID user_id.
Table: Confirmations
+----------------+----------+
| Column Name | Type |
+----------------+----------+
| user_id | int |
| time_stamp | datetime |
| action | ENUM |
+----------------+----------+
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
user_id is a foreign key (reference column) to the Signups table.
action is an ENUM (category) of the type ('confirmed', 'timeout')
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').
The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.
Write a solution to find the confirmation rate of each user.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Signups table:
+---------+---------------------+
| user_id | time_stamp |
+---------+---------------------+
| 3 | 2020-03-21 10:16:13 |
| 7 | 2020-01-04 13:57:59 |
| 2 | 2020-07-29 23:09:44 |
| 6 | 2020-12-09 10:39:37 |
+---------+---------------------+
Confirmations table:
+---------+---------------------+-----------+
| user_id | time_stamp | action |
+---------+---------------------+-----------+
| 3 | 2021-01-06 03:30:46 | timeout |
| 3 | 2021-07-14 14:00:00 | timeout |
| 7 | 2021-06-12 11:57:29 | confirmed |
| 7 | 2021-06-13 12:58:28 | confirmed |
| 7 | 2021-06-14 13:59:27 | confirmed |
| 2 | 2021-01-22 00:00:00 | confirmed |
| 2 | 2021-02-28 23:59:59 | timeout |
+---------+---------------------+-----------+
Output:
+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6 | 0.00 |
| 3 | 0.00 |
| 7 | 1.00 |
| 2 | 0.50 |
+---------+-------------------+
Explanation:
User 6 did not request any confirmation messages. The confirmation rate is 0.
User 3 made 2 requests and both timed out. The confirmation rate is 0.
User 7 made 3 requests and all were confirmed. The confirmation rate is 1.
User 2 made 2 requests where one was confirmed and the other timed out. The confirmation rate is 1 / 2 = 0.5.
# Write your MySQL query statement below
SELECT
s.user_id,
ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate
楼主: Myron2017
狗仔卡
楼主
