GoodThought NGO Data Analysis

Background

GoodThought NGO is a humanitarian organization focused on education, healthcare, and sustainable development. This project involved querying a PostgreSQL database covering assignments, donations, and donor activity from 2010 to 2023 to identify where funding was concentrated and which programs were delivering the highest impact by region.

Database Schema

The database contains four tables:

Table	Primary Key	Key Columns
assignments	assignment_id	assignment_name, start_date, end_date, budget, region, impact_score
donations	donation_id	donor_id (FK), amount, donation_date, assignment_id (FK), status
donors	donor_id	donor_name, donor_type
beneficiaries	beneficiary_id	beneficiary_name, region, assignment_id (FK)

Analysis 1: Highest Donation Assignments

This query joins assignments, donations, and donors to find the top 5 assignments by total donation amount, grouped by region and donor type.

SELECT
  a.assignment_name,
  a.region,
  ROUND(SUM(d1.amount), 2) AS rounded_total_donation_amount,
  d2.donor_type
FROM public.assignments a
LEFT JOIN public.donations d1
  ON a.assignment_id = d1.assignment_id
LEFT JOIN public.donors d2
  ON d1.donor_id = d2.donor_id
WHERE d1.amount IS NOT NULL
GROUP BY a.assignment_id, a.region, d2.donor_type
ORDER BY rounded_total_donation_amount DESC
LIMIT 5;

Results:

Assignment	Region	Total Donations	Donor Type
Assignment_3033	East	$3,840.66	Individual
Assignment_300	West	$3,133.98	Organization
Assignment_4114	North	$2,778.57	Organization
Assignment_1765	West	$2,626.98	Organization
Assignment_268	East	$2,488.69	Individual

Individual donors drove the top result, but organizations account for four of the five. That split is worth noting when thinking about where fundraising effort is going.

Analysis 2: Top Regional Impact Assignments

This query uses a CTE with ROW_NUMBER() to isolate the highest-impact assignment in each region, then counts how many donations each received.

WITH max_region_impact AS (
  SELECT
    a.assignment_id,
    a.assignment_name,
    a.region,
    a.impact_score,
    ROW_NUMBER() OVER(
      PARTITION BY a.region
      ORDER BY a.impact_score DESC, a.assignment_name
    ) AS rn
  FROM public.assignments a
)
SELECT
  m.assignment_name,
  m.region,
  m.impact_score,
  COUNT(d.donation_id) AS num_total_donations
FROM max_region_impact m
LEFT JOIN public.donations d
  ON m.assignment_id = d.assignment_id
WHERE m.rn = 1
GROUP BY m.assignment_name, m.region, m.impact_score
ORDER BY m.region;

Results:

Assignment	Region	Impact Score	Donations Received
Assignment_316	East	10.00	2
Assignment_2253	North	9.99	1
Assignment_3547	South	10.00	1
Assignment_2794	West	9.99	2

High impact scores do not map cleanly to high donation counts. The programs scoring best on impact are receiving very little donor attention, which points to a resource allocation gap worth investigating.

What I Demonstrated

• Multi-table JOINs: chained LEFT JOIN across three tables (assignments, donations, donors) to bring donor type into an aggregation starting from the assignment level.
• CTEs: used WITH max_region_impact AS (...) to separate the window function logic from the final aggregation, keeping the query readable and the intent clear.
• Window functions: ROW_NUMBER() OVER (PARTITION BY region ORDER BY impact_score DESC) ranks assignments within each region; the outer query filters to rank 1 with WHERE rn = 1.
• Aggregations and rounding: SUM() with ROUND(..., 2) for clean financial figures; COUNT() for donation tallies.
• NULL handling: WHERE d1.amount IS NOT NULL guards the donation sum against null rows introduced by the LEFT JOIN.