【大厂真题】SQL 14题解 | 查询下订单用户访问次数？

# 需求分析：结果表
# 排序


SELECT t.user_id,m.nums as visit_nums
from (
        SELECT DISTINCT a.user_id,DATE_FORMAT(order_time,'%Y-%m-%d') as order_date
        from order_tb a
        where DATE_FORMAT(order_time,'%Y-%m-%d')='2022-09-02'
) t
inner JOIN 
(
        SELECT DATE_FORMAT(visit_time,'%Y-%m-%d') as visit_date,user_id,count(*) nums
        from visit_tb b
        # where TIMESTAMPDIFF(second,visit_time,leave_time)>0 测试案例有错误数据，找到对应
        group by DATE_FORMAT(visit_time,'%Y-%m-%d'),user_id
) m 
on t.user_id=m.user_id
and t.order_date=m.visit_date
order by visit_nums desc