Quickstart

-- Active people, by membership status
SELECT
    COALESCE(membership, 'Unspecified') as membership,
    COUNT(*) as people
FROM planning_center.people_people
WHERE status = 'active'
GROUP BY COALESCE(membership, 'Unspecified')
ORDER BY people DESC;

-- Weekly check-in trend over the last 12 weeks
SELECT
    DATE_TRUNC('week', c.created_at) as week,
    COUNT(DISTINCT cr.relationship_id) as unique_attendees,
    COUNT(*) as check_ins
FROM planning_center.checkins_check_ins c
JOIN planning_center.checkins_check_ins_relationships cr
    ON cr.check_in_id = c.check_in_id
    AND cr.relationship_type = 'Person'
WHERE c.created_at >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', c.created_at)
ORDER BY week DESC;

-- Giving totals by fund, this year
SELECT
    f.name as fund,
    COUNT(DISTINCT d.donation_id) as gifts,
    SUM(des.amount_cents) / 100.0 as total_dollars
FROM planning_center.giving_donations d
JOIN planning_center.giving_donations_relationships dr
    ON dr.donation_id = d.donation_id
    AND dr.relationship_type = 'Designation'
JOIN planning_center.giving_designations des
    ON des.designation_id = dr.relationship_id
JOIN planning_center.giving_designations_relationships desr
    ON desr.designation_id = des.designation_id
    AND desr.relationship_type = 'Fund'
JOIN planning_center.giving_funds f
    ON f.fund_id = desr.relationship_id
WHERE d.payment_status = 'succeeded'
  AND d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY f.name
ORDER BY total_dollars DESC;