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;

How many podcasts have only published one episode and then failed?

(This query ignores the last 30 days)

select count(*) from podcasts where episodeCount=1 AND newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 2592000)

How many active podcasts have only published one episode in the last 12 months?

(Again, this query ignores the last 30 days)

SELECT
    SUM(IIF(episodeCount=1 AND newestItemPubdate > ((SELECT MAX(newestItemPubdate) FROM podcasts) - 34148952), 1, 0) AND newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 2592000)) AS onlyoneepisode,
    SUM(IIF(newestItemPubdate > ((SELECT MAX(newestItemPubdate) FROM podcasts) - 34148952), 1, 0) AND newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 2592000)) AS allactive
FROM podcasts;