时间戳

首页 > 解决方案 > SQL Amazon BIE 面试题 客户表

mysql - SQL Amazon BIE 面试题 客户表

问题描述

交易表

列类型 id 整数 user_id 整数 created_at 日期时间 product_id 整数 数量 整数

用户表

列类型 id 整数名称 varchar 性别 varchar

编写查询以识别在 2019 年和 2020 年分别进行超过三笔交易的客户。

输出

列类型 customer_name 字符串

我的代码是：</p> 

WITH CTE AS( SELECT u.name, count(user_id) AS "Num_Apperence",YEAR(t.created_at)
             FROM transactions t
             LEFT JOIN users u 
             ON t.user_id = u.id
             WHERE YEAR(t.created_at) = 2019
             GROUP BY u.name
             HAVING count(user_id) > 3
)
SELECT Name1
FROM (SELECT u.name AS 'Name1', count(user_id) AS "Num_Apperence",YEAR(t.created_at)
FROM transactions t
LEFT JOIN users u 
ON t.user_id = u.id
WHERE YEAR(t.created_at) = 2020
GROUP BY u.name
HAVING count(user_id) > 3) AS T
INNER JOIN CTE 
ON CTE.name = T.Name1

这是可运行的，但无法通过测试用例。我就纳闷了，怎么回事？？

标签： mysql

解决方案

一种方法是使用带有条件聚合的单个查询来查找在 2019 年和 2020 年进行超过三笔交易的用户：

WITH cte AS (
    SELECT user_id
    FROM transactions
    GROUP BY user_id
    HAVING COUNT(CASE WHEN YEAR(created_at) = 2019 THEN 1 END) > 3 AND
           COUNT(CASE WHEN YEAR(created_at) = 2020 THEN 1 END) > 3
)

SELECT
    u.id,
    u.name
FROM users u
INNER JOIN cte t
    ON t.user_id = u.id;

推荐阅读