queries.sql

-- 1. Lifetime giving by donor, including constituents with no gifts.
SELECT
    d.donor_id,
    d.region,
    COALESCE(SUM(g.amount), 0) AS lifetime_giving
FROM donors AS d
LEFT JOIN donations AS g ON g.donor_id = d.donor_id
GROUP BY d.donor_id, d.region
ORDER BY lifetime_giving DESC;

-- 2. Number of gifts per donor, including constituents with no gifts.
SELECT
    d.donor_id,
    COUNT(g.donation_id) AS gift_count
FROM donors AS d
LEFT JOIN donations AS g ON g.donor_id = d.donor_id
GROUP BY d.donor_id
ORDER BY gift_count DESC, d.donor_id;

-- 3. Average gift amount by donor for constituent-level giving analysis.
SELECT
    d.donor_id,
    d.region,
    COUNT(g.donation_id) AS gift_count,
    ROUND(AVG(g.amount), 2) AS average_gift_amount
FROM donors AS d
INNER JOIN donations AS g ON g.donor_id = d.donor_id
WHERE g.amount > 0
GROUP BY d.donor_id, d.region
ORDER BY average_gift_amount DESC, d.donor_id;

-- 4. Last gift and recency relative to the latest date in the dataset.
WITH latest_activity AS (
    SELECT MAX(donation_date) AS as_of_date
    FROM donations
)
SELECT
    d.donor_id,
    MAX(g.donation_date) AS last_donation_date,
    CAST(
        julianday(latest_activity.as_of_date) - julianday(MAX(g.donation_date)) AS INTEGER
    ) AS days_since_last_gift
FROM donors AS d
LEFT JOIN donations AS g ON g.donor_id = d.donor_id
CROSS JOIN latest_activity
GROUP BY d.donor_id, latest_activity.as_of_date
ORDER BY days_since_last_gift;

-- 5. Monthly fundraising totals with gift and active-donor counts.
SELECT
    strftime('%Y-%m', donation_date) AS gift_month,
    ROUND(SUM(amount), 2) AS total_raised,
    COUNT(*) AS gift_count,
    COUNT(DISTINCT donor_id) AS active_donors
FROM donations
GROUP BY gift_month
ORDER BY gift_month;

-- 6. Portfolio retention view: one-time versus repeat donors.
WITH donor_gifts AS (
    SELECT donor_id, COUNT(*) AS gift_count
    FROM donations
    GROUP BY donor_id
),
retention_summary AS (
    SELECT
        CASE WHEN gift_count > 1 THEN 'Repeat donor' ELSE 'One-time donor' END AS donor_status,
        COUNT(*) AS donor_count
    FROM donor_gifts
    GROUP BY donor_status
)
SELECT
    donor_status,
    donor_count,
    ROUND(
        100.0 * donor_count / (SELECT SUM(donor_count) FROM retention_summary), 1
    ) AS percent_of_giving_donors
FROM retention_summary
ORDER BY donor_count DESC;

-- 7. Campaign performance across revenue, participation, and gift size.
SELECT
    campaign,
    COUNT(*) AS gift_count,
    COUNT(DISTINCT donor_id) AS unique_donors,
    ROUND(SUM(amount), 2) AS total_raised,
    ROUND(AVG(amount), 2) AS average_gift
FROM donations
GROUP BY campaign
ORDER BY total_raised DESC;

-- 8. Acquisition-channel performance, including repeat-donor counts.
WITH donor_performance AS (
    SELECT
        d.donor_id,
        d.acquisition_channel,
        COUNT(g.donation_id) AS gift_count,
        COALESCE(SUM(g.amount), 0) AS lifetime_giving
    FROM donors AS d
    LEFT JOIN donations AS g ON g.donor_id = d.donor_id
    GROUP BY d.donor_id, d.acquisition_channel
)
SELECT
    acquisition_channel,
    COUNT(DISTINCT donor_id) AS acquired_donors,
    SUM(CASE WHEN gift_count > 1 THEN 1 ELSE 0 END) AS repeat_donors,
    ROUND(AVG(lifetime_giving), 2) AS average_lifetime_giving,
    ROUND(SUM(lifetime_giving), 2) AS total_raised
FROM donor_performance
GROUP BY acquisition_channel
ORDER BY total_raised DESC;