78 lines
3.2 KiB
SQL
78 lines
3.2 KiB
SQL
-- 2026年3月后未退费订单，激活课程在下单30天内的学习进度分布
|
||
-- 区分 L1 和 L2
|
||
|
||
WITH orders AS (
|
||
-- 2026-03-01起，状态非已退款（order_status!=4）的订单
|
||
SELECT o.id, o.out_trade_no, o.account_id, o.pay_success_date
|
||
FROM bi_vala_order o
|
||
JOIN bi_vala_app_account a ON o.account_id = a.id
|
||
WHERE o.pay_success_date >= '2026-03-01'
|
||
AND o.order_status != 4
|
||
AND o.pay_success_date IS NOT NULL
|
||
AND a.status = 1 -- 排除测试账号
|
||
),
|
||
tickets AS (
|
||
-- 订单关联的已激活课程票
|
||
SELECT o.out_trade_no, o.account_id, o.pay_success_date,
|
||
t.character_id, t.season_package_level
|
||
FROM orders o
|
||
JOIN bi_vala_seasonal_ticket t ON o.out_trade_no = t.out_trade_no
|
||
WHERE t.status = 1
|
||
AND t.deleted_at IS NULL
|
||
AND t.season_package_level IN ('A1', 'A2')
|
||
),
|
||
all_chapter_records AS (
|
||
-- 合并8张分表的完课记录
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_0 WHERE play_status = 1
|
||
UNION ALL
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_1 WHERE play_status = 1
|
||
UNION ALL
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_2 WHERE play_status = 1
|
||
UNION ALL
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_3 WHERE play_status = 1
|
||
UNION ALL
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_4 WHERE play_status = 1
|
||
UNION ALL
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_5 WHERE play_status = 1
|
||
UNION ALL
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_6 WHERE play_status = 1
|
||
UNION ALL
|
||
SELECT user_id, chapter_id, created_at FROM bi_user_chapter_play_record_7 WHERE play_status = 1
|
||
),
|
||
level_records AS (
|
||
-- 完课记录关联课程等级
|
||
SELECT pr.user_id, pr.chapter_id, pr.created_at, l.course_level
|
||
FROM all_chapter_records pr
|
||
JOIN bi_level_unit_lesson l ON pr.chapter_id = l.id
|
||
WHERE l.course_level IN ('L1', 'L2')
|
||
),
|
||
ticket_progress AS (
|
||
-- 每个ticket（订单+角色+等级）在下单30天内的完课数
|
||
SELECT
|
||
t.out_trade_no,
|
||
t.account_id,
|
||
t.character_id,
|
||
t.season_package_level,
|
||
t.pay_success_date,
|
||
COUNT(DISTINCT lr.chapter_id) AS completed_lessons
|
||
FROM tickets t
|
||
LEFT JOIN level_records lr
|
||
ON t.character_id = lr.user_id
|
||
AND lr.course_level = CASE
|
||
WHEN t.season_package_level = 'A1' THEN 'L1'
|
||
WHEN t.season_package_level = 'A2' THEN 'L2'
|
||
END
|
||
AND lr.created_at >= t.pay_success_date
|
||
AND lr.created_at <= t.pay_success_date + INTERVAL '30 days'
|
||
GROUP BY t.out_trade_no, t.account_id, t.character_id, t.season_package_level, t.pay_success_date
|
||
)
|
||
-- 分布统计
|
||
SELECT
|
||
CASE WHEN season_package_level = 'A1' THEN 'L1' ELSE 'L2' END AS course_level,
|
||
completed_lessons,
|
||
COUNT(*) AS ticket_count,
|
||
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY season_package_level), 1) AS pct
|
||
FROM ticket_progress
|
||
GROUP BY season_package_level, completed_lessons
|
||
ORDER BY season_package_level, completed_lessons;
|