O2O优惠券核销-SQL实现

十二十二呀

已于 2023-01-10 22:14:26 修改

阅读量664

点赞数 2

分类专栏：数据分析笔记文章标签： sql

于 2023-01-10 22:11:35 首次发布

本文链接：https://blog.csdn.net/twlve/article/details/128626526

版权

该博客通过SQL深入分析O2O优惠券数据，涵盖各距离的领券、消费和核销率，消费券类型、满减门槛、工作日核销周期、月份趋势等关键指标，揭示用户和商家的核销行为特点。

摘要由CSDN通过智能技术生成

前言：

目的：利用O2O优惠券数据集锻炼个人SQL编写能力

过程：将文件导入datagrip，利用SQL进行数据分析，具体分析内容见另一篇博客：O2O优惠券核销-数据分析

说明：本次SQL主要实现上述博客中的仪表盘部分

1. 输出样本数据的整体情况

2. 查找各距离的领券人数/用券消费人数/核销率

3. 消费券使用情况占比

4. 不同优惠券类型的核销情况和平均领取距离

5. 不同满减门槛的核销情况

6. 不同工作日的优惠券平均核销周期、核销率

7. 不同月份优惠券领券次数/核销次数/核销率

8. 不同领券次数用户的分布情况（平均核销率/占比）

9. 不同核销率用户分布（占比）

10. 不同领券次数商家的分布情况（平均核销率/占比）

11. 不同核销率的商家分布情况（占比）

12.仪表盘

1. 输出样本数据的整体情况

select
    count(User_id) as '样本总数',
    count(distinct Merchant_id) as '商家总数',
    count(distinct User_id) as '用户总数',
    count(Date) as '消费总数',
    count(Date_received) as '领券总数',
    (select count(*) from ddm.offline_train as a where a.Date_received is not null and a.Date is not null) as '领券消费总数',
    (select count(*) from ddm.offline_train as a where a.Date_received is null and a.Date is not null) as '无券消费总数',
    (select count(*) from ddm.offline_train as a where a.Date_received is not null and a.Date is not null)/count(Date_received) as '核销率'
from ddm.offline_train

2. 查找各距离的领券人数/用券消费人数/核销率

# 查找各距离的领券人数/用券消费人数/核销率
select
    Distance,
    count(Coupon_id) as get_coupons_num,
    sum(if(Date_received is not null and Date is not null,1,0)) as user_coupons_num,
    sum(if(Date_received is not null and Date is not null,1,0)) /count(Coupon_id) as use_coupons_rate
from ddm.offline_train
where Distance is not null
group by Distance
order by distance

3. 消费券使用情况占比

# 消费券使用情况占比
with temp as (
    select
        case
            when Date_received is not null and Date is not null then '有券消费'
            when Date_received is not null and Date is null then '有券未消费'
            when Date_received is null and Date is not null then '无券消费'
        end as flag
    from ddm.offline_train
)
select
    flag as '优惠券使用情况',
    concat(round(count(flag)/(select count(*) from temp)*100,2),'%') as '百分比'
from temp
group by flag
order by count(flag)/(select count(*) from temp)

4. 不同优惠券类型的核销情况和平均领取距离

# 不同优惠券类型的核销情况和平均领取距离
select
    Discount_rate,
    avg(Distance) as '平均距离',
    count(Date_received) as '领券人数',
    sum(if(Date_received is not null and Date is not null,1,0)) as '