Content inventory

1-- core.wordpress.content_inventory
2-- Every published post and page with its length, age, last update, comments and
3-- featured image: the Posts and Pages lists in wp-admin, as a content audit.
4--
5-- Grain: one row per published post or page (current state as of the last sync).
6-- Depends on: staging.wordpress.posts, staging.wordpress.postmeta
7--
8-- Only post_type 'post' and 'page' with status 'publish' are included. Custom post
9-- types (products, events, case studies) are left out because their names differ
10-- per site: add them to the IN list below. Revisions, attachments, menu items and
11-- auto-drafts are WordPress internals and never belong here.
12--
13-- `revisions` counts saved revisions of the post, which is how often it has been
14-- edited since revisions were enabled. Sites that disable or prune revisions will
15-- show zero.
16
17WITH featured AS (
18    SELECT DISTINCT post_id
19    FROM {{staging.wordpress.postmeta}}
20    WHERE meta_key = '_thumbnail_id'
21      AND NULLIF(meta_value, '') IS NOT NULL
22      AND meta_value != '0'
23),
24
25revisions AS (
26    SELECT
27        parent_id                   AS post_id,
28        COUNT(*)                    AS revisions,
29        MAX(published_at_local)     AS last_revision_at_local
30    FROM {{staging.wordpress.posts}}
31    WHERE post_type = 'revision'
32    GROUP BY parent_id
33)
34
35SELECT
36    p.post_id,
37    p.post_type,
38    p.title,
39    p.slug,
40    p.author_id,