SQL练习笔记——统计每日的新充用户数，新提用户人数，新用户充提比

表中包含信息如下，统计每日的新充用户数，新提用户人数，新用户充提比

字段说明：statics_date:统计日期，channel:渠道，desc:描述，registerTime:用户注册时间，uid:用户ID，registerip:注册IP

解题思路

#1.查询出每天的新充用户人数和新提用户人数（充值日期=注册日期，提现日期=注册日期）

利用 count(distinct + case when)

select statics_date as 日期,
count(distinct case when `desc` ='充值' and date(registerTime) = statics_date then uid end) as 新充用户人数,
count(distinct case when `desc` ='提现' and date(registerTime) = statics_date then uid end) as 新提用户人数
from record
group by 日期;

#2.计算新用户充提比（新用户当日提现人数/新用户当日充值人数）

with 每日统计 as
(select statics_date as 日期,
count(distinct case when `desc` ='充值' and date(registerTime) = statics_date then uid end) as 新充用户人数,
count(distinct case when `desc` ='提现' and date(registerTime) = statics_date then uid end) as 新提用户人数
from record
group by 日期
)
select *,concat(round((新提用户人数/新充用户人数),2)*100,'%')  as 新用户充提比
from 每日统计;

输出结果：