HiveSql 面试题 - 统计用户注册日期，总登录次数、订单数等指标

1 需求

从用户登录明细表（user_login_detail）和订单信息表（order_info）中查询每个用户的注册日期（首次登录日期）、总登录次数以及其在2021年的登录次数、订单数和订单总额。

• 需要用到的表

用户登录明细表：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

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

• 期望结果

user_id (用户id)	register_date (注册日期)	total_login_count (累积登录次数)	login_count_2021 (2021年登录次数)	order_count_2021 (2021年下单次数)	order_amount_2021 (2021年订单金额) <decimal(16,2)>
101	2021-09-21	5	5	4	143660.00
102	2021-09-22	4	4	4	177850.00
103	2021-09-23	2	2	4	75890.00
104	2021-09-24	4	4	4	89880.00
105	2021-10-04	1	1	4	120100.00
106	2021-10-04	2	2	4	119150.00
107	2021-09-25	4	4	4	124150.00
108	2021-10-06	2	2	4	155770.00
109	2021-09-26	3	3	2	129480.00

2 解答

• 思路

题目需求比较复杂，由多个小需求合并才可以得到结果数据，并且统计的维度也不太相同，这里分别对每个子需求进行计算，最后再合并即可。

• 实现

1 统计用户的注册日期：使用开窗函数取得第一次登录日期即可

select user_id, date_format(login_ts, 'yyyy-MM-dd') register_date
from (
         SELECT user_id,
                login_ts,
                row_number() over (partition by user_id order by login_ts asc) rn
         from user_login_detail
         group by user_id, login_ts
         ) tmp
where rn = 1

user_id	register_date
101	2021-09-21
1010	2021-09-27
102	2021-09-22
103	2021-09-23
104	2021-09-24
105	2021-10-04
106	2021-10-04
107	2021-09-25
108	2021-10-06
109	2021-09-26

2 统计每个用户的总登录次数

select user_id,count(login_ts) total_login_count from user_login_detail group by user_id

user_id	total_login_count
101	5
1010	2
102	4
103	2
104	4
105	1
106	2
107	4
108	2
109	3

3 统计每个用户在 21 年的登录次数

select user_id,count(login_ts) login_count_2021 from user_login_detail 
where login_ts like '2021%' group by user_id

user_id	login_count_2021
101	5
1010	2
102	4
103	2
104	4
105	1
106	2
107	4
108	2
109	3

4 统计每个用户在 21 年的订单信息

SELECT user_id,count(order_id) order_count_2021,sum(total_amount) order_amount_2021
from order_info where create_date like '2021%' group by user_id

user_id	order_amount_2021	order_count_2021
101	143660.00	4
102	177850.00	4
103	75890.00	4
104	89880.00	4
105	120100.00	4
106	119150.00	4
107	124150.00	4
108	155770.00	4
109	129480.00	2

5 数据合并

select t1.user_id, register_date, total_login_count, login_count_2021, order_count_2021, order_amount_2021
from (select user_id, date_format(login_ts, 'yyyy-MM-dd') register_date
      from (
               SELECT user_id,
                      login_ts,
                      row_number() over (partition by user_id order by login_ts asc) rn
               from user_login_detail
               group by user_id, login_ts
               ) tmp
      where rn = 1) t1
     inner join (select user_id, count(login_ts) total_login_count from user_login_detail group by user_id) t2
                on t1.user_id = t2.user_id
     inner join (select user_id, count(login_ts) login_count_2021
                 from user_login_detail
                 where login_ts like '2021%'
                 group by user_id) t3 on t1.user_id = t3.user_id
     inner join (SELECT user_id, count(order_id) order_count_2021, sum(total_amount) order_amount_2021
                 from order_info
                 where create_date like '2021%'
                 group by user_id) t4 on t1.user_id = t4.user_id