Podcast Index Top 500 Normie Feeds

gistfile1.txt

WITH ranked_podcasts AS (
    SELECT 
        title, 
        url,
        imageUrl,
        datetime(lastUpdate, 'unixepoch') as lastUpdateDate, 
        popularityScore,
        ROW_NUMBER() OVER (ORDER BY lastUpdate DESC) as position
    FROM podcasts
    WHERE 'spirituality' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'religion' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'weather' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'non-profit' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'nonprofits' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'cricket' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'rugby' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'music' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'cryptocurrency' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND 'spirituality' NOT IN (category1, category2, category3, category4, category5, category6, category7, category8, category9, category10)
       AND language = 'en'
       AND dead = 0
       AND popularityScore = 9
       AND priority = 5
       AND lower(title) NOT LIKE '%bitcoin%'
       AND lower(title) NOT LIKE '%music%'
)
SELECT 
    position, 
    title, 
    url,
    imageUrl,
    lastUpdateDate,
    popularityScore
FROM ranked_podcasts
LIMIT 500;