podcasts.sql

podcasts.sql

SELECT
    YEAR(v.created) AS 'year',
    CASE
        WHEN MONTH(v.created) = '1' THEN 'Jan'
        WHEN MONTH(v.created) = '2' THEN 'Feb'
        WHEN MONTH(v.created) = '3' THEN 'Mar'
        WHEN MONTH(v.created) = '4' THEN 'Apr'
        WHEN MONTH(v.created) = '5' THEN 'May'
        WHEN MONTH(v.created) = '6' THEN 'Jun'
        WHEN MONTH(v.created) = '7' THEN 'Jul'
        WHEN MONTH(v.created) = '8' THEN 'Aug'
        WHEN MONTH(v.created) = '9' THEN 'Sep'
        WHEN MONTH(v.created) = '10' THEN 'Oct'
        WHEN MONTH(v.created) = '11' THEN 'Nov'
        WHEN MONTH(v.created) = '12' THEN 'Dec'
    END AS 'month',
    FLOOR((SUM(v.duration) / 60) / 60) AS 'aggregate_version_durations'
FROM
    version v
        JOIN
    version_version_type vvt ON vvt.pid = v.pid
        AND vvt.version_type_id = 'Podcast'
        JOIN
    ondemand od ON od.version_pid = v.pid
WHERE
    YEAR(v.created) = '2017'
GROUP BY MONTH(v.created) , YEAR(v.created)
ORDER BY YEAR(v.created) , MONTH(v.created)