题目如下:
1、请你统计每款的SPU(货号)种类数量,并按SPU数量降序排序
-- 11月结束后,小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。
-- 产品情况表product_tb:(item_id指某款号的具体货号,style_id指款号,tag_price表示标签价格,inventory指库存量):
select style_id
,count(DISTINCT item_id) as SPU_num
from product_tb
GROUP BY style_id
ORDER BY SPU_num desc
2、请你统计实际总销售额与客单价(人均付费,总收入/总用户数,结果保留两位小数)
select round(sum(sales_price),2) as sales_total
,round(sum(sales_price)/count(DISTINCT user_id),2) as per_trans
from sales_tb
3、请你统计折扣率(GMV/吊牌金额,GMV指的是成交金额),以上例子的输出结果如下(折扣率保留两位小数)
select round(sum(s.sales_price)/sum(s.sales_num*p.tag_price)*100,2) as discount_rate
from sales_tb s
left join product_tb p
on s.item_id = p.item_id
4、统计每款的动销率(pin_rate,有销售的SKU数量/在售SKU数量)与售罄率(sell-through_rate,GMV/备货值,备货值=吊牌价*库存数) 按style_id升序排序
select style_id, round(sum(sku) / sum(inventory-sku)*100,2) pin_rate,
round(sum(gmv) / sum(inventory*tag_price)*100,2) sell_through_rate
FROM
(SELECT item_id,sum(sales_num) sku ,sum(sales_price) gmv
FROM sales_tb
GROUP BY item_id) t
JOIN product_tb pt USING(item_id)
GROUP by style_id
ORDER by style_id
5、请你统计连续2天及以上在该店铺购物的用户及其对应的次数(若有多个用户,按user_id升序排序)
SELECT user_id, max(rk) as days_count
FROM (
SELECT user_id, sales_date,
dense_rank() over(partition by user_id order by sales_date) as rk
FROM sales_tb
) as t
WHERE rk >= 2
GROUP BY user_id
ORDER BY user_id