hivesql刷题40题-【11-20】

1.11 用户注册、登录、下单综合统计

1.11.1 题目需求

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

select login.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')) as register_date,
         count(1) total_login_count,
         count(if(year(login_ts) = '2021', 1, null)) login_count_2021
         from user_login_detail
         group by user_id
     ) login
         join
     (
         select user_id,count(distinct(order_id))  order_count_2021,
         sum(total_amount) order_amount_2021
         from order_info
         where year(create_date) = '2021'
         group by user_id
     ) oi
     on login.user_id = oi.user_id

1.12 查询指定日期的全部商品价格

1.12.1 题目需求

从商品价格修改明细表（sku_price_modify_detail）中查询2021-10-01的全部商品的价格，假设所有商品初始价格默认都是99。期望结果如下：

select sku_info.sku_id,nvl(new_price, 99) as price
from sku_info
left join
     (
         select sku_id,new_price
         from (
                  select sku_id,new_price,change_date,row_number() over (partition by sku_id order by change_date desc) rn
                  from sku_price_modify_detail
                  where change_date <= '2021-10-01'
              ) t1
         where rn = 1
     ) t2
  on sku_info