9. 在线音乐平台综合分析

某在线音乐平台“乐享音乐”希望分析用户的付费订阅和退订行为，以优化内容推荐和定价策略。平台维护用户信息、歌曲信息、订阅支付记录和退订记录四张核心表。

数据规模：用户表约 3000 万行，订阅支付记录每日新增约 200 万行，歌曲表约 60 万行，退订记录每日新增约 5 万行。

9.1 表结构

用户表 dim_users

字段 类型 含义
user_id string 用户编号
user_name string 用户名
register_date string 注册日期
country string 国家
tags array<string> 标签，如 ['premium', 'family']
profile map<string,string> 扩展属性，如 {'age':'22','gender':'F'}
vip_level int VIP 等级，范围 0—5

歌曲表 dim_songs

字段 类型 含义
song_id string 歌曲编号
song_name string 歌曲名称
genre_l1 string 一级流派
genre_l2 string 二级流派
price double 单曲订阅价格
singer string 歌手

订阅支付记录表 fact_subscriptions

字段 类型 含义
sub_id string 订阅记录号
user_id string 用户编号
song_id string 歌曲编号
sub_date string 订阅支付日期
duration int 订阅时长，单位为天
pay_amount double 实际支付金额
status string 状态：completed、expired

退订表 fact_unsubscribes

字段 类型 含义
unsub_id string 退订单号
sub_id string 原订阅记录号
unsub_date string 退订申请日期
reason string 退订原因

9.2 查询高等级 VIP 用户

查询 VIP 等级大于等于 4 的用户编号、用户名、国家，以及 profile 中的年龄。年龄转换为整数，无法转换时显示 NULL。

SELECT
  user_id,
  user_name,
  country,
  CAST(profile['age'] AS INT) AS age
FROM dim_users
WHERE vip_level >= 4;

9.3 统计 Pop 歌手订阅金额 Top 5

查询二级流派为 pop 的歌曲，统计每位歌手的订阅支付总金额和订阅记录数，按总金额降序取前 5 名。

SELECT
  ds.singer,
  SUM(fs.pay_amount) AS total_pay,
  COUNT(*) AS subscription_count
FROM fact_subscriptions fs
JOIN dim_songs ds
  ON fs.song_id = ds.song_id
WHERE ds.genre_l2 = 'pop'
GROUP BY ds.singer
ORDER BY total_pay DESC
LIMIT 5;

9.4 统计发生过退订的歌曲

通过订阅记录关联退订表，找出曾被退订过至少一次的歌曲，输出歌曲编号、歌曲名称和退订次数。

SELECT
  ds.song_id,
  ds.song_name,
  COUNT(fu.unsub_id) AS unsubscribe_count
FROM fact_subscriptions fs
JOIN fact_unsubscribes fu
  ON fs.sub_id = fu.sub_id
JOIN dim_songs ds
  ON fs.song_id = ds.song_id
GROUP BY ds.song_id, ds.song_name
HAVING COUNT(fu.unsub_id) >= 1;

9.5 找出首单支付超过 50 元的用户

首单定义为每位用户按 sub_date 升序排列后的第一条订阅支付记录。假设同一天只有一条记录。

WITH ranked_subscriptions AS (
  SELECT
    user_id,
    pay_amount,
    sub_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY sub_date ASC
    ) AS rn
  FROM fact_subscriptions
)
SELECT
  u.user_id,
  u.user_name,
  r.pay_amount AS first_pay_amount,
  r.sub_date AS first_sub_date
FROM ranked_subscriptions r
JOIN dim_users u
  ON r.user_id = u.user_id
WHERE r.rn = 1
  AND r.pay_amount > 50;