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

# Advanced queries

# Advanced Giving Queries

Ready to unlock deeper insights from your giving data? These advanced queries will help you perform complex analysis, track trends, and generate sophisticated reports.

<Card title="Query Customization Required" icon="circle-exclamation" color="#FFA500" horizontal>
  These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.
</Card>

## Query Requirements

### Schema Prefix

**IMPORTANT:** All tables in the Planning Center Giving module live in the `planning_center` schema. Always prefix table names with `planning_center.` in your queries.

✅ CORRECT: `SELECT * FROM planning_center.giving_donations`
❌ INCORRECT: `SELECT * FROM giving_donations`

### Row Level Security (RLS)

Row Level Security automatically scopes results by:

* **tenant\_organization\_id** – only data from 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'`

Keep your attention on domain-specific filters (date ranges, refunded flags, fund logic) while RLS handles tenancy and system status.

## Table of Contents

* [Complex Joins and Relationships](#complex-joins-and-relationships)
* [Time-Based Analysis](#time-based-analysis)
* [Donor Segmentation](#donor-segmentation)
* [Recurring Giving Analysis](#recurring-giving-analysis)
* [Pledge Campaign Tracking](#pledge-campaign-tracking)
* [Window Functions and Rankings](#window-functions-and-rankings)
* [Performance Optimization](#performance-optimization)

## Complex Joins and Relationships

### Complete Donation Details with All Relationships

This query brings together donations, donors, funds, batches, and campuses:

```sql theme={null}
-- Comprehensive donation view with all related data
WITH donation_details AS (
    SELECT 
        d.donation_id,
        d.amount_cents / 100.0 as donation_amount,
        d.payment_method,
        d.received_at,
        d.fee_cents / 100.0 as fee_amount,
        d.refunded,
        -- Get person details
        p.person_id,
        p.first_name,
        p.last_name,
        p.donor_number,
        -- Get batch details
        b.batch_id,
        b.description as batch_description,
        b.total_cents / 100.0 as batch_total,
        -- Get campus details
        c.campus_id,
        c.name as campus_name
    FROM planning_center.giving_donations d
    -- Join to person
    LEFT JOIN planning_center.giving_donation_relationships dr_person
        ON d.donation_id = dr_person.donation_id
        AND dr_person.relationship_type = 'Person'
    LEFT JOIN planning_center.giving_people p
        ON dr_person.relationship_id = p.person_id
    -- Join to batch
    LEFT JOIN planning_center.giving_donation_relationships dr_batch
        ON d.donation_id = dr_batch.donation_id
        AND dr_batch.relationship_type = 'Batch'
    LEFT JOIN planning_center.giving_batches b
        ON dr_batch.relationship_id = b.batch_id
    -- Join to campus
    LEFT JOIN planning_center.giving_donation_relationships dr_campus
        ON d.donation_id = dr_campus.donation_id
        AND dr_campus.relationship_type = 'Campus'
    LEFT JOIN planning_center.giving_campuses c
        ON dr_campus.relationship_id = c.campus_id
)
SELECT * FROM donation_details
WHERE received_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY received_at DESC;
```

### Multi-Fund Donations Analysis

Find donations that were split across multiple funds:

```sql theme={null}
-- Donations split across multiple funds
WITH donation_fund_counts AS (
    SELECT 
        d.donation_id,
        d.amount_cents / 100.0 as total_amount,
        d.received_at,
        COUNT(DISTINCT des.fund_id) as num_funds,
        STRING_AGG(f.name, ', ' ORDER BY des.amount_cents DESC) as fund_names
    FROM planning_center.giving_donations d
    JOIN planning_center.giving_designation_relationships dr
        ON d.donation_id = dr.designation_id
        AND dr.relationship_type = 'Donation'
    JOIN planning_center.giving_designations des
        ON dr.relationship_id = des.designation_id
    JOIN planning_center.giving_funds f
        ON des.fund_id = f.fund_id
    WHERE d.received_at >= CURRENT_DATE - INTERVAL '90 days'
        AND d.refunded = false
    GROUP BY d.donation_id, d.amount_cents, d.received_at
)
SELECT *
FROM donation_fund_counts
WHERE num_funds > 1  -- Only multi-fund donations
ORDER BY total_amount DESC;
```

## Time-Based Analysis

### Year-Over-Year Comparison by Month

```sql theme={null}
-- Compare giving by month across multiple years
WITH monthly_giving AS (
    SELECT 
        DATE_TRUNC('month', received_at) as month,
        EXTRACT(YEAR FROM received_at) as year,
        EXTRACT(MONTH FROM received_at) as month_num,
        TO_CHAR(received_at, 'Month') as month_name,
        COUNT(*) as donation_count,
        COUNT(DISTINCT dr.relationship_id) as unique_donors,
        SUM(d.amount_cents) / 100.0 as total_amount
    FROM planning_center.giving_donations d
    LEFT JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.refunded = false
        AND d.received_at >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '2 years')
    GROUP BY DATE_TRUNC('month', received_at), 
             EXTRACT(YEAR FROM received_at),
             EXTRACT(MONTH FROM received_at),
             TO_CHAR(received_at, 'Month')
)
SELECT 
    month_name,
    month_num,
    MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) - 2 THEN total_amount END) as two_years_ago,
    MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN total_amount END) as last_year,
    MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) THEN total_amount END) as this_year,
    -- Calculate year-over-year growth
    ROUND(
        ((MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) THEN total_amount END) / 
          NULLIF(MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN total_amount END), 0)) - 1) * 100, 
        2
    ) as yoy_growth_percent
FROM monthly_giving
GROUP BY month_name, month_num
ORDER BY month_num;
```

### Rolling 12-Month Trends

```sql theme={null}
-- Calculate 12-month rolling average
WITH monthly_totals AS (
    SELECT 
        DATE_TRUNC('month', received_at) as month,
        SUM(amount_cents) / 100.0 as monthly_total,
        COUNT(*) as donation_count
    FROM planning_center.giving_donations
    WHERE refunded = false
        AND received_at >= CURRENT_DATE - INTERVAL '24 months'
    GROUP BY DATE_TRUNC('month', received_at)
),
rolling_averages AS (
    SELECT 
        month,
        monthly_total,
        donation_count,
        AVG(monthly_total) OVER (
            ORDER BY month 
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) as rolling_12m_avg,
        SUM(monthly_total) OVER (
            ORDER BY month 
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) as rolling_12m_total
    FROM monthly_totals
)
SELECT 
    month,
    monthly_total,
    rolling_12m_avg,
    rolling_12m_total,
    ROUND(((monthly_total / NULLIF(rolling_12m_avg, 0)) - 1) * 100, 2) as pct_vs_12m_avg
FROM rolling_averages
WHERE month >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY month DESC;
```

## Donor Segmentation

### Donor Lifecycle Analysis

Categorize donors by their giving patterns:

```sql theme={null}
-- Segment donors by giving frequency and recency
WITH donor_metrics AS (
    SELECT 
        p.person_id,
        p.first_name,
        p.last_name,
        COUNT(DISTINCT d.donation_id) as total_donations,
        SUM(d.amount_cents) / 100.0 as lifetime_giving,
        MIN(d.received_at) as first_donation_date,
        MAX(d.received_at) as last_donation_date,
        CURRENT_DATE - MAX(d.received_at)::date as days_since_last_donation,
        COUNT(DISTINCT DATE_TRUNC('month', d.received_at)) as months_given
    FROM planning_center.giving_people p
    JOIN planning_center.giving_donation_relationships dr
        ON p.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE d.refunded = false
    GROUP BY p.person_id, p.first_name, p.last_name
),
donor_segments AS (
    SELECT 
        *,
        CASE 
            WHEN days_since_last_donation <= 90 AND months_given >= 10 THEN 'Champion'
            WHEN days_since_last_donation <= 90 AND months_given >= 6 THEN 'Loyal'
            WHEN days_since_last_donation <= 90 AND months_given >= 3 THEN 'Developing'
            WHEN days_since_last_donation <= 90 THEN 'New'
            WHEN days_since_last_donation <= 180 THEN 'At Risk'
            WHEN days_since_last_donation <= 365 THEN 'Lapsed'
            ELSE 'Lost'
        END as donor_segment,
        CASE 
            WHEN lifetime_giving >= 10000 THEN 'Major'
            WHEN lifetime_giving >= 5000 THEN 'Mid-Level'
            WHEN lifetime_giving >= 1000 THEN 'Regular'
            ELSE 'Small'
        END as giving_level
    FROM donor_metrics
)
SELECT 
    donor_segment,
    giving_level,
    COUNT(*) as donor_count,
    AVG(lifetime_giving) as avg_lifetime_giving,
    SUM(lifetime_giving) as total_lifetime_giving
FROM donor_segments
GROUP BY donor_segment, giving_level
ORDER BY donor_segment, giving_level DESC;
```

### First-Time Donor Retention

Track how many first-time donors give again:

```sql theme={null}
-- First-time donor retention analysis
WITH first_donations AS (
    SELECT 
        dr.relationship_id as person_id,
        MIN(d.received_at) as first_donation_date
    FROM planning_center.giving_donations d
    JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.refunded = false
    GROUP BY dr.relationship_id
),
second_donations AS (
    SELECT 
        fd.person_id,
        fd.first_donation_date,
        MIN(d.received_at) as second_donation_date
    FROM first_donations fd
    JOIN planning_center.giving_donation_relationships dr
        ON fd.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE d.received_at > fd.first_donation_date
        AND d.refunded = false
    GROUP BY fd.person_id, fd.first_donation_date
)
SELECT 
    DATE_TRUNC('month', first_donation_date) as cohort_month,
    COUNT(DISTINCT fd.person_id) as first_time_donors,
    COUNT(DISTINCT sd.person_id) as retained_donors,
    ROUND(COUNT(DISTINCT sd.person_id) * 100.0 / COUNT(DISTINCT fd.person_id), 2) as retention_rate,
    AVG(sd.second_donation_date - sd.first_donation_date) as avg_days_to_second_donation
FROM first_donations fd
LEFT JOIN second_donations sd ON fd.person_id = sd.person_id
WHERE fd.first_donation_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', first_donation_date)
ORDER BY cohort_month DESC;
```

## Recurring Giving Analysis

### Active Recurring Donors

```sql theme={null}
-- Analyze active recurring donations
SELECT 
    rd.recurring_donation_id,
    rd.amount_cents / 100.0 as recurring_amount,
    rd.frequency,
    rd.status,
    rd.created_at as setup_date,
    rd.last_donation_received_at,
    p.first_name,
    p.last_name,
    -- Calculate expected annual value
    CASE rd.frequency
        WHEN 'weekly' THEN (rd.amount_cents / 100.0) * 52
        WHEN 'biweekly' THEN (rd.amount_cents / 100.0) * 26
        WHEN 'monthly' THEN (rd.amount_cents / 100.0) * 12
        WHEN 'quarterly' THEN (rd.amount_cents / 100.0) * 4
        WHEN 'yearly' THEN rd.amount_cents / 100.0
        ELSE 0
    END as expected_annual_value
FROM planning_center.giving_recurring_donations rd
JOIN planning_center.giving_recurringdonation_relationships rdr
    ON rd.recurring_donation_id = rdr.recurring_donation_id
    AND rdr.relationship_type = 'Person'
JOIN planning_center.giving_people p
    ON rdr.relationship_id = p.person_id
WHERE rd.status = 'active'
ORDER BY expected_annual_value DESC;
```

### Recurring Giving Health Metrics

```sql theme={null}
-- Key metrics for recurring giving program
WITH recurring_metrics AS (
    SELECT 
        COUNT(DISTINCT rd.recurring_donation_id) as active_recurring_count,
        SUM(
            CASE rd.frequency
                WHEN 'weekly' THEN (rd.amount_cents / 100.0) * 52
                WHEN 'biweekly' THEN (rd.amount_cents / 100.0) * 26
                WHEN 'monthly' THEN (rd.amount_cents / 100.0) * 12
                WHEN 'quarterly' THEN (rd.amount_cents / 100.0) * 4
                WHEN 'yearly' THEN rd.amount_cents / 100.0
                ELSE 0
            END
        ) as total_expected_annual,
        AVG(rd.amount_cents / 100.0) as avg_recurring_amount
    FROM planning_center.giving_recurring_donations rd
    WHERE rd.status = 'active'
),
churn_metrics AS (
    SELECT 
        COUNT(*) as churned_last_90_days
    FROM planning_center.giving_recurring_donations
    WHERE status = 'inactive'
        AND updated_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT 
    rm.active_recurring_count,
    rm.total_expected_annual,
    rm.avg_recurring_amount,
    cm.churned_last_90_days,
    ROUND(cm.churned_last_90_days * 100.0 / NULLIF(rm.active_recurring_count + cm.churned_last_90_days, 0), 2) as churn_rate_90d
FROM recurring_metrics rm
CROSS JOIN churn_metrics cm;
```

## Pledge Campaign Tracking

### Campaign Progress Dashboard

```sql theme={null}
-- Track pledge campaign progress
WITH campaign_summary AS (
    SELECT 
        pc.pledge_campaign_id,
        pc.name as campaign_name,
        pc.description,
        pc.goal_cents / 100.0 as campaign_goal,
        pc.pledges_total_cents / 100.0 as total_pledged,
        pc.received_total_cents / 100.0 as total_received,
        COUNT(DISTINCT p.pledge_id) as pledge_count
    FROM planning_center.giving_pledge_campaigns pc
    LEFT JOIN planning_center.giving_pledges p
        ON p.pledge_campaign_id = pc.pledge_campaign_id
    GROUP BY pc.pledge_campaign_id, pc.name, pc.description, 
             pc.goal_cents, pc.pledges_total_cents, pc.received_total_cents
)
SELECT 
    campaign_name,
    campaign_goal,
    total_pledged,
    total_received,
    pledge_count,
    ROUND((total_pledged / NULLIF(campaign_goal, 0)) * 100, 2) as percent_pledged,
    ROUND((total_received / NULLIF(total_pledged, 0)) * 100, 2) as fulfillment_rate,
    campaign_goal - total_received as remaining_to_goal
FROM campaign_summary
ORDER BY campaign_goal DESC;
```

### Individual Pledge Tracking

```sql theme={null}
-- Track individual pledge fulfillment
SELECT 
    p.pledge_id,
    per.first_name,
    per.last_name,
    p.amount_cents / 100.0 as pledge_amount,
    p.received_cents / 100.0 as amount_received,
    pc.name as campaign_name,
    p.created_at as pledge_date,
    ROUND((p.received_cents * 100.0 / NULLIF(p.amount_cents, 0)), 2) as percent_fulfilled,
    (p.amount_cents - p.received_cents) / 100.0 as remaining_balance
FROM planning_center.giving_pledges p
JOIN planning_center.giving_pledge_campaigns pc
    ON p.pledge_campaign_id = pc.pledge_campaign_id
JOIN planning_center.giving_pledge_relationships pr
    ON p.pledge_id = pr.pledge_id
    AND pr.relationship_type = 'Person'
JOIN planning_center.giving_people per
    ON pr.relationship_id = per.person_id
WHERE (p.amount_cents - p.received_cents) > 0  -- Outstanding pledges only
ORDER BY remaining_balance DESC;
```

## Window Functions and Rankings

### Top Donors by Percentile

```sql theme={null}
-- Rank donors by giving and show percentiles
WITH donor_totals AS (
    SELECT 
        p.person_id,
        p.first_name,
        p.last_name,
        SUM(d.amount_cents) / 100.0 as total_given,
        COUNT(d.donation_id) as donation_count
    FROM planning_center.giving_people p
    JOIN planning_center.giving_donation_relationships dr
        ON p.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND d.refunded = false
    GROUP BY p.person_id, p.first_name, p.last_name
),
ranked_donors AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY total_given DESC) as giving_rank,
        NTILE(100) OVER (ORDER BY total_given DESC) as percentile,
        SUM(total_given) OVER (ORDER BY total_given DESC) as cumulative_total,
        SUM(total_given) OVER () as grand_total
    FROM donor_totals
)
SELECT 
    giving_rank,
    first_name,
    last_name,
    total_given,
    donation_count,
    percentile,
    ROUND((cumulative_total / grand_total) * 100, 2) as cumulative_percent_of_total
FROM ranked_donors
WHERE percentile >= 90  -- Top 10% of donors
ORDER BY giving_rank;
```

### Fund Growth Trends with Moving Averages

```sql theme={null}
-- Track fund performance with smoothed trends
WITH daily_fund_totals AS (
    SELECT 
        f.fund_id,
        f.name as fund_name,
        DATE(d.received_at) as donation_date,
        SUM(des.amount_cents) / 100.0 as daily_total
    FROM planning_center.giving_funds f
    JOIN planning_center.giving_designations des ON f.fund_id = des.fund_id
    JOIN planning_center.giving_designation_relationships dr
        ON des.designation_id = dr.designation_id
        AND dr.relationship_type = 'Donation'
    JOIN planning_center.giving_donations d ON dr.relationship_id = d.donation_id
    WHERE d.received_at >= CURRENT_DATE - INTERVAL '90 days'
        AND d.refunded = false
    GROUP BY f.fund_id, f.name, DATE(d.received_at)
),
fund_trends AS (
    SELECT 
        fund_id,
        fund_name,
        donation_date,
        daily_total,
        AVG(daily_total) OVER (
            PARTITION BY fund_id 
            ORDER BY donation_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as seven_day_avg,
        AVG(daily_total) OVER (
            PARTITION BY fund_id 
            ORDER BY donation_date 
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) as thirty_day_avg
    FROM daily_fund_totals
)
SELECT 
    fund_name,
    donation_date,
    daily_total,
    ROUND(seven_day_avg, 2) as seven_day_avg,
    ROUND(thirty_day_avg, 2) as thirty_day_avg,
    ROUND(((seven_day_avg / NULLIF(thirty_day_avg, 0)) - 1) * 100, 2) as trend_direction_pct
FROM fund_trends
WHERE donation_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY fund_name, donation_date DESC;
```

## Performance Optimization

### Using CTEs for Complex Queries

```sql theme={null}
-- Optimized query using multiple CTEs to break down complexity
WITH active_donors AS (
    -- First, identify active donors
    SELECT DISTINCT dr.relationship_id as person_id
    FROM planning_center.giving_donation_relationships dr
    JOIN planning_center.giving_donations d ON dr.donation_id = d.donation_id
    WHERE dr.relationship_type = 'Person'
        AND d.received_at >= CURRENT_DATE - INTERVAL '365 days'
        AND d.refunded = false
),
donor_stats AS (
    -- Calculate statistics only for active donors
    SELECT 
        ad.person_id,
        COUNT(d.donation_id) as donation_count,
        SUM(d.amount_cents) / 100.0 as total_given,
        AVG(d.amount_cents) / 100.0 as avg_donation
    FROM active_donors ad
    JOIN planning_center.giving_donation_relationships dr
        ON ad.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d ON dr.donation_id = d.donation_id
    WHERE d.refunded = false
    GROUP BY ad.person_id
)
-- Final result with person details
SELECT 
    p.first_name,
    p.last_name,
    ds.donation_count,
    ds.total_given,
    ds.avg_donation
FROM donor_stats ds
JOIN planning_center.giving_people p ON ds.person_id = p.person_id
WHERE ds.total_given >= 1000  -- Major donors only
ORDER BY ds.total_given DESC;
```

### Efficient Date Range Queries

```sql theme={null}
-- Use date functions efficiently for better performance
-- Good: Uses index-friendly date comparison
SELECT COUNT(*), SUM(amount_cents) / 100.0 as total
FROM planning_center.giving_donations
WHERE received_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND received_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- Alternative: Using date generation for reports
WITH date_series AS (
    SELECT generate_series(
        DATE_TRUNC('week', CURRENT_DATE - INTERVAL '12 weeks'),
        DATE_TRUNC('week', CURRENT_DATE),
        '1 week'::interval
    ) as week_start
)
SELECT 
    ds.week_start,
    COALESCE(SUM(d.amount_cents) / 100.0, 0) as weekly_total
FROM date_series ds
LEFT JOIN planning_center.giving_donations d
    ON d.received_at >= ds.week_start
    AND d.received_at < ds.week_start + INTERVAL '1 week'
    AND d.refunded = false
GROUP BY ds.week_start
ORDER BY ds.week_start DESC;
```

## Best Practices for Advanced Queries

### 1. Use CTEs for Readability

Break complex queries into logical steps using Common Table Expressions (WITH clauses).

### 2. Optimize JOIN Order

Join smaller result sets first, then join to larger tables.

### 3. Use Appropriate Indexes

The relationship\_type and relationship\_id columns are indexed for efficient joins.

### 4. Aggregate Early

When possible, aggregate data in CTEs before joining to reduce the working set size.

### 5. Handle NULL Values

Always consider NULL values in calculations and use NULLIF to prevent division by zero.

## Next Steps

* Review [Reporting Examples](/planning-center/giving/reporting-examples) for complete, production-ready reports
* Check the [Data Model](/planning-center/giving/data-model) for detailed table documentation
* Return to [Basic Queries](/planning-center/giving/basic-queries) to review fundamentals