题解 | 查询下订单用户访问次数？
查询下订单用户访问次数？
https://www.nowcoder.com/practice/32bc1e0fce2343ad934b76a025e09fc5
WITH filtered_orders AS (
SELECT DISTINCT user_id
FROM order_tb
WHERE order_time >= '2022-09-02'
AND order_time < '2022-09-03'
),
filtered_visits AS (
SELECT user_id, COUNT(*) AS visit_nums
FROM visit_tb
WHERE visit_time >= '2022-09-02'
AND visit_time < '2022-09-03'
GROUP BY user_id
)
SELECT fv.user_id, fv.visit_nums
FROM filtered_visits fv
JOIN filtered_orders fo ON fv.user_id = fo.user_id
order by visit_nums desc
先过滤再join，用两个cte，一个代表过滤order，一个代表过滤访问，之后再join。注意笛卡尔积。
查看7道真题和解析