Advanced Publishing Queries

Complex queries for deep analysis, cross-module insights, and sophisticated reporting. These queries demonstrate advanced SQL techniques and integration patterns.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Publishing module live in the planning_center schema. Always prefix table names with planning_center. when writing advanced queries. ✅ CORRECT: SELECT * FROM planning_center.publishing_episodes ❌ INCORRECT: SELECT * FROM publishing_episodes

Row Level Security (RLS)

Row Level Security automatically governs:

tenant_organization_id – restricts results to your organization
system_status – active records returned by default

Do not add these filters manually—RLS already enforces them and redundant predicates can hide data or slow execution:

❌ WHERE tenant_organization_id = 1
❌ WHERE system_status = 'active'

Focus on media analytics, engagement, and cross-module joins while trusting RLS for tenancy and system status.

Time-Based Analytics

Engagement Decay Analysis

How quickly do views drop off after publishing?

-- Analyze view decay over time for episodes
WITH episode_age_metrics AS (
    SELECT 
        e.episode_id,
        e.title,
        e.published_live_at,
        est.view_count,
        est.created_at as stat_date,
        EXTRACT(DAY FROM (est.created_at - e.published_live_at)) as days_after_publish,
        EXTRACT(WEEK FROM (est.created_at - e.published_live_at)) as weeks_after_publish
    FROM planning_center.publishing_episodes e
    JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '6 months'
        AND est.created_at >= e.published_live_at
)
SELECT 
    weeks_after_publish,
    COUNT(DISTINCT episode_id) as episodes_measured,
    AVG(view_count) as avg_views,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY view_count) as median_views,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY view_count) as q1_views,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY view_count) as q3_views,
    SUM(view_count) as total_views
FROM episode_age_metrics
WHERE weeks_after_publish <= 12
GROUP BY weeks_after_publish
ORDER BY weeks_after_publish;

Year-over-Year Growth Analysis

Compare publishing metrics across years.

-- Year-over-year publishing comparison
WITH yearly_metrics AS (
    SELECT 
        DATE_PART('year', e.published_live_at) as year,
        DATE_PART('month', e.published_live_at) as month,
        COUNT(DISTINCT e.episode_id) as episodes_published,
        COUNT(DISTINCT e.series_id) as active_series,
        COUNT(DISTINCT sp.speaker_id) as unique_speakers,
        SUM(est.view_count) as total_views,
        SUM(est.download_count) as total_downloads,
        AVG(est.view_count) as avg_views_per_episode
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_speakerships ship 
        ON e.episode_id = ship.episode_id
    LEFT JOIN planning_center.publishing_speakers sp 
        ON ship.speaker_id = sp.speaker_id
    WHERE e.published_live_at >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '2 years')
    GROUP BY year, month
)
SELECT 
    ym.month,
    ym.episodes_published as current_year_episodes,
    ym_prev.episodes_published as last_year_episodes,
    ROUND(((ym.episodes_published::numeric - ym_prev.episodes_published) / 
           NULLIF(ym_prev.episodes_published, 0)) * 100, 2) as episode_growth_pct,
    ym.total_views as current_year_views,
    ym_prev.total_views as last_year_views,
    ROUND(((ym.total_views::numeric - ym_prev.total_views) / 
           NULLIF(ym_prev.total_views, 0)) * 100, 2) as view_growth_pct,
    ym.avg_views_per_episode as current_avg_views,
    ym_prev.avg_views_per_episode as last_year_avg_views
FROM yearly_metrics ym
LEFT JOIN yearly_metrics ym_prev 
    ON ym.month = ym_prev.month 
    AND ym.year = ym_prev.year + 1
WHERE ym.year = DATE_PART('year', CURRENT_DATE)
ORDER BY ym.month;

Publishing Consistency Score

Measure how consistently you publish content.

-- Publishing consistency analysis
WITH weekly_publishing AS (
    SELECT 
        DATE_TRUNC('week', published_live_at) as week,
        COUNT(*) as episodes_published,
        ARRAY_AGG(DISTINCT EXTRACT(DOW FROM published_live_at)) as publishing_days,
        ARRAY_AGG(title ORDER BY published_live_at) as episode_titles
    FROM planning_center.publishing_episodes
    WHERE published_live_at >= CURRENT_DATE - INTERVAL '52 weeks'
        AND published_live_at IS NOT NULL
    GROUP BY week
),
consistency_metrics AS (
    SELECT 
        COUNT(*) as total_weeks,
        COUNT(CASE WHEN episodes_published > 0 THEN 1 END) as weeks_with_content,
        AVG(episodes_published) as avg_episodes_per_week,
        STDDEV(episodes_published) as stddev_episodes,
        MODE() WITHIN GROUP (ORDER BY episodes_published) as mode_episodes_per_week,
        MAX(episodes_published) as max_episodes_in_week,
        MIN(CASE WHEN episodes_published > 0 THEN episodes_published END) as min_episodes_in_week
    FROM weekly_publishing
)
SELECT 
    total_weeks,
    weeks_with_content,
    ROUND((weeks_with_content::numeric / total_weeks) * 100, 2) as consistency_percentage,
    ROUND(avg_episodes_per_week::numeric, 2) as avg_episodes_per_week,
    ROUND(stddev_episodes::numeric, 2) as publishing_variance,
    mode_episodes_per_week as typical_weekly_episodes,
    max_episodes_in_week,
    min_episodes_in_week,
    CASE 
        WHEN (weeks_with_content::numeric / total_weeks) >= 0.95 THEN 'Excellent'
        WHEN (weeks_with_content::numeric / total_weeks) >= 0.85 THEN 'Good'
        WHEN (weeks_with_content::numeric / total_weeks) >= 0.70 THEN 'Fair'
        ELSE 'Needs Improvement'
    END as consistency_rating
FROM consistency_metrics;

Speaker Analytics

Speaker Collaboration Patterns

Which speakers frequently teach together?

-- Find speaker collaboration patterns
WITH episode_speakers AS (
    SELECT 
        e.episode_id,
        e.title as episode_title,
        e.published_live_at,
        ARRAY_AGG(sp.name ORDER BY sp.name) as speakers,
        ARRAY_AGG(sp.speaker_id ORDER BY sp.speaker_id) as speaker_ids,
        COUNT(sp.speaker_id) as speaker_count
    FROM planning_center.publishing_episodes e
    JOIN planning_center.publishing_speakerships ship 
        ON e.episode_id = ship.episode_id
    JOIN planning_center.publishing_speakers sp 
        ON ship.speaker_id = sp.speaker_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY e.episode_id, e.title, e.published_live_at
    HAVING COUNT(sp.speaker_id) > 1
),
speaker_pairs AS (
    SELECT 
        s1.speaker_id as speaker1_id,
        s1.name as speaker1_name,
        s2.speaker_id as speaker2_id,
        s2.name as speaker2_name,
        COUNT(DISTINCT e.episode_id) as episodes_together
    FROM planning_center.publishing_episodes e
    JOIN planning_center.publishing_speakerships ship1 
        ON e.episode_id = ship1.episode_id
    JOIN planning_center.publishing_speakers s1 
        ON ship1.speaker_id = s1.speaker_id
    JOIN planning_center.publishing_speakerships ship2 
        ON e.episode_id = ship2.episode_id
    JOIN planning_center.publishing_speakers s2 
        ON ship2.speaker_id = s2.speaker_id
    WHERE s1.speaker_id < s2.speaker_id  -- Avoid duplicates
        AND e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY s1.speaker_id, s1.name, s2.speaker_id, s2.name
)
SELECT 
    speaker1_name,
    speaker2_name,
    episodes_together,
    ROUND(episodes_together::numeric / 52 * 100, 1) as pct_of_year_together
FROM speaker_pairs
ORDER BY episodes_together DESC;

Speaker Topic Analysis

What topics does each speaker cover? (Based on series)

-- Analyze speaker topics through series
WITH speaker_series_stats AS (
    SELECT 
        sp.speaker_id,
        sp.name as speaker_name,
        s.series_id,
        s.title as series_title,
        s.description as series_description,
        COUNT(DISTINCT e.episode_id) as episodes_in_series,
        MIN(e.published_live_at) as first_episode,
        MAX(e.published_live_at) as last_episode,
        SUM(est.view_count) as total_views,
        AVG(est.view_count) as avg_views
    FROM planning_center.publishing_speakers sp
    JOIN planning_center.publishing_speakerships ship 
        ON sp.speaker_id = ship.speaker_id
    JOIN planning_center.publishing_episodes e 
        ON ship.episode_id = e.episode_id
    JOIN planning_center.publishing_series s 
        ON e.series_id = s.series_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    GROUP BY sp.speaker_id, sp.name, s.series_id, s.title, s.description
)
SELECT 
    speaker_name,
    COUNT(DISTINCT series_id) as series_count,
    SUM(episodes_in_series) as total_episodes,
    ARRAY_AGG(series_title ORDER BY total_views DESC) as series_taught,
    ROUND(AVG(avg_views)::numeric, 0) as avg_views_per_episode,
    SUM(total_views) as total_career_views,
    MIN(first_episode) as teaching_since,
    MAX(last_episode) as most_recent_teaching
FROM speaker_series_stats
GROUP BY speaker_id, speaker_name
ORDER BY total_episodes DESC;

Speaker Performance Benchmarking

Compare speaker engagement metrics.

-- Speaker performance benchmarking
WITH speaker_metrics AS (
    SELECT 
        sp.speaker_id,
        sp.name as speaker_name,
        COUNT(DISTINCT e.episode_id) as episode_count,
        AVG(est.view_count) as avg_views,
        AVG(est.download_count) as avg_downloads,
        AVG(est.unique_viewers) as avg_unique_viewers,
        AVG(est.average_watch_time) / 60 as avg_watch_minutes,
        STDDEV(est.view_count) as view_stddev,
        MAX(est.view_count) as max_views,
        MIN(est.view_count) as min_views
    FROM planning_center.publishing_speakers sp
    JOIN planning_center.publishing_speakerships ship 
        ON sp.speaker_id = ship.speaker_id
    JOIN planning_center.publishing_episodes e 
        ON ship.episode_id = e.episode_id
    JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY sp.speaker_id, sp.name
    HAVING COUNT(DISTINCT e.episode_id) >= 3  -- Minimum episodes for comparison
),
benchmarks AS (
    SELECT 
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_views) as median_views,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY avg_views) as q3_views,
        AVG(avg_views) as overall_avg_views
    FROM speaker_metrics
)
SELECT 
    sm.speaker_name,
    sm.episode_count,
    ROUND(sm.avg_views::numeric, 0) as avg_views,
    ROUND(sm.avg_downloads::numeric, 0) as avg_downloads,
    ROUND(sm.avg_watch_minutes::numeric, 1) as avg_watch_minutes,
    ROUND(sm.view_stddev::numeric, 0) as view_consistency,
    CASE 
        WHEN sm.avg_views > b.q3_views THEN 'Top Performer'
        WHEN sm.avg_views > b.median_views THEN 'Above Average'
        ELSE 'Below Average'
    END as performance_tier,
    ROUND(((sm.avg_views - b.overall_avg_views) / b.overall_avg_views) * 100, 1) as pct_vs_average
FROM speaker_metrics sm
CROSS JOIN benchmarks b
ORDER BY sm.avg_views DESC;

Series Deep Dive

Series Performance Trajectory

How do views change throughout a series?

-- Analyze view trajectory within series
WITH series_episodes AS (
    SELECT 
        s.series_id,
        s.title as series_title,
        e.episode_id,
        e.title as episode_title,
        e.published_live_at,
        est.view_count,
        ROW_NUMBER() OVER (PARTITION BY s.series_id ORDER BY e.published_live_at) as episode_number,
        COUNT(*) OVER (PARTITION BY s.series_id) as total_episodes_in_series,
        FIRST_VALUE(est.view_count) OVER (PARTITION BY s.series_id ORDER BY e.published_live_at) as first_episode_views,
        LAST_VALUE(est.view_count) OVER (PARTITION BY s.series_id ORDER BY e.published_live_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_episode_views
    FROM planning_center.publishing_series s
    JOIN planning_center.publishing_episodes e 
        ON s.series_id = e.series_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE s.episodes_count >= 4  -- Series with at least 4 episodes
        AND e.published_live_at IS NOT NULL
)
SELECT 
    series_title,
    total_episodes_in_series,
    first_episode_views,
    last_episode_views,
    ROUND(AVG(CASE WHEN episode_number = 1 THEN view_count END)::numeric, 0) as ep1_avg_views,
    ROUND(AVG(CASE WHEN episode_number = 2 THEN view_count END)::numeric, 0) as ep2_avg_views,
    ROUND(AVG(CASE WHEN episode_number = 3 THEN view_count END)::numeric, 0) as ep3_avg_views,
    ROUND(AVG(CASE WHEN episode_number = total_episodes_in_series THEN view_count END)::numeric, 0) as final_ep_avg_views,
    ROUND(((last_episode_views::numeric - first_episode_views) / 
           NULLIF(first_episode_views, 0)) * 100, 1) as view_change_pct,
    CASE 
        WHEN last_episode_views > first_episode_views * 1.1 THEN 'Growing Engagement'
        WHEN last_episode_views < first_episode_views * 0.9 THEN 'Declining Engagement'
        ELSE 'Stable Engagement'
    END as engagement_trend
FROM series_episodes
GROUP BY series_id, series_title, total_episodes_in_series, first_episode_views, last_episode_views
ORDER BY total_episodes_in_series DESC, series_title;

Optimal Series Length Analysis

What’s the ideal number of episodes for a series?

-- Analyze engagement by series length
WITH series_performance AS (
    SELECT 
        s.series_id,
        s.title,
        s.episodes_count,
        COUNT(DISTINCT e.episode_id) as actual_episodes,
        AVG(est.view_count) as avg_views_per_episode,
        SUM(est.view_count) as total_series_views,
        AVG(est.unique_viewers) as avg_unique_viewers,
        STDDEV(est.view_count) as view_variance,
        EXTRACT(DAY FROM (s.ended_at - s.started_at)) as series_duration_days
    FROM planning_center.publishing_series s
    JOIN planning_center.publishing_episodes e 
        ON s.series_id = e.series_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE s.published = true
        AND s.ended_at IS NOT NULL
    GROUP BY s.series_id, s.title, s.episodes_count, s.started_at, s.ended_at
),
length_buckets AS (
    SELECT 
        CASE 
            WHEN episodes_count <= 2 THEN '1-2 Episodes'
            WHEN episodes_count <= 4 THEN '3-4 Episodes'
            WHEN episodes_count <= 6 THEN '5-6 Episodes'
            WHEN episodes_count <= 8 THEN '7-8 Episodes'
            ELSE '9+ Episodes'
        END as series_length_bucket,
        episodes_count,
        COUNT(*) as series_count,
        AVG(avg_views_per_episode) as avg_views,
        AVG(total_series_views) as avg_total_views,
        AVG(avg_unique_viewers) as avg_unique_viewers,
        AVG(view_variance) as avg_view_variance,
        AVG(series_duration_days) as avg_duration_days
    FROM series_performance
    GROUP BY series_length_bucket, episodes_count
)
SELECT 
    series_length_bucket,
    series_count,
    ROUND(avg_views::numeric, 0) as avg_views_per_episode,
    ROUND(avg_total_views::numeric, 0) as avg_total_series_views,
    ROUND(avg_unique_viewers::numeric, 0) as avg_unique_viewers,
    ROUND(avg_view_variance::numeric, 0) as view_consistency_score,
    ROUND(avg_duration_days::numeric, 0) as avg_series_days,
    RANK() OVER (ORDER BY avg_views DESC) as performance_rank
FROM length_buckets
ORDER BY series_length_bucket;

Resource and Distribution Analysis

Multi-Channel Performance Comparison

How does content perform across different channels?

-- Compare performance across distribution channels
WITH channel_episodes AS (
    SELECT 
        c.channel_id,
        c.name as channel_name,
        c.kind as channel_type,
        e.episode_id,
        e.title as episode_title,
        e.published_live_at,
        et.published_at as channel_publish_time,
        est.view_count,
        est.download_count,
        est.unique_viewers
    FROM planning_center.publishing_channels c
    JOIN planning_center.publishing_episode_times et 
        ON c.channel_id = et.channel_id
    JOIN planning_center.publishing_episodes e 
        ON et.episode_id = e.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id 
        AND c.channel_id = est.channel_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '6 months'
)
SELECT 
    channel_name,
    channel_type,
    COUNT(DISTINCT episode_id) as episodes_published,
    AVG(view_count) as avg_views,
    SUM(view_count) as total_views,
    AVG(download_count) as avg_downloads,
    AVG(unique_viewers) as avg_unique_viewers,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY view_count) as median_views,
    MAX(view_count) as best_performing_episode_views,
    MIN(view_count) as worst_performing_episode_views,
    AVG(EXTRACT(HOUR FROM (channel_publish_time - published_live_at))) as avg_delay_hours
FROM channel_episodes
GROUP BY channel_id, channel_name, channel_type
ORDER BY total_views DESC;

Resource Utilization Analysis

Which resources are most popular?

-- Analyze resource downloads and usage
WITH resource_metrics AS (
    SELECT 
        er.kind as resource_type,
        er.name as resource_name,
        e.title as episode_title,
        s.title as series_title,
        er.file_size,
        er.url,
        e.published_live_at,
        est.view_count as episode_views,
        CASE 
            WHEN er.kind ILIKE '%note%' THEN 'Notes'
            WHEN er.kind ILIKE '%slide%' OR er.kind ILIKE '%presentation%' THEN 'Slides'
            WHEN er.kind ILIKE '%guide%' OR er.kind ILIKE '%study%' THEN 'Study Materials'
            WHEN er.kind ILIKE '%video%' THEN 'Video'
            WHEN er.kind ILIKE '%audio%' THEN 'Audio'
            ELSE 'Other'
        END as resource_category
    FROM planning_center.publishing_episode_resources er
    JOIN planning_center.publishing_episodes e 
        ON er.episode_id = e.episode_id
    LEFT JOIN planning_center.publishing_series s 
        ON e.series_id = s.series_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
)
SELECT 
    resource_category,
    COUNT(*) as resource_count,
    COUNT(DISTINCT episode_title) as episodes_with_resource,
    SUM(file_size) / 1073741824.0 as total_size_gb,
    AVG(file_size) / 1048576.0 as avg_size_mb,
    AVG(episode_views) as avg_episode_views_with_resource,
    ARRAY_AGG(DISTINCT resource_type) as resource_types
FROM resource_metrics
GROUP BY resource_category
ORDER BY resource_count DESC;

Cross-Module Integration

Publishing and People Integration

Connect online viewers with church members.

-- Analyze viewer demographics (requires People module)
-- This query assumes you have a way to link viewers to people records
WITH viewer_demographics AS (
    SELECT 
        e.episode_id,
        e.title as episode_title,
        s.title as series_title,
        est.unique_viewers,
        -- Join with people data (pseudo-code - adjust based on your setup)
        COUNT(DISTINCT p.person_id) as identified_viewers,
        COUNT(DISTINCT CASE WHEN p.membership = 'Member' THEN p.person_id END) as member_viewers,
        COUNT(DISTINCT CASE WHEN p.membership = 'Regular Attender' THEN p.person_id END) as attender_viewers,
        COUNT(DISTINCT CASE WHEN p.campus_id IS NOT NULL THEN p.person_id END) as campus_connected_viewers
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_series s 
        ON e.series_id = s.series_id
    -- This is a conceptual join - adjust based on your tracking setup
    LEFT JOIN planning_center.people_people p 
        ON true -- Replace with actual join condition
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY e.episode_id, e.title, s.title, est.unique_viewers
)
SELECT 
    series_title,
    COUNT(DISTINCT episode_id) as episode_count,
    SUM(unique_viewers) as total_unique_viewers,
    SUM(identified_viewers) as total_identified_viewers,
    ROUND((SUM(identified_viewers)::numeric / NULLIF(SUM(unique_viewers), 0)) * 100, 2) as viewer_identification_rate,
    SUM(member_viewers) as total_member_viewers,
    SUM(attender_viewers) as total_attender_viewers,
    ROUND((SUM(member_viewers)::numeric / NULLIF(SUM(identified_viewers), 0)) * 100, 2) as member_percentage
FROM viewer_demographics
GROUP BY series_title
ORDER BY total_unique_viewers DESC;

Publishing and Giving Correlation

Analyze giving patterns during sermon series.

-- Correlate sermon series with giving patterns (requires Giving module)
WITH series_giving_periods AS (
    SELECT 
        s.series_id,
        s.title as series_title,
        s.started_at,
        s.ended_at,
        COUNT(DISTINCT e.episode_id) as episode_count,
        AVG(est.view_count) as avg_episode_views
    FROM planning_center.publishing_series s
    JOIN planning_center.publishing_episodes e 
        ON s.series_id = e.series_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE s.started_at >= CURRENT_DATE - INTERVAL '1 year'
        AND s.ended_at IS NOT NULL
    GROUP BY s.series_id, s.title, s.started_at, s.ended_at
),
giving_during_series AS (
    SELECT 
        sgp.series_id,
        sgp.series_title,
        sgp.started_at,
        sgp.ended_at,
        sgp.episode_count,
        sgp.avg_episode_views,
        -- Join with giving data (adjust based on your schema)
        COUNT(DISTINCT d.donation_id) as donations_during_series,
        SUM(d.amount_cents) / 100.0 as total_giving_amount,
        COUNT(DISTINCT d.person_id) as unique_donors,
        AVG(d.amount_cents) / 100.0 as avg_donation_amount
    FROM series_giving_periods sgp
    -- Conceptual join with giving data
    LEFT JOIN planning_center.giving_donations d 
        ON d.received_at BETWEEN sgp.started_at AND COALESCE(sgp.ended_at, CURRENT_DATE)
    GROUP BY sgp.series_id, sgp.series_title, sgp.started_at, sgp.ended_at, 
             sgp.episode_count, sgp.avg_episode_views
)
SELECT 
    series_title,
    started_at,
    ended_at,
    episode_count,
    ROUND(avg_episode_views::numeric, 0) as avg_views,
    donations_during_series,
    ROUND(total_giving_amount::numeric, 2) as total_giving,
    unique_donors,
    ROUND(avg_donation_amount::numeric, 2) as avg_donation,
    ROUND((total_giving_amount / NULLIF(episode_count, 0))::numeric, 2) as giving_per_episode
FROM giving_during_series
ORDER BY started_at DESC;

Performance Optimization Queries

Identify Slow-Performing Content

Find content that needs promotion or improvement.

-- Identify underperforming content for optimization
WITH performance_benchmarks AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY est.view_count) as q1_views,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY est.view_count) as median_views,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY est.view_count) as q3_views,
        AVG(est.view_count) as avg_views
    FROM planning_center.publishing_episodes e
    JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
),
episode_performance AS (
    SELECT 
        e.episode_id,
        e.title,
        e.published_live_at,
        s.title as series_title,
        sp.name as speaker_name,
        est.view_count,
        est.download_count,
        est.unique_viewers,
        er.resource_count,
        EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) as days_since_published
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_series s 
        ON e.series_id = s.series_id
    LEFT JOIN planning_center.publishing_speakerships ship 
        ON e.episode_id = ship.episode_id
    LEFT JOIN planning_center.publishing_speakers sp 
        ON ship.speaker_id = sp.speaker_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN (
        SELECT episode_id, COUNT(*) as resource_count
        FROM planning_center.publishing_episode_resources
        GROUP BY episode_id
    ) er ON e.episode_id = er.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
)
SELECT 
    ep.title,
    ep.series_title,
    ep.speaker_name,
    ep.published_live_at,
    ep.days_since_published,
    ep.view_count,
    pb.median_views as expected_views,
    ROUND(((ep.view_count::numeric - pb.median_views) / pb.median_views) * 100, 1) as performance_vs_median_pct,
    CASE 
        WHEN ep.view_count < pb.q1_views THEN 'Critical - Bottom 25%'
        WHEN ep.view_count < pb.median_views THEN 'Below Average'
        WHEN ep.view_count < pb.q3_views THEN 'Above Average'
        ELSE 'Top Performer'
    END as performance_tier,
    COALESCE(ep.resource_count, 0) as resources_available,
    CASE 
        WHEN ep.view_count < pb.q1_views AND ep.resource_count = 0 THEN 'Add Resources'
        WHEN ep.view_count < pb.q1_views AND ep.days_since_published < 7 THEN 'Needs Promotion'
        WHEN ep.view_count < pb.q1_views THEN 'Review Content Quality'
        ELSE 'No Action Needed'
    END as recommended_action
FROM episode_performance ep
CROSS JOIN performance_benchmarks pb
WHERE ep.view_count < pb.median_views
ORDER BY ep.view_count ASC, ep.published_live_at DESC;

Tips for Advanced Queries

Use CTEs (WITH clauses) for complex multi-step analysis
Window Functions for running totals and rankings
PERCENTILE_CONT for statistical analysis
ARRAY_AGG to collect related values
CASE statements for conditional logic and categorization
Cross-module joins require understanding your data relationships

Performance Considerations

Add indexes on frequently joined columns
Use EXPLAIN ANALYZE to optimize slow queries
Consider materialized views for complex reports
Partition large tables by date if needed

Next Steps

Reporting Examples - Production-ready report templates
Data Model - Complete schema reference
Basic Queries - Simpler query examples

Advanced analysis leads to advanced insights. Use these patterns to unlock the full potential of your publishing data.

​Advanced Publishing Queries

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Time-Based Analytics

​Engagement Decay Analysis

​Year-over-Year Growth Analysis

​Publishing Consistency Score

​Speaker Analytics

​Speaker Collaboration Patterns

​Speaker Topic Analysis

​Speaker Performance Benchmarking

​Series Deep Dive

​Series Performance Trajectory

​Optimal Series Length Analysis

​Resource and Distribution Analysis

​Multi-Channel Performance Comparison

​Resource Utilization Analysis

​Cross-Module Integration

​Publishing and People Integration

​Publishing and Giving Correlation

​Performance Optimization Queries

​Identify Slow-Performing Content

​Tips for Advanced Queries

​Performance Considerations

​Next Steps