Publishing cadence

1-- core.wordpress.publishing_cadence
2-- How much is published and edited each month, per author and content type: the
3-- editorial output report WordPress itself does not have.
4--
5-- Grain: one row per month (site time zone) x post type x author.
6-- Depends on: staging.wordpress.posts
7--
8-- `posts_published` counts content that is published now, on the month of its
9-- publish date. A post unpublished later drops out of its month, so past months can
10-- shrink slightly; WordPress keeps no publish history to avoid that.
11--
12-- `edits` counts saved revisions in the month, attributed to the revision's author
13-- (the person who saved it), not to the post's author. Autosaves are revisions too
14-- in WordPress and are included.
15
16WITH published AS (
17    SELECT
18        DATE_TRUNC(published_date, MONTH)   AS month_start,
19        post_type,
20        author_id,
21        COUNT(*)                            AS posts_published,
22        SUM(word_count)                     AS words_published
23    FROM {{staging.wordpress.posts}}
24    WHERE post_type IN ('post', 'page')
25      AND post_status = 'publish'
26    GROUP BY 1, 2, 3
27),
28
29edits AS (
30    SELECT
31        DATE_TRUNC(r.published_date, MONTH) AS month_start,
32        p.post_type,
33        r.author_id,
34        COUNT(*)                            AS edits,
35        COUNT(DISTINCT r.parent_id)         AS posts_edited
36    FROM {{staging.wordpress.posts}} r
37    JOIN {{staging.wordpress.posts}} p
38      ON p.post_id = r.parent_id
39    WHERE r.post_type = 'revision'
40      AND p.post_type IN ('post', 'page')