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

题目：已知表名A，字段分别为dt（交易日期，格式为20180101）、seller_id（卖家id）、buyer_id（买家id），item_id（商品），amt（交易金额，单位元）：写出下面sql语句: 统计201807~201809期间，卖家id：123，店铺内交易金额最高的商品id以及购买该商品id 金额最高的买家id
这种题型最简便的写法应该是怎样的，有大佬知道么😅

#阿里巴巴##笔试题目##数据分析师#

全部评论

推荐最新楼层

不一样的Mayday

门头沟学院数据分析师

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

点赞回复分享

发布于 2019-04-13 16:47

大王叫我来巡山哟~

门头沟学院产品经理

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

5 回复分享

发布于 2019-08-22 21:03

蓝色天宇

四川大学数据库工程师

点赞回复分享

发布于 2019-04-29 04:00

想不到什么昵称

拼多多_数据分析师

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

点赞回复分享

发布于 2019-08-20 22:09

尼喜lu

武汉大学测试工程师

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来选择最大值可能导致选择的数据不全

点赞回复分享

发布于 2019-08-22 20:27

蔚来汽车内推

蔚来汽车_数据分析工程师

老哥也是闲鱼吗

点赞回复分享

发布于 2019-08-22 20:31

番茄天后

华中科技大学算法工程师

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

点赞回复分享

发布于 2019-08-23 22:57

让我上岸吧~

国家开放大学数据分析师

--获得交易金额最高的商品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 只会很复杂的😂

点赞回复分享

发布于 2019-08-27 10:40

Feara

吉林大学产品经理

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

点赞回复分享

发布于 2019-08-30 00:41

牛客370083735号

天津大学仁爱学院 ETL工程师

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 ;

点赞回复分享

发布于 2023-06-16 12:40 河南