Materialized View Chains — Multi-Layer Aggregation Pipelines

A materialized view can be built on top of another materialized view. This creates a pipeline: raw data → hourly rollup → daily rollup → weekly rollup. Each layer is cheaper to query and easier to reason about.

DROP MATERIALIZED VIEW IF EXISTS mv_weekly_tweets, mv_daily_tweets, mv_hourly_tweets CASCADE;

-- Layer 1: hourly tweet stats (refreshed every hour)
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_hourly_tweets AS
SELECT
    user_id,
    DATE_TRUNC('hour', created_at)     AS hour_bucket,
    COUNT(*)                              AS tweets,
    SUM(like_count)                       AS likes,
    SUM(impression_count)                 AS impressions
FROM tw_tweets
GROUP BY user_id, DATE_TRUNC('hour', created_at)
WITH DATA;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mvht_user_hour ON mv_hourly_tweets (user_id, hour_bucket);

-- Layer 2: daily stats built FROM the hourly mat view (refreshed daily)
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_daily_tweets AS
SELECT
    user_id,
    hour_bucket::DATE                    AS day,
    SUM(tweets)                          AS tweets,
    SUM(likes)                           AS likes,
    SUM(impressions)                     AS impressions,
    ROUND(100.0 * SUM(likes)
          / NULLIF(SUM(impressions), 0), 4) AS engagement_rate
FROM mv_hourly_tweets   -- reads from Layer 1
GROUP BY user_id, hour_bucket::DATE
WITH DATA;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mvdt_user_day ON mv_daily_tweets (user_id, day);

-- Layer 3: weekly summary built FROM the daily mat view (refreshed weekly)
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_weekly_tweets AS
SELECT
    user_id,
    DATE_TRUNC('week', day)::DATE       AS week_start,
    SUM(tweets)                           AS tweets,
    SUM(likes)                            AS likes,
    ROUND(AVG(engagement_rate), 4)        AS avg_daily_engagement
FROM mv_daily_tweets    -- reads from Layer 2
GROUP BY user_id, DATE_TRUNC('week', day)
WITH DATA;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mvwt_user_week ON mv_weekly_tweets (user_id, week_start);