文章目录
SQL练习题:回购率 复购率 消费差异
统计不同月份的下单人数
select month(paidTime),count(distinct userid)
from data.orderinfor
where isPaid = '已支付'
group by month(paidTime);
统计用户三月份的回购率和复购率
1、复购率:这个月份中所有消费人数中消费以上的举手
select count(ct),count(if(ct>1, 1, null)) from (
select userId, count(userId) as ct
from data.orderinfor
where isPaid = '已支付' and month(paidTime) = 3
group by userId) t;
2、回购率:上个月份购买的人数这个月份依旧购买
(1) 得到每个用户在哪个时间消费过
select userId, date_format(paidTime,'%Y-%m-01') from data.orderinfor
where idPaid = '已支付'
group by userId, date_format(paidTime,'%Y-%m-01')
select *
from t1
left join
t2 on t1.userId = t2.userID;
select *
from t1
left join
t2 on t1.userId = t2.userID
and t1.m = date_sub(t2.m, interval 1 month) ;
select t1.m, count(t1.m), count(t2.m)
from t1
left join
t2 on t1.userId = t2.userID
where t1.m = date_sub(t2.m, interval 1 month)
group by t1.m;
统计男女用户的消费频次是否有差异
就是男生平均消费多少次,女生平均消费多少次
select avg() over(partition by a.sex ) from
(select * from data.userinfor
where sex <> '') a;
统计多次消费的用户,第一次和最后一次消费间隔是多少
max min datediff
统计不同年龄段,用户的消费金额是否有差异
case when 年龄
统计消费的二八法则,消费的top20%的用户,贡献了多少额度
ntile(5) over order by