> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getparable.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Basic queries

# Basic Publishing Queries

Simple, powerful queries to get started with your Publishing data. Copy, paste, and customize these for your needs!

## 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 querying.

✅ 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** – returns active records by default

**Do not add these filters manually**—RLS already applies them and redundant predicates can suppress data or hurt performance:

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

Focus on content-specific filters (series, published dates, media metadata) while relying on RLS for tenancy and status.

## Recent Episodes

### Latest Published Episodes

See your most recent content with key details.

```sql theme={null}
-- Get the 20 most recent published episodes
SELECT 
    episode_id,
    title,
    description,
    published_live_at,
    video_url,
    church_center_url
FROM planning_center.publishing_episodes
WHERE published_live_at IS NOT NULL
ORDER BY published_live_at DESC
LIMIT 20;
```

### Episodes by Series

Find all episodes in a specific series.

```sql theme={null}
-- Get all episodes from a series (replace series name)
SELECT 
    e.title as episode_title,
    e.published_live_at,
    e.description,
    s.title as series_title,
    s.started_at as series_start,
    s.ended_at as series_end
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_series s 
    ON e.series_id = s.series_id
WHERE s.title LIKE '%Christmas%'  -- Change series name here
ORDER BY e.published_live_at;
```

### Episodes This Year

All episodes published in the current year.

```sql theme={null}
-- Episodes published this year
SELECT 
    title,
    published_live_at,
    video_url,
    DATE_PART('month', published_live_at) as month,
    DATE_PART('week', published_live_at) as week
FROM planning_center.publishing_episodes
WHERE published_live_at >= DATE_TRUNC('year', CURRENT_DATE)
    AND published_live_at IS NOT NULL
ORDER BY published_live_at DESC;
```

## Series Analytics

### Active Series List

All series with episode counts.

```sql theme={null}
-- List all series with episode counts
SELECT 
    s.series_id,
    s.title,
    s.description,
    s.started_at,
    s.ended_at,
    s.episodes_count,
    s.published,
    COUNT(e.episode_id) as actual_episode_count
FROM planning_center.publishing_series s
LEFT JOIN planning_center.publishing_episodes e 
    ON s.series_id = e.series_id
GROUP BY s.series_id, s.title, s.description, 
         s.started_at, s.ended_at, s.episodes_count, s.published
ORDER BY s.started_at DESC;
```

### Current/Recent Series

Series that are currently active or recently ended.

```sql theme={null}
-- Current and recent series (last 6 months)
SELECT 
    title,
    description,
    started_at,
    ended_at,
    episodes_count,
    CASE 
        WHEN ended_at IS NULL THEN 'Ongoing'
        WHEN ended_at > CURRENT_DATE THEN 'Upcoming'
        ELSE 'Completed'
    END as status
FROM planning_center.publishing_series
WHERE started_at >= CURRENT_DATE - INTERVAL '6 months'
   OR ended_at IS NULL
   OR ended_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY started_at DESC;
```

### Series Duration Analysis

How long do your series typically run?

```sql theme={null}
-- Average series length and episode count
SELECT 
    COUNT(*) as total_series,
    AVG(episodes_count) as avg_episodes_per_series,
    AVG(
        CASE 
            WHEN ended_at IS NOT NULL 
            THEN EXTRACT(DAY FROM (ended_at - started_at))
            ELSE NULL
        END
    ) as avg_series_duration_days,
    MAX(episodes_count) as max_episodes_in_series,
    MIN(episodes_count) as min_episodes_in_series
FROM planning_center.publishing_series
WHERE published = true
    AND started_at IS NOT NULL;
```

## Speaker Insights

### Speaker Frequency

How often does each speaker teach?

```sql theme={null}
-- Speaker frequency in the last year
SELECT 
    sp.speaker_id,
    sp.name as speaker_name,
    COUNT(DISTINCT e.episode_id) as episode_count,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as latest_episode,
    COUNT(DISTINCT e.series_id) as series_count
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
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY sp.speaker_id, sp.name
ORDER BY episode_count DESC;
```

### Recent Speakers

Who has spoken in the last few months?

```sql theme={null}
-- Speakers in the last 3 months
SELECT DISTINCT
    sp.name as speaker_name,
    sp.description,
    COUNT(e.episode_id) as recent_episodes,
    MAX(e.published_live_at) as most_recent_episode
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
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY sp.speaker_id, sp.name, sp.description
ORDER BY most_recent_episode DESC;
```

### Speaker and Series Combination

Which speakers taught which series?

```sql theme={null}
-- Speakers by series
SELECT 
    s.title as series_title,
    sp.name as speaker_name,
    COUNT(e.episode_id) as episodes_in_series,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as last_episode
FROM planning_center.publishing_series s
JOIN planning_center.publishing_episodes e 
    ON s.series_id = e.series_id
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
GROUP BY s.series_id, s.title, sp.speaker_id, sp.name
ORDER BY s.started_at DESC, episodes_in_series DESC;
```

## Content Performance

### Episode View Counts

Track engagement metrics for episodes.

```sql theme={null}
-- Episode performance metrics
SELECT 
    e.title as episode_title,
    e.published_live_at,
    est.view_count,
    est.download_count,
    est.unique_viewers,
    est.average_watch_time / 60 as avg_watch_minutes,
    s.title as series_title
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
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
ORDER BY est.view_count DESC NULLS LAST
LIMIT 25;
```

### Top Performing Content

Your most viewed episodes.

```sql theme={null}
-- Top 10 most viewed episodes of all time
SELECT 
    e.title,
    e.published_live_at,
    est.view_count,
    est.download_count,
    est.unique_viewers,
    s.title as series_title,
    EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) as days_since_published
FROM planning_center.publishing_episodes e
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
WHERE est.view_count IS NOT NULL
ORDER BY est.view_count DESC
LIMIT 10;
```

### Weekly Engagement Trends

How does engagement change over time?

```sql theme={null}
-- Weekly engagement summary
SELECT 
    DATE_TRUNC('week', e.published_live_at) as week,
    COUNT(DISTINCT e.episode_id) as episodes_published,
    SUM(est.view_count) as total_views,
    SUM(est.download_count) as total_downloads,
    AVG(est.view_count) as avg_views_per_episode,
    SUM(est.unique_viewers) as total_unique_viewers
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episode_statistics est 
    ON e.episode_id = est.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY week
ORDER BY week DESC;
```

## Channel Distribution

### Channel Overview

See all your distribution channels.

```sql theme={null}
-- List all channels with settings
SELECT 
    channel_id,
    name,
    kind,
    description,
    streaming_service,
    church_center_enabled,
    default_publish_time,
    time_zone
FROM planning_center.publishing_channels
ORDER BY name;
```

### Episodes by Channel

Content distribution across channels.

```sql theme={null}
-- Episode count by channel
SELECT 
    c.name as channel_name,
    c.kind as channel_type,
    COUNT(DISTINCT e.episode_id) as episode_count,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as latest_episode
FROM planning_center.publishing_channels c
LEFT JOIN planning_center.publishing_episodes e 
    ON c.channel_id = e.channel_id
GROUP BY c.channel_id, c.name, c.kind
ORDER BY episode_count DESC;
```

## Resources and Materials

### Episode Resources

Find downloadable materials for episodes.

```sql theme={null}
-- Resources for recent episodes
SELECT 
    e.title as episode_title,
    e.published_live_at,
    er.name as resource_name,
    er.kind as resource_type,
    er.url,
    er.file_size / 1048576 as size_mb
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episode_resources er 
    ON e.episode_id = er.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY e.published_live_at DESC, er.name;
```

### Resource Types Summary

What types of resources are you providing?

```sql theme={null}
-- Count resources by type
SELECT 
    kind as resource_type,
    COUNT(*) as resource_count,
    COUNT(DISTINCT episode_id) as episodes_with_type,
    SUM(file_size) / 1073741824 as total_size_gb
FROM planning_center.publishing_episode_resources
GROUP BY kind
ORDER BY resource_count DESC;
```

## Publishing Schedule

### Upcoming Publishing Times

When will content be published next?

```sql theme={null}
-- Next scheduled publishing times by channel
SELECT 
    c.name as channel_name,
    cnt.next_publish_at,
    EXTRACT(DAY FROM (cnt.next_publish_at - CURRENT_TIMESTAMP)) as days_until_publish,
    c.default_publish_time
FROM planning_center.publishing_channel_next_times cnt
JOIN planning_center.publishing_channels c 
    ON cnt.channel_id = c.channel_id
WHERE cnt.next_publish_at > CURRENT_TIMESTAMP
ORDER BY cnt.next_publish_at;
```

### Episode Publishing Schedule

When episodes are scheduled to go live.

```sql theme={null}
-- Episode publishing schedule
SELECT 
    e.title as episode_title,
    et.published_at as scheduled_time,
    c.name as channel_name,
    et.status as publish_status,
    e.published_live_at as actual_publish_time
FROM planning_center.publishing_episode_times et
JOIN planning_center.publishing_episodes e 
    ON et.episode_id = e.episode_id
JOIN planning_center.publishing_channels c 
    ON et.channel_id = c.channel_id
WHERE et.published_at >= CURRENT_DATE
ORDER BY et.published_at;
```

## Quick Metrics

### Publishing Summary Dashboard

Key metrics at a glance.

```sql theme={null}
-- Publishing dashboard metrics
SELECT 
    (SELECT COUNT(*) FROM planning_center.publishing_episodes 
     WHERE published_live_at IS NOT NULL) as total_episodes,
    
    (SELECT COUNT(*) FROM planning_center.publishing_series 
     WHERE published = true) as total_series,
    
    (SELECT COUNT(*) FROM planning_center.publishing_speakers) as total_speakers,
    
    (SELECT COUNT(*) FROM planning_center.publishing_channels) as total_channels,
    
    (SELECT SUM(view_count) FROM planning_center.publishing_episode_statistics) as total_views,
    
    (SELECT SUM(download_count) FROM planning_center.publishing_episode_statistics) as total_downloads,
    
    (SELECT COUNT(*) FROM planning_center.publishing_episodes 
     WHERE published_live_at >= CURRENT_DATE - INTERVAL '30 days') as episodes_last_30_days,
    
    (SELECT COUNT(*) FROM planning_center.publishing_episode_resources) as total_resources;
```

### Monthly Publishing Cadence

How consistent is your publishing schedule?

```sql theme={null}
-- Monthly publishing statistics
SELECT 
    DATE_TRUNC('month', published_live_at) as month,
    COUNT(*) as episodes_published,
    COUNT(DISTINCT series_id) as active_series,
    COUNT(DISTINCT DATE_TRUNC('week', published_live_at)) as weeks_with_content,
    ARRAY_AGG(DISTINCT EXTRACT(DOW FROM published_live_at)) as publishing_days
FROM planning_center.publishing_episodes
WHERE published_live_at >= CURRENT_DATE - INTERVAL '12 months'
    AND published_live_at IS NOT NULL
GROUP BY month
ORDER BY month DESC;
```

## Tips for Using These Queries

1. **Customize Date Ranges** - Change INTERVAL values to match your needs
2. **Filter by Channel** - Add `AND channel_id = 'YOUR_CHANNEL_ID'` to focus on specific channels
3. **Add Speaker Filters** - Join with speakerships to analyze specific speakers
4. **Export Results** - Use your SQL client's export feature for reports
5. **Schedule Reports** - Many of these work great as automated weekly/monthly reports

## Next Steps

Ready for more complex analysis? Check out:

* [Advanced Queries](/planning-center/publishing/advanced-queries) - Complex joins and analytics
* [Reporting Examples](/planning-center/publishing/reporting-examples) - Production-ready reports
* [Data Model](/planning-center/publishing/data-model) - Complete table reference

***

*Start simple, build confidence, then explore more complex queries as you grow!*