2026-05-31 @ 7:32pm

Domain Expansion: Cursed, sub-optimal SQL query!

I thought it’d be funny to share the first working SQL query draft I came up with from a personal project.

SELECT youtube_artists.youtube_channel_id, 
    artist_name, 
    latest_subscriber_count-min(subscriber_count) as subscriber_delta, 
    max(view_count)-min(view_count) as view_delta, 
    min(view_count) as min_view_count,
    latest_subscriber_count,
    earliest_subscriber_count
FROM youtube_artist_stats
JOIN youtube_artists ON youtube_artist_stats.youtube_id = youtube_artists.youtube_id
JOIN (
    SELECT youtube_id, subscriber_count as latest_subscriber_count
    FROM (SELECT youtube_id, subscriber_count, ROW_NUMBER() OVER (PARTITION BY youtube_id ORDER BY date_pulled DESC) as rn FROM youtube_artist_stats) 
    WHERE rn = 1) AS c 
ON youtube_artist_stats.youtube_id = c.youtube_id
JOIN (
    SELECT youtube_id, subscriber_count as earliest_subscriber_count
    FROM (SELECT youtube_id, subscriber_count, ROW_NUMBER() OVER (PARTITION BY youtube_id ORDER BY date_pulled ASC) as rn FROM youtube_artist_stats) 
    WHERE rn = 1) AS d
ON youtube_artist_stats.youtube_id = d.youtube_id
GROUP BY youtube_artists.youtube_channel_id