阿里数据分析岗在线笔试的一道sql题

select item_id, buyer_id, sum(amt) as buyer_all_amt from A where where dt >= '20180701' and dt <= '20180930' and seller_id = 123  and  item_id in (select a.item_id from           (select item_id, sum(amt) as all_amt from A           where dt >= '20180701' and dt <= '20180930' and seller_id = 123            group by item_id            order by all_amt desc            limit 1)  a ) group by  item_id, buyer_id  order by buyer_all_amt desc limit 1

select a.item_id,b.buyer_id from ( select item_id,max(sum(amt)) as max_sum_amt from A where dt>=201807 and dt<=201809 and buyer_id=123 group by 1 )a left join  ( select item_id,buyer_id,max(sum(amt)) as max_sum_amt from A where dt>=201807 and dt<=201809 group by 1,2 ) b on a.item_id=b.item_id

0

with tmp as( select c.item_id from (select b.item_id, b.sum_amt, rank()over(order by b.sum_amt desc) rnk from (select item_id, sum(amt) sum_amt from (select buyer_id, item_id, amt from A  where month(dt) in (7,8,9) and seller_id = '123' )a group by item_id )b )c where c.rnk = 1)      select buyer_id, item_id from A where item id = (select item_id from tmp) group by buyer_id, item_id order by SUM(amt) desc limit 1

select item_id,buyer_id from(      select item_id,buyer_id,dense_rank() over(order by tot_amt desc) as ranking1,      dense_rank() over(partition by item_id order by amt desc) as ranking2 from (            select item_id, buyer_id,amt,sum(amt) over (partition by item_id order by amt ) as tot_amt             from A             where dt>=201807 and dt <=201809 and seller_id= 123) as a ) as b where b.ranking1 = 1 and b.ranking2=1; 我的方法是采用窗口函数，先计算每个item_id的总金额tot_amt，然后用dense_rank()分别对tot_amt、amt排序，再根据条件ranking=1，选择 item_id,buyer_id  ps：采用rank()函数是考虑到可能有销售总额相同的 item_id以及最高购买金额相同的buyer_id，若直接采用limit1来选择最大值可能导致选择的数据不全

老哥也是闲鱼吗

我也收到笔试通知了是一个小时，不知道是考算法还是sql

--获得交易金额最高的商品id with tmp_id as ( select  item_id  ,max(amt_total) from (select    item_id   ,sum(amt) as amt_total from A where dt >= '20180701' and dt <= '20180901' and seller_id = '123' group by item_id) select   a1.item_id  ,a2.buyer_id  ,max(a2.amt) as max_amt from (select item_id from tmp_id) a1 left join (select    item_id   ,buyer_id   ,amt from A where dt >= '20180701' and dt <= '20180901' and seller_id = '123') a2 on A1.item_id = A2.item_id group by a1.item_id, a2.buyer_id 只会很复杂的😂

select b.*, distinct c.buyer_id, max(gmv_by_buyer)over(partition by c.buyer_id) as 最豪买家 from (select distinct b.item_id , max(gmv_by_item)over(partition by b.item_id)as 最热销单品 from (select item_id, sum(amp)over(partition by item_id ) as gmv_by_item from A  where month(dt) in (7,8,9) and seller_id = '123' )bb )b join  (select  item_id, buyer_id , sum(amp)over(partition by buyer_id ) as gmv_by_buyer from A  where month(dt) in (7,8,9) and seller_id = '123' )c on b.item_id=c.item_id

with max_item_id as (select item_id ( select item_id,sum(amt) as sum_amt,rom_number() over(order by sum(amt) desc ) rn from table_a where to_date(dt,'yyyymmdd')>=to_date('20180701','yyyymmdd') and to_date(dt,'yyyymmdd')<=to_date('20180931','yyyymmdd') and a.seller_id='123' group by item_id )t where t.rn=1 ) select item_id,buyer_id from ( select a.item_id,a.buyer_id,sum(a.amt) sum_buyer_id,rom_number() over(order by sum(a.amt) desc ) rn from table_a a join max_item_id b on a.item_id=b.item_id where to_date(dt,'yyyymmdd')>=to_date('20180701','yyyymmdd') and to_date(dt,'yyyymmdd')<=to_date('20180931','yyyymmdd') and a.seller_id='123' group by a.item_id,a.buyer_id ) t where t.rn=1 ;