1. 题目需求
从用户登录明细表(user_login_detail)和订单信息表(order_info)中查询每个用户的注册日期(首次登录日期)、总登录次数以及其在2021年的登录次数、订单数和订单总额。
期望结果如下:
2. 需要用到的表:
用户登录明细表:user_login_detail
订单信息表:order_info
3. 查询sql
select
t1.user_id,
register_date,
total_login_count,
login_count_2021,
order_count_2021,
order_amount_2021
from
(
select
user_id,
min(date_format (login_ts, 'yyyy-MM-dd')) register_date,
count(*) total_login_count,
sum(if (year (login_ts) = 2021, 1, 0)) login_count_2021
from
user_login_detail
group by
user_id
) t1
join (
select
user_id,
count(*) order_count_2021,
sum(total_amount) order_amount_2021
from
order_info
where
year (create_date) = 2021
group by
user_id
) t2 on t1.user_id = t2.user_id