sql 如何根据月份查询数据总数_数据分析—笔面试专题4

SQL题

快手数据分析实习生面试题

表sale如下所示，查询每个商品类目下当月累计销量金额大于9万的卖家数占比分布。

解题思路

第一步：在原表中增加月份列，并将查询结果作为表a

select *, month(date) as 月份 from sale;

第二步：计算每月卖家总数，将查询结果作为表b

select month(date) as 月份, count(distinct seller_id) as 卖家总数 
from sale
group by month(date);

第三步：采用左连接，将表a和表b按月份进行连接，并根据分组字段“商品类目”，“月份”和筛选条件“累计金额大于9万”写出SQL语句

select a.p_class, a.月份, count(distinct a.seller_id) as 卖家数, b.卖家总数,
count(distinct a.seller_id)/b.卖家总数 as 人数占比
from (select *, month(date) as 月份 from sale) as a 
left join (select month(date) as 月份, count(distinct seller_id) as 卖家总数 from sale group by month(date)) as b
on a.月份 = b.月份
group by a.p_class, a.月份
having sum(a.pay_amount) > 90000;

概率统计

统计抽样知识点总结