Hive sql 进阶题 02

需要建表语句的私信留言均可查询用户的累计消费金额及VIP等级从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额以及每个用户在其每个下单日期的VIP等级。用户vip等级根据累积消费金额计算计算规则如下设累积消费总额为X若0X10000,则vip等级为普通会员若10000X30000,则vip等级为青铜会员若30000X50000,则vip等级为白银会员若50000X80000,则vip为黄金会员若80000X100000,则vip等级为白金会员若X100000,则vip等级为钻石会员select *, case when sum 100000 then 钻石会员 when sum 80000 then 白金会员 when sum 50000 then 黄金会员 when sum 30000 then 白银会员 when sum 10000 then 青铜会员 when sum 0 then 普通会员 end vip from (select user_id, create_date, sum(total_amount_per_day) over (partition by user_id order by create_date) sum from (select user_id, create_date, sum(total_amount) total_amount_per_day from order_info group by user_id, create_date) t1 ) t2;查询首次下单后第二天连续下单的用户比率从订单信息表(order_info)中查询首次下单后第二天仍然下单的用户占所有下单用户的比例结果保留一位小数使用百分数显示select concat(round(count(if(diff 1, 1, null)) / count(*) * 100), %) from (select user_id, create_date, datediff(lead(create_date, 1, 9999-12-31) over (partition by user_id order by create_date), create_date) diff, rank() over (partition by user_id order by create_date) rk from (select user_id, create_date from order_info group by user_id, create_date) t1) t2 where rk 1;每个商品销售首年的年份、销售数量和销售金额从订单明细表(order_detail)统计每个商品销售首年的年份销售数量和销售总额。select sku_id, years, num_amount, sum_amount from (select sku_id, years, sum(sku_num) num_amount, sum(amount) sum_amount, rank() over (partition by sku_id order by years) rk from (select sku_id, substr(create_date, 1, 4) years, price * sku_num amount, sku_num from order_detail) t1 group by sku_id, years) t2 where rk 1;筛选去年总销量小于100的商品从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量假设今天的日期是2022-01-10不考虑上架时间小于一个月的商品select t1.sku_id, name, order_num from ( select sku_id, sum(sku_num) order_num from order_detail where year(create_date) 2021 and sku_id in ( select sku_id from sku_info where datediff(2022-01-10, from_date) 30 ) group by sku_id having sum(sku_num) 100 ) t1 left join sku_info t2 on t1.sku_id t2.sku_id;