NIUKE SQL：大厂面试真题（五） 【某宝】

SQL180 某宝店铺的SPU数量

请你统计每款的SPU（货号）数量，并按SPU数量降序排序

SELECT style_id,COUNT(item_id) AS SPU_num
FROM product_tb
GROUP BY style_id
ORDER BY SPU_num DESC

SQL181 某宝店铺的实际销售额与客单价

请你统计实际总销售额与客单价（人均付费，总收入/总用户数，结果保留两位小数）

SELECT SUM(sales_price) AS sales_total,
    ROUND(SUM(sales_price)/COUNT(DISTINCT user_id),2) AS per_trans
FROM sales_tb

SQL182 某宝店铺折扣率

请你统计折扣率（GMV/吊牌金额，GMV指的是成交金额）折扣率保留两位小数

SELECT ROUND(SUM(sales_price)/SUM(tag_price*sales_num)*100,2) AS 'discount_rate(%)'
FROM product_tb JOIN sales_tb USING(item_id)

SQL183 某宝店铺动销率与售罄率

请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）与售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数），按style_id升序排序

SELECT 
    style_id,
    ROUND(SUM(num)/(SUM(inventory)-SUM(num))*100,2) AS 'pin_rate(%)',
    ROUND(SUM(item_GMV)/SUM(tag_price*inventory)*100,2) AS 'sell-through_rate(%)'
FROM product_tb AS A JOIN 
(   SELECT item_id,SUM(sales_num) AS num,SUM(sales_price) AS item_GMV 
    FROM sales_tb 
    GROUP BY item_id    
) AS B USING(item_id)

GROUP BY style_id
ORDER BY style_id

SQL184 某宝店铺连续2天及以上购物的用户及其对应的天数

请你统计连续2天及以上在该店铺购物的用户及其对应的次数（若有多个用户，按user_id升序排序）

SELECT user_id,COUNT(*) AS days_count
FROM(
    SELECT
	    DISTINCT user_id,
        sales_date,
        DENSE_RANK() over(PARTITION by user_id ORDER BY sales_date) AS rn
    FROM sales_tb
) AS a
GROUP BY user_id,DATE_ADD(sales_date,INTERVAL - rn day)
HAVING days_count >=2
ORDER BY user_id