SQL登录次数

表：user_login_detail
字段：user_id login_ts
表：order_info
字段：order_id user_id create_date total_amount

需求：从用户登录明细表 user_login_detail 和订单信息表 order_info 中查询每个用户的注册日期（首次登录日期）

总登录次数，2021登录次数 、订单数，订单金额
期望结果：
user_id register_date total_login_count login_count_2021 order_count_2021 order_amount_2021

-- 方法一
select 
 l.user_id,l.register_date ,l.total_login_count,l.login_count_2021
 ,r.order_count_2021,r.order_amount_2021

 from (
    select user_id,substr(login_ts,1,10) register_date ,total_login_count,login_count_2021  from (
        select user_id,login_ts,row_number()  over(partition by user_id order by login_ts) lgr
        ,count(user_id) over(partition by user_id) total_login_count
        ,count(case when year(login_ts) = '2021' then user_id else null end) over(partition by user_id) login_count_2021
        from user_login_detail 
    ) a where lgr = 1

) l  left join (
    select user_id,count(order_id) order_count_2021,sum(total_amount) order_amount_2021 from order_info
    where create_date between '2021-01-01' and '2021-12-31' group by user_id
) r on r.user_id = r.user_id
;



-- 方法二



select 
  
  one.user_id,two.register_date
     ,two.total_login_count
     ,two.login_count_2021
     ,one.order_count_2021
     ,one.order_amount_2021
  
  from (

    select user_id,count(order_id) order_count_2021,sum(total_amount) order_amount_2021 from order_info
    where create_date between '2021-01-01' and '2021-12-31' group by user_id
) one left join (

    select 
      distinct user_id
     ,register_date
     ,total_login_count
     ,count(*) over(partition by user_id) login_count_2021
    
     from (
        select 
         user_id
         ,login_date
         ,count(*) over(partition by user_id) total_login_count
         -- 注意这里 first_value 的用法
         ,first_value(login_date) over(partition by user_id order by login_date) register_date
         
         from (
            select  user_id,substr(login_ts,1,10) login_date from user_login_detail
        ) a 
    ) b where year(b.login_date) = '2021'
) two on one.user_id = two.user_id