表: Calls
+--------------+----------+ | Column Name | Type | +--------------+----------+ | caller_id | int | | recipient_id | int | | call_time | datetime | | city | varchar | +--------------+----------+ (caller_id, recipient_id, call_time) 是该表的主键(具有唯一值的列)。 每一行包含 caller id, recipient id, call time,和 city。
编写一个查询,找到每个 city
的 高峰 通话 时间。如果 多个时间 有 相同 数量的通话,则所有这些时间都将被视为该特定城市的 高峰时间。
按照 高峰时间 和 city
按 降序 排序返回结果表。
结果格式如下例所示。
示例 1:
输入: Calls table: +-----------+--------------+---------------------+----------+ | caller_id | recipient_id | call_time | city | +-----------+--------------+---------------------+----------+ | 8 | 4 | 2021-08-24 22:46:07 | Houston | | 4 | 8 | 2021-08-24 22:57:13 | Houston | | 5 | 1 | 2021-08-11 21:28:44 | Houston | | 8 | 3 | 2021-08-17 22:04:15 | Houston | | 11 | 3 | 2021-08-17 13:07:00 | New York | | 8 | 11 | 2021-08-17 14:22:22 | New York | +-----------+--------------+---------------------+----------+ 输出: +----------+-------------------+-----------------+ | city | peak_calling_hour | number_of_calls | +----------+-------------------+-----------------+ | Houston | 22 | 3 | | New York | 14 | 1 | | New York | 13 | 1 | +----------+-------------------+-----------------+ 解释: 对于 Houston: - 高峰时间是 22:00,总共记录了 3 次通话。 对于 New York: - 3:00 和 14:00 都有相同数量的通话,因此这两个时间都被视为高峰时间。 输出表按照高峰时间和城市按降序排序。
# Write your MySQL query statement below
WITH
T AS (
SELECT
*,
RANK() OVER (
PARTITION BY city
ORDER BY cnt DESC
) AS rk
FROM
(
SELECT
city,
HOUR(call_time) AS h,
COUNT(1) AS cnt
FROM Calls
GROUP BY 1, 2
) AS t
)
SELECT city, h AS peak_calling_hour, cnt AS number_of_calls
FROM T
WHERE rk = 1
ORDER BY 2 DESC, 1 DESC;