local-albums.sql

SELECT
  COUNT(Track.id) AS playCount,
  CanonicalArtist.title AS ArtistName,
  CanonicalPage.title AS AlbumName,
FROM
  -- Tables to query playlist info
  tracks AS Track
  INNER JOIN broadcasts AS Broadcast ON (Track.broadcast_id = Broadcast.id)
  INNER JOIN programs AS Program ON (Program.id = Track.program_id)
  -- Tables to query canonical presentation
  INNER JOIN wiki_pages AS LookupPage ON (LookupPage.album_key = Track.album_key)
  -- Map the codex entry to the canonical representation where duplicates have been editorially collapsed:
  INNER JOIN wiki_pages AS CanonicalPage ON (CanonicalPage.id = COALESCE(LookupPage.alias_to, LookupPage.id))
  
  -- Look up the associated artist
  INNER JOIN wiki_pages AS ArtistLookup ON (ArtistLookup.artist_key = CanonicalPage.artist_key)
  INNER JOIN wiki_pages AS CanonicalArtist ON (CanonicalArtist.id = COALESCE(ArtistLookup.alias_to, ArtistLookup.id))
WHERE
      -- Broadcast exclusions:
      Broadcast.copy_of = 0 -- Exclude re-runs
  AND Program.exclude_charts = 0 -- Exclude programs set to exclude from charts
  -- Date Range:
  AND YEAR(Track.played) = 2023
  -- Look up corresponding entry in the codex
  AND LookupPage.type = 'album'
  AND LookupPage.artist_key = Track.artist_key
  AND ArtistLookup.type = 'artist'
  -- Exclude deactivated codex content
  AND CanonicalPage.active = 1
  AND CanonicalArtist.active = 1
  -- Only local artists
  AND CanonicalArtist.tag_local = 1
GROUP BY
  CanonicalPage.id, CanonicalArtist.id
ORDER BY
  playCount DESC