An artist report for a streaming app

-- Per song: how often it was played and skipped
WITH song_plays AS (
  SELECT songs.id,
         songs.artist_id,
         songs.title,
         COUNT(*) AS plays,
         SUM(plays.skipped) AS skips
  FROM songs
  JOIN plays ON plays.song_id = songs.id
  GROUP BY songs.id
),

-- Number each artist's songs, most played first
ranked AS (
  SELECT artist_id,
         title,
         plays,
         ROW_NUMBER() OVER (PARTITION BY artist_id ORDER BY plays DESC, title) AS place
  FROM song_plays
),

-- Per artist: plays, different listeners, skips
totals AS (
  SELECT songs.artist_id,
         COUNT(*) AS plays,
         COUNT(DISTINCT plays.listener_id) AS listeners,
         SUM(plays.skipped) AS skips
  FROM songs
  JOIN plays ON plays.song_id = songs.id
  GROUP BY songs.artist_id
)

SELECT a.name AS artist,
       a.genre,
       COALESCE(t.plays, 0) AS plays,
       COALESCE(t.listeners, 0) AS listeners,
       CAST(ROUND(100.0 * t.skips / t.plays) AS INTEGER) || '%' AS skip_rate,
       r.title || ' (' || r.plays || ')' AS top_song
FROM artists AS a
LEFT JOIN totals AS t ON t.artist_id = a.id
LEFT JOIN ranked AS r ON r.artist_id = a.id AND r.place = 1
ORDER BY plays DESC, artist;