390 lines
11 KiB
SQL
390 lines
11 KiB
SQL
-- 用户购课完课情况统计脚本
|
||
-- 统计范围：已支付金额≥498元的有效订单用户
|
||
-- 输出字段：账号ID、角色ID、激活课包、性别、年龄、省市、账号/角色创建日期、购课信息、L1/L2最新完课信息
|
||
|
||
select c.account_id as "账号ID"
|
||
,c.id as "角色ID"
|
||
,c.purchase_season_package as "激活课包"
|
||
,c.gender as "角色性别"
|
||
,2026 - cast(c.birthday as int) as "角色年龄"
|
||
,f.account_province as "角色省"
|
||
,f.account_city as "角色市"
|
||
,a.account_created_date as "账号创建日期"
|
||
,c.character_created_date as "角色创建日期"
|
||
,b.goods_name as "课包"
|
||
,b.key_from as "购课渠道"
|
||
,b.pay_date as "购课日期"
|
||
,d.chapter_name_L1 as "L1课程名称"
|
||
,d.finish_date_L1 as "L1完课日期"
|
||
,d.gap_date_L1 as "L1距离上次完课间隔"
|
||
,e.chapter_name_L2 as "L2课程名称"
|
||
,e.finish_date_L2 as "L2完课日期"
|
||
,e.gap_date_L2 as "L2距离上次完课间隔"
|
||
|
||
from
|
||
(
|
||
select id
|
||
,to_char(created_at,'YYYY-MM-DD') as account_created_date
|
||
from bi_vala_app_account
|
||
where status = 1
|
||
group by id
|
||
,created_at
|
||
) as a
|
||
left join
|
||
(
|
||
select account_id
|
||
,goods_name
|
||
,key_from
|
||
,to_char(pay_success_date,'YYYY-MM-DD') as pay_date
|
||
from bi_vala_order
|
||
where pay_amount_int > 49800
|
||
and order_status = 3
|
||
group by account_id
|
||
,goods_name
|
||
,key_from
|
||
,pay_success_date
|
||
) as b on a.id = b.account_id
|
||
left join
|
||
(
|
||
select id
|
||
,account_id
|
||
,case when purchase_season_package in ('[1,16,17,18,19,20,2,12,13,14]','[1,16,2,12,13,14,17,18,19,20]','[1,2,12,13,14,17,18,19,20]','[16,1,17,18,19,20,2,12,13,14]','[16,1,2,12,13,14,17,18,19,20]','[16,17,18,19,20,1,2,12,13,14]','[1,2,12,13,14,16,17,18,19,20]') then 'L1和L2'
|
||
when purchase_season_package in ('[1,16,17,18,19,20]','[16,1,17,18,19,20]','[16,17,18,19,20]','[16,17,18,19,20,1]') then 'L1'
|
||
when purchase_season_package in ('[1,16,17]','[16,1,17]','[16,17]','[16,17,1]') then 'L1S1'
|
||
when purchase_season_package in ('[1,16,2,12,13,14,17]') then 'L1S1和L2'
|
||
when purchase_season_package in ('[1,16,2,12,13,14]','[1,2,12,13,14,16]' ,'[1,2,12,13,14]','[16,1,2,12,13,14]') then 'L2'
|
||
when purchase_season_package in ('[1,16,2]','[1,2,16]','[1,2]','[16,1,2]') then 'L2S1'
|
||
when purchase_season_package in ('[1,2,12]') then 'L2S1和L2S2'
|
||
when purchase_season_package in ('[1,16,17,18,19,20,2]') then 'L1和L2S1'
|
||
when purchase_season_package in ('[1,16]','[1]','[16]','[16,1]','[]') then '没激活'
|
||
else purchase_season_package
|
||
end as purchase_season_package
|
||
,to_char(created_at,'YYYY-MM-DD') as character_created_date
|
||
,case when gender = 0 then '男'
|
||
when gender = 1 then '女'
|
||
else '未知'
|
||
end as gender
|
||
,case when split_part(birthday,'-',1) = '' then '0000'
|
||
else split_part(birthday,'-',1)
|
||
end as birthday
|
||
from bi_vala_app_character
|
||
where deleted_at is NULL
|
||
group by id
|
||
,account_id
|
||
,purchase_season_package
|
||
,created_at
|
||
,gender
|
||
,birthday
|
||
) as c on b.account_id = c.account_id
|
||
left join
|
||
(
|
||
select user_id
|
||
,finish_date as finish_date_L1
|
||
,gap_date as gap_date_L1
|
||
,chapter_name as chapter_name_L1
|
||
from
|
||
(
|
||
select user_id
|
||
,finish_date
|
||
,gap_date
|
||
,chapter_name
|
||
,rank () over (partition by user_id order by finish_date desc) as rankno
|
||
from
|
||
(
|
||
select user_id
|
||
,finish_date
|
||
,current_date - finish_date_1 as gap_date
|
||
,format('%s-%s-%s-%s',course_level,course_season,course_unit,course_lesson) as chapter_name
|
||
,rank () over (partition by user_id order by format('%s-%s-%s-%s',course_level,course_season,course_unit,course_lesson) desc) as rankno
|
||
from
|
||
(
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_0
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_1
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_2
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_3
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_4
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_5
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_6
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_7
|
||
where play_status = 1
|
||
and chapter_id > 331
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
) as x
|
||
left join
|
||
(
|
||
select cast(id as int) as id
|
||
,course_level
|
||
,course_season
|
||
,course_unit
|
||
,course_lesson
|
||
from bi_level_unit_lesson
|
||
group by id
|
||
,course_level
|
||
,course_season
|
||
,course_unit
|
||
,course_lesson
|
||
) as y on x.chapter_id = y.id
|
||
group by user_id
|
||
,finish_date_1
|
||
,finish_date
|
||
,chapter_id
|
||
,course_level
|
||
,course_season
|
||
,course_unit
|
||
,course_lesson
|
||
)
|
||
where rankno = 1
|
||
group by user_id
|
||
,finish_date
|
||
,gap_date
|
||
,chapter_name
|
||
)
|
||
where rankno = 1
|
||
) as d on c.id = d.user_id
|
||
left join
|
||
(
|
||
select user_id
|
||
,finish_date as finish_date_L2
|
||
,gap_date as gap_date_L2
|
||
,chapter_name as chapter_name_L2
|
||
from
|
||
(
|
||
select user_id
|
||
,finish_date
|
||
,gap_date
|
||
,chapter_name
|
||
,rank () over (partition by user_id order by finish_date desc) as rankno
|
||
from
|
||
(
|
||
select user_id
|
||
,finish_date
|
||
,current_date - finish_date_1 as gap_date
|
||
,format('%s-%s-%s-%s',course_level,course_season,course_unit,course_lesson) as chapter_name
|
||
,rank () over (partition by user_id order by format('%s-%s-%s-%s',course_level,course_season,course_unit,course_lesson) desc) as rankno
|
||
from
|
||
(
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_0
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_1
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_2
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_3
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_4
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_5
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_6
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
union all
|
||
select user_id
|
||
,chapter_id
|
||
,to_char(updated_at,'YYYY-MM-DD') as finish_date
|
||
,date(updated_at) as finish_date_1
|
||
from bi_user_chapter_play_record_7
|
||
where play_status = 1
|
||
and chapter_id < 332
|
||
group by user_id
|
||
,chapter_id
|
||
,updated_at
|
||
) as x
|
||
left join
|
||
(
|
||
select cast(id as int) as id
|
||
,course_level
|
||
,course_season
|
||
,course_unit
|
||
,course_lesson
|
||
from bi_level_unit_lesson
|
||
group by id
|
||
,course_level
|
||
,course_season
|
||
,course_unit
|
||
,course_lesson
|
||
) as y on x.chapter_id = y.id
|
||
group by user_id
|
||
,finish_date_1
|
||
,finish_date
|
||
,chapter_id
|
||
,course_level
|
||
,course_season
|
||
,course_unit
|
||
,course_lesson
|
||
)
|
||
where rankno = 1
|
||
group by user_id
|
||
,finish_date
|
||
,gap_date
|
||
,chapter_name
|
||
)
|
||
where rankno = 1
|
||
) as e on c.id = e.user_id
|
||
left join
|
||
(
|
||
select account_id
|
||
,split_part(login_address,'-',1) as account_province
|
||
,split_part(login_address,'-',2) as account_city
|
||
from account_detail_info
|
||
group by account_id
|
||
,login_address
|
||
) as f on c.account_id = f.account_id
|
||
group by c.account_id
|
||
,c.id
|
||
,c.purchase_season_package
|
||
,c.gender
|
||
,c.birthday
|
||
,f.account_province
|
||
,f.account_city
|
||
,a.account_created_date
|
||
,c.character_created_date
|
||
,b.goods_name
|
||
,b.key_from
|
||
,b.pay_date
|
||
,d.chapter_name_L1
|
||
,d.finish_date_L1
|
||
,d.gap_date_L1
|
||
,e.chapter_name_L2
|
||
,e.finish_date_L2
|
||
,e.gap_date_L2
|
||
order by b.key_from
|
||
,b.pay_date;
|