Skip to content

Instantly share code, notes, and snippets.

@BenWard
Created December 18, 2023 01:34
Show Gist options
  • Save BenWard/38a0c4948e4b3146347ae73f519f6ec7 to your computer and use it in GitHub Desktop.
Save BenWard/38a0c4948e4b3146347ae73f519f6ec7 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment