Project: Impact Analysis of GoodThought NGO Initiatives

GoodThought NGO has been a catalyst for positive change, focusing its efforts on education, healthcare, and sustainable development to make a significant difference in communities worldwide. With this mission, GoodThought has orchestrated an array of assignments aimed at uplifting underprivileged populations and fostering long-term growth.

This project offers a hands-on opportunity to explore how data-driven insights can direct and enhance these humanitarian efforts. In this project, you'll engage with the GoodThought PostgreSQL database, which encapsulates detailed records of assignments, funding, impacts, and donor activities from 2010 to 2023. This comprehensive dataset includes:

Assignments: Details about each project, including its name, duration (start and end dates), budget, geographical region, and the impact score.
Donations: Records of financial contributions, linked to specific donors and assignments, highlighting how financial support is allocated and utilized.
Donors: Information on individuals and organizations that fund GoodThought’s projects, including donor types.

Refer to the below ERD diagram for a visual representation of the relationships between these data tables:

You will execute SQL queries to answer two questions, as listed in the instructions. Good luck!

Hidden code highest_donation_assignments

Hidden code top_regional_impact_assignments

Unknown table

DataFrameas

df1

variable

-- Explore the data in the table
SELECT *
FROM public.donations
LIMIT 50

DataFrameas

highest_donation_assignments

variable

-- "highest_donation_assignments"
-- Since CREATE TABLE AS cannot be executed in a read-only transaction (such as in Jupyter or some cloud SQL environments),
-- we will use a CTE and just SELECT the results instead of creating a table.

WITH donation_sums AS (
    SELECT 
        a.assignment_id,
        a.assignment_name,
        a.region,
        dn.donor_type,
        ROUND(SUM(d.amount), 2) AS rounded_total_donation_amount
    FROM public.donations d
    JOIN public.assignments a ON a.assignment_id = d.assignment_id
    JOIN public.donors dn ON d.donor_id = dn.donor_id
    GROUP BY a.assignment_id, a.assignment_name, a.region, dn.donor_type
)
SELECT 
    assignment_name,
    region,
    rounded_total_donation_amount,
    donor_type
FROM donation_sums
ORDER BY rounded_total_donation_amount DESC
LIMIT 5;

DataFrameas

top_regional_impact_assignments

variable

SELECT 
    a.assignment_name,
    a.region,
    a.impact_score,
    t.num_total_donations
FROM (
    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_id ASC) AS rn
    FROM public.assignments a
    JOIN public.donations d ON a.assignment_id = d.assignment_id
) a
JOIN (
    SELECT 
        d.assignment_id,
        COUNT(DISTINCT d.donation_id) AS num_total_donations
    FROM public.donations d
    GROUP BY d.assignment_id
) t
    ON a.assignment_id = t.assignment_id
WHERE a.rn = 1
ORDER BY a.region ASC;