@
1. 题目需求
分别从登陆明细表(user_login_detail)和配送信息表中用户登录时间和下单时间统计登陆次数和交易次数
结果如下(截取部分):
2. 需要用到的表
用户登录明细表:user_login_detail
配送信息表:delivery_info
3. 查询sql
SELECT
A.user_id,
A.login_date,
if (A.login_count is not null, A.login_count, 0) as login_count,
if (B.order_count is not null, B.order_count, 0) as order_count
FROM
(
select
user_id,
date_format (login_ts, 'yyyy-MM-dd') as login_date,
count(login_ts) as login_count
from
user_login_detail
group by
user_id,
date_format (login_ts, 'yyyy-MM-dd')
) A
left join (
select
user_id,
date_format (order_date, 'yyyy-MM-dd') as order_date,
count(order_date) as order_count
from
delivery_info
group by
user_id,
date_format (order_date, 'yyyy-MM-dd')
) B ON A.user_id = B.user_id
and A.login_date = B.order_date