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