Advanced Planning Center Giving Queries

-- 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_donations_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_donations_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_donations_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;

-- 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 f.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 to designations via donation_relationships (type='Designation')
    JOIN planning_center.giving_donations_relationships dr_des
        ON d.donation_id = dr_des.donation_id
        AND dr_des.relationship_type = 'Designation'
    JOIN planning_center.giving_designations des
        ON dr_des.relationship_id = des.designation_id
    -- Join to funds via designation_relationships (type='Fund')
    JOIN planning_center.giving_designations_relationships desr
        ON des.designation_id = desr.designation_id
        AND desr.relationship_type = 'Fund'
    JOIN planning_center.giving_funds f
        ON desr.relationship_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;

-- 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, 'FMMonth') 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_donations_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, 'FMMonth')
)
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;

-- 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;

-- 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_donations_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 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_donations_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_donations_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', fd.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', fd.first_donation_date)
ORDER BY cohort_month DESC;

-- Analyze active recurring donations
-- Person is found via donations that link to the recurring donation
SELECT DISTINCT ON (rd.recurring_donation_id)
    rd.recurring_donation_id,
    rd.amount_cents / 100.0 as recurring_amount,
    rd.schedule,
    rd.status,
    rd.created_at as setup_date,
    rd.last_donation_received_at,
    p.first_name,
    p.last_name
FROM planning_center.giving_recurring_donations rd
-- Find a donation linked to this recurring donation
JOIN planning_center.giving_donations_relationships dr_rd
    ON dr_rd.relationship_id = rd.recurring_donation_id
    AND dr_rd.relationship_type = 'RecurringDonation'
-- Get the person from that same donation
JOIN planning_center.giving_donations_relationships dr_p
    ON dr_rd.donation_id = dr_p.donation_id
    AND dr_p.relationship_type = 'Person'
JOIN planning_center.giving_people p
    ON dr_p.relationship_id = p.person_id
WHERE rd.status = 'active'
ORDER BY rd.recurring_donation_id, rd.amount_cents DESC;

-- Key metrics for recurring giving program
-- Note: schedule is stored as JSONB; frequency is not a direct column.
-- Use amount_cents as a proxy for expected value (monthly equivalent assumes 12x/year).
WITH recurring_metrics AS (
    SELECT 
        COUNT(DISTINCT rd.recurring_donation_id) as active_recurring_count,
        SUM(rd.amount_cents / 100.0) as total_active_amount,
        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 IN ('indefinite_hold', 'temporary_hold')
        AND updated_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT 
    rm.active_recurring_count,
    rm.total_active_amount,
    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;

-- 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,
        -- received_total_from_pledges_cents + received_total_outside_of_pledges_cents = total received
        (COALESCE(pc.received_total_from_pledges_cents, 0) + 
         COALESCE(pc.received_total_outside_of_pledges_cents, 0)) / 100.0 as total_received,
        COUNT(DISTINCT pr.pledge_id) as pledge_count,
        SUM(COALESCE(p.amount_cents, 0)) / 100.0 as total_pledged
    FROM planning_center.giving_pledge_campaigns pc
    -- Pledges connect to campaigns via pledge_relationships (type='PledgeCampaign' if data exists)
    -- or can be queried separately and aggregated
    LEFT JOIN planning_center.giving_pledges_relationships pr
        ON pr.relationship_type = 'PledgeCampaign'
        AND pr.relationship_id = pc.pledge_campaign_id
    LEFT JOIN planning_center.giving_pledges p
        ON p.pledge_id = pr.pledge_id
    GROUP BY pc.pledge_campaign_id, pc.name, pc.description, 
             pc.goal_cents, pc.received_total_from_pledges_cents,
             pc.received_total_outside_of_pledges_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;

-- Track individual pledge fulfillment
-- donated_total_cents tracks how much has been received toward a pledge
SELECT 
    p.pledge_id,
    per.first_name,
    per.last_name,
    p.amount_cents / 100.0 as pledge_amount,
    p.donated_total_cents / 100.0 as amount_received,
    pc.name as campaign_name,
    p.created_at as pledge_date,
    ROUND((p.donated_total_cents * 100.0 / NULLIF(p.amount_cents, 0)), 2) as percent_fulfilled,
    (p.amount_cents - COALESCE(p.donated_total_cents, 0)) / 100.0 as remaining_balance
FROM planning_center.giving_pledges p
-- Join to campaign via pledge_relationships (type='PledgeCampaign')
JOIN planning_center.giving_pledges_relationships pr_campaign
    ON p.pledge_id = pr_campaign.pledge_id
    AND pr_campaign.relationship_type = 'PledgeCampaign'
JOIN planning_center.giving_pledge_campaigns pc
    ON pr_campaign.relationship_id = pc.pledge_campaign_id
-- Join to person via pledge_relationships (type='Person')
JOIN planning_center.giving_pledges_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 - COALESCE(p.donated_total_cents, 0)) > 0  -- Outstanding pledges only
ORDER BY remaining_balance DESC;

-- 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_donations_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;

-- 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 funds to designations via designation_relationships (type='Fund')
    JOIN planning_center.giving_designations_relationships desr
        ON desr.relationship_id = f.fund_id
        AND desr.relationship_type = 'Fund'
    JOIN planning_center.giving_designations des
        ON des.designation_id = desr.designation_id
    -- Join designations to donations via donation_relationships (type='Designation')
    JOIN planning_center.giving_donations_relationships dr_des
        ON dr_des.relationship_id = des.designation_id
        AND dr_des.relationship_type = 'Designation'
    JOIN planning_center.giving_donations d ON dr_des.donation_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;

-- 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_donations_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_donations_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;

-- 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;