Mat Views for Dashboard Caching — A Complete Production Example

This lesson builds a complete analytics dashboard backed entirely by materialized views — the architecture used by every serious analytics product.

-- Dashboard mat view 1: platform summary (refreshed every 15 minutes)
DROP MATERIALIZED VIEW IF EXISTS mv_platform_summary;
CREATE MATERIALIZED VIEW mv_platform_summary AS
SELECT
    DATE_TRUNC('hour', NOW())                          AS as_of_hour,
    COUNT(DISTINCT u.id)                                AS total_users,
    COUNT(DISTINCT u.id) FILTER (WHERE u.verified)      AS verified_users,
    COUNT(DISTINCT u.id) FILTER (WHERE u.is_bot)        AS bot_users,
    COUNT(t.id)                                         AS total_tweets,
    COUNT(t.id) FILTER (WHERE t.created_at >= NOW() - INTERVAL '24h') AS tweets_24h,
    SUM(t.like_count)                                   AS total_likes,
    SUM(t.impression_count)                             AS total_impressions,
    ROUND(100.0 * SUM(t.like_count + t.retweet_count)
          / NULLIF(SUM(t.impression_count), 0), 4)      AS platform_engagement_rate
FROM tw_users u
LEFT JOIN tw_tweets t ON t.user_id = u.id
WITH DATA;

-- Dashboard mat view 2: top content (refreshed hourly)
CREATE MATERIALIZED VIEW mv_top_content AS
SELECT
    t.id,
    u.username,
    u.verified,
    LEFT(t.content, 100)                               AS preview,
    t.like_count,
    t.retweet_count,
    t.impression_count,
    t.created_at,
    RANK() OVER (ORDER BY t.like_count DESC)            AS likes_rank,
    RANK() OVER (ORDER BY t.retweet_count DESC)         AS retweets_rank
FROM tw_tweets t
JOIN tw_users u ON u.id = t.user_id
WHERE t.created_at >= NOW() - INTERVAL '7 days'
  AND t.retweet_of_id IS NULL
WITH DATA;

CREATE UNIQUE INDEX ON mv_top_content (id);
CREATE INDEX ON mv_top_content (likes_rank);