Spotifyオープンデータを用いた2023年チャート分析

--楽曲名の重複は認めず、アーティストごとに楽曲数を集計します
select
  artist,
  count(distinct song) as chart_in_count
from
  your_table
group by
  1
order by
  2 desc

--連続してTop1を取った日数
with ranked_days as (
  select
    date,
    song,
    artist,
    --次に1位を取る日の抽出
    lead(date) over (partition by song, artist order by date) as next_date
  from
    your_table
  where
    date between '2023-05-18' and '2024-02-25'
    and position = 1
),
differences as (
  select
    song,
    artist,
    date,
    next_date,
    --現在日時と次に1位を取る日の差分の抽出
    date_diff(next_date, date, day) as diff
  from
    ranked_days
),
grouped as (
  select
    song,
    artist,
    date,
    --現在日時と次に1位を取る日の差分が1日の場合は0を、それ以外の場合は1を返します(連続して1位を取っているか否か)
    if(diff = 1, 0, 1) as is_break,
    --同一の楽曲が連続して1位を取っている区間を0, 1, 2, 3, 4.....と数値を割り振ります
    sum(if(diff = 1, 0, 1)) over (partition by song, artist order by date) as groupid
  from
    differences
),
consecutivetopdays as (
  select
    song,
    artist,
    --下2行のクエリでそれぞれの同一の楽曲が連続して1位を取っている区間の合計日数を抽出
    groupid,
    count(*) as consecutivedays
  from
    grouped
  group by
    1, 2, 3
),
maxconsecutivetopdays as (
  select
    song,
    artist,
    --同一の楽曲が連続して1位を取っている区間の最大値の抽出
    max(consecutivedays) as maxconsecutivedays
  from
    consecutivetopdays
  group by
    1, 2
)
select
  song,
  artist,
  maxconsecutivedays
from
  maxconsecutivetopdays
order by
  3 desc

クエリ結果は下記の通り

以下は各リージョンごとの集計結果です。
今回の集計期間は283日間ですが、
日本のチャート1位を連続で取っていた上位2楽曲のみで140日以上居座り続けました。また、1位を取った楽曲は7種類と楽曲の人気にかなり偏りがあることが伺えます。
アメリカは最大でも1ヶ月程度しかチャート1位を連続取り続ける楽曲がありません。
イギリスを比較すると、一部の楽曲がチャート1位を連続して取っており日本とかなり傾向が似ています。

日本

アメリカ

イギリス

3. 1週間ごとの楽曲チャートTop10推移

集計期間は283日間ありますが、全ての日程でプロットすると視覚的に認識し辛くなるので毎週月曜日のチャートTop10を取得します。

select
  date,
  position,
  song,
  artist
from
 your_table
where 
  date between '2023-05-18' and '2024-02-25'
  --月曜日のデータのみを抽出
  and extract(dayofweek from date) = 2
  and position between 1 and 10
order by
  1, 2 asc