111 lines
4.5 KiB
SQL
111 lines
4.5 KiB
SQL
-- L1+L2用户(821人)上周学习情况：仅学L1 / 仅学L2 / 都学了
|
||
|
||
WITH last_week_active_chars AS (
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_0 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
UNION
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_1 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
UNION
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_2 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
UNION
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_3 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
UNION
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_4 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
UNION
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_5 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
UNION
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_6 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
UNION
|
||
SELECT DISTINCT user_id FROM bi_user_chapter_play_record_7 WHERE play_status = 1 AND created_at >= '2026-05-11' AND created_at < '2026-05-18'
|
||
),
|
||
last_week_active_accounts AS (
|
||
SELECT DISTINCT c.account_id
|
||
FROM last_week_active_chars lw
|
||
JOIN bi_vala_app_character c ON lw.user_id = c.id
|
||
),
|
||
paying_active AS (
|
||
SELECT DISTINCT lw.account_id
|
||
FROM last_week_active_accounts lw
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM bi_vala_order o
|
||
JOIN bi_vala_app_account a ON o.account_id = a.id AND a.status = 1
|
||
WHERE o.account_id = lw.account_id
|
||
AND o.order_status IN (3, 4)
|
||
AND o.pay_success_date IS NOT NULL
|
||
)
|
||
),
|
||
user_goods AS (
|
||
SELECT o.account_id, o.goods_id
|
||
FROM bi_vala_order o
|
||
JOIN paying_active pa ON o.account_id = pa.account_id
|
||
WHERE o.order_status IN (3, 4) AND o.pay_success_date IS NOT NULL
|
||
),
|
||
l1l2_accounts AS (
|
||
SELECT account_id
|
||
FROM user_goods
|
||
GROUP BY account_id
|
||
HAVING bool_or(goods_id = 61) OR (bool_or(goods_id IN (57, 60, 63)) AND bool_or(goods_id IN (31, 32, 33, 54)))
|
||
),
|
||
l1l2_chars AS (
|
||
SELECT c.id AS user_id, c.account_id
|
||
FROM bi_vala_app_character c
|
||
JOIN l1l2_accounts a ON c.account_id = a.account_id
|
||
),
|
||
last_week_chapters AS (
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_0 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
UNION ALL
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_1 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
UNION ALL
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_2 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
UNION ALL
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_3 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
UNION ALL
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_4 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
UNION ALL
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_5 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
UNION ALL
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_6 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
UNION ALL
|
||
SELECT p.user_id, p.chapter_id
|
||
FROM bi_user_chapter_play_record_7 p
|
||
WHERE p.play_status = 1 AND p.created_at >= '2026-05-11' AND p.created_at < '2026-05-18'
|
||
),
|
||
chapter_with_level AS (
|
||
SELECT DISTINCT c.account_id, bl.course_level
|
||
FROM last_week_chapters lw
|
||
JOIN l1l2_chars c ON lw.user_id = c.user_id
|
||
JOIN bi_level_unit_lesson bl ON lw.chapter_id = bl.id
|
||
WHERE bl.course_level IN ('L1', 'L2')
|
||
),
|
||
user_level_study AS (
|
||
SELECT
|
||
account_id,
|
||
bool_or(course_level = 'L1') AS studied_l1,
|
||
bool_or(course_level = 'L2') AS studied_l2
|
||
FROM chapter_with_level
|
||
GROUP BY account_id
|
||
)
|
||
SELECT
|
||
CASE
|
||
WHEN studied_l1 AND studied_l2 THEN 'L1和L2都学了'
|
||
WHEN studied_l1 THEN '仅学了L1'
|
||
WHEN studied_l2 THEN '仅学了L2'
|
||
END AS study_type,
|
||
COUNT(*) AS user_count
|
||
FROM user_level_study
|
||
GROUP BY 1
|
||
ORDER BY 1;
|