search

openpodcast / api

OpenPodcast API server
https://openpodcast.dev
8 stars 2 forks source link

extend monitoring to check tables (incl. anchor) based on auth tables #148

Closed woolfg closed 10 months ago

woolfg commented 10 months ago 

WITH

podcasts_spotify as (SELECT DISTINCT account_id FROM spotifyPodcastListeners JOIN podcasts USING (account_id)),
podcasts_apple as (SELECT DISTINCT account_id FROM appleTrendsPodcastFollowers JOIN podcasts USING (account_id)),
podcasts_anchor as (SELECT DISTINCT account_id FROM anchorTotalPlays JOIN podcasts USING (account_id)),
yesterday as (SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) as day)

SELECT CONCAT("no data in spotifyPodcastListeners on ",day) as problem, podcasts.account_id FROM
podcasts_spotify as podcasts
JOIN yesterday
LEFT JOIN spotifyPodcastListeners spotify ON (podcasts.account_id = spotify.account_id AND day=spl_date)
WHERE spl_date IS NULL

union

SELECT CONCAT("no data in appleTrendsPodcastFollowers on ",day) as problem, podcasts.account_id FROM
podcasts_apple as podcasts
JOIN yesterday
LEFT JOIN appleTrendsPodcastFollowers apple ON (podcasts.account_id = apple.account_id AND day=atf_date)
WHERE atf_date IS NULL and podcasts.account_id <> 1

union

SELECT CONCAT("no data in anchorTotalPlays on ",day) as problem, podcasts.account_id FROM
podcasts_anchor as podcasts
JOIN yesterday
LEFT JOIN anchorTotalPlays anchor ON (podcasts.account_id = anchor.account_id AND day=date)
WHERE date IS NULL

union 

SELECT CONCAT("0 values for listeners in spotifyPodcastAgeBase on ",date) as problem, account_id
FROM `spotifyPodcastAgeBase`
WHERE `spotifyPodcastAgeBase`.`date` >= DATE_SUB(NOW(), INTERVAL 1 week)
GROUP BY account_id, date
having SUM(`spotifyPodcastAgeBase`.`listeners`) = 0 AND account_id = 2

already in place for a while