Data mining the Podcast Index

The Podcast Index - released by Adam Curry and Dave Jones more than five years ago - is a super-useful thing to go data mining into the wide world of podcasts.

There’s a weekly-updated file containing every public feed in the Podcast Index - it’s here, and is about 1.8GB. Unzip it, and it becomes a 5GB file, which you can “easily” interrogate using a thing like this SQLite browser.

Once you’ve installed the SQLite browser - if you have Homebrew installed, it’s just brew install --cask db-browser-for-sqlite …

• Open the podcastindex_feeds.db file.
• Hit the “Execute SQL” tab.
• Post one of the SQL statements below.

I’m mostly posting this so I have a note of them.

How many podcasts have been updated in the past year?

This looks at your data dump to find out the latest updated show, and then displays a count for how many were updated in the year up to that date.

SELECT
    SUM(IIF(newestItemPubdate > ((SELECT MAX(newestItemPubdate) FROM podcasts) - 31556952), 1, 0)) AS inlastyear,
    SUM(IIF(newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 31556952), 1, 0)) AS older
FROM podcasts;

(I’ve removed a few from this list, which were outdated).