Hive sql 进阶题 03

用户注册、登录、下单综合统计从用户登录明细表user_login_detail和订单信息表order_info中查询每个用户的注册日期首次登录日期、总登录次数以及其在2021年的登录次数、订单数和订单总额。select t1.user_id, register_date, cnt, cnt2021, order_count_2021, order_amount_2021 from (select user_id, min(date_format(login_ts, yyyy-MM-dd)) register_date, count(*) cnt, count(if(year(login_ts) 2021, 1, null)) cnt2021 from user_login_detail group by user_id) t1 left join (select user_id, count(if(year(create_date) 2021, 1, null)) order_count_2021, sum(if(year(create_date) 2021, total_amount, 0)) order_amount_2021 from order_info group by user_id) t2 on t1.user_id t2.user_id;查询指定日期的全部商品价格从商品价格修改明细表sku_price_modify_detail中查询2021-10-01的全部商品的价格假设所有商品初始价格默认都是99。select sku_info.sku_id, nvl(new_price, 99) 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.sku_id t2.sku_id;即时订单比例订单配送中如果期望配送日期和下单日期相同称为即时订单如果期望配送日期和下单日期不同称为计划订单。请从配送信息表delivery_info中求出每个用户的首单用户的第一个订单中即时订单的比例保留两位小数以小数形式显示。select round(count(if(order_date custom_date, 1, null)) / count(*), 2) percentage from (select user_id, order_date, custom_date, row_number() over (partition by user_id order by order_date) rn from delivery_info) t1 where rn 1;向用户推荐朋友收藏的商品现需要请向所有用户推荐其朋友收藏但是用户自己未收藏的商品请从好友关系表friendship_info和收藏表favor_info中查询出应向哪位用户推荐哪些商品。select distinct t1.user_id, friend_favor.sku_id from ( select user1_id user_id, user2_id friend_id from friendship_info union select user2_id, user1_id from friendship_info ) t1 left join favor_info friend_favor on t1.friend_id friend_favor.user_id left join favor_info user_favor on t1.user_id user_favor.user_id and friend_favor.sku_id user_favor.sku_id where user_favor.sku_id is null;查询所有用户的连续登录两天及以上的日期区间登录明细表user_login_detail中查询出所有用户的连续登录两天及以上的日期区间以登录时间login_ts为准。select user_id, min(login_date) start_date, max(login_date) end_date from ( select user_id, login_date, date_sub(login_date, rn) flag from ( select user_id, login_date, row_number() over (partition by user_id order by login_date) rn from ( select user_id, date_format(login_ts, yyyy-MM-dd) login_date from user_login_detail group by user_id, date_format(login_ts, yyyy-MM-dd) ) t1 ) t2 ) t3 group by user_id, flag having count(*) 2;