30. 登录次数及交易次数统计

题目需求

分别从登录明细表（user_login_detail）和配送信息表（delivery_info）中，根据用户登录时间和下单时间统计每个用户在每一天的登录次数和交易次数。

期望结果如下：

user_id （用户id）	login_date （登录时间）	login_count （登陆次数）	order_count （交易次数）
101	2021-09-21	1	0
101	2021-09-27	1	1
101	2021-09-28	1	1
101	2021-09-29	1	1
…	…	…	…

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

配送信息表：delivery_info

delivery_id （运单 id ）	order_id （订单id）	user_id （用户 id ）	order_date （下单日期）	custom_date （期望配送日期）
1	1	101	2021-09-27	2021-09-29
2	2	101	2021-09-28	2021-09-28
3	3	101	2021-09-29	2021-09-30

实现一

select t1.user_id             as user_id,
       t1.login_date          as login_date,
       t1.login_count         as login_count,
       nvl(t2.order_count, 0) as order_count
from (
         -- 1） 统计每个 user_id 在 每一天的 登录次数
         select user_id,
                date_format(login_ts, 'yyyy-MM-dd') as login_date,
                count(*)                            as login_count
         from user_login_detail
         group by user_id, date_format(login_ts, 'yyyy-MM-dd')
     ) t1
         left join
     (
         -- 2） 统计每个 user_id 在 每一天的 交易(下单)次数
         select user_id,
                order_date,
                count(*) as order_count
         from delivery_info
         group by user_id, order_date
     ) t2
     on t1.user_id = t2.user_id and t1.login_date = t2.order_date;

题目来源

http://practice.atguigu.cn/#/question/30/desc?qType=SQL