拼多多提前批第二批笔试

可能不正确

1.

用户订单表ord，字段user_id,ord_id,goods_id,goods_num,ord_amt,create_time

求最近30天每件商品的平均售价

select good_id,sum(ord_amt)/sum(good_num)
from ord
where datediff(now(),date(create_time))<=30
group by goods_id

2.

用户浏览网站页面流水表pv_log，字段pv_id,user_id,create_time

网站用户维表dim_user，字段user_id,age,create_time

求浏览不同页面的用户对应的平均年龄

select pv_id,sum(dim_user.age)/count(dim_user.age)
from pv_log left join dim_user on pv_log.user_id=dim_user.user_id
group by pv_log.pv_id

3.

用户订单表tbl_ordr，字段user_id,ordr_id,ord_goods,ordr_time

用户商品点击明细表clk,字段clk_id,user_id,clk_time,clk_goods

假设仅有一天数据，点击和下单必须是同一用户同一商品，多次点击然后下单的话算最后一次点击。输出用户点击后下单的记录，clk_id，ordr_id

select tbl_ordr.user_id,tbl_ordr.ord_goods
from tbl_ordr left join clk on tbl_ordr.user_id=clk.user_id and tbl_ordr.ord_goods=clk.clk_goods
where tbl_ordr.clk_time>=clk.clk_time
group by (tbl_ordr.user_id,tbl_ordr.ord_goods)