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!

DataFrameas

df

variable

SELECT * from assignments limit 5

DataFrameas

df1

variable

select * from donors limit 5

DataFrameas

df2

variable

select * from donations limit 5

DataFrameas

highest_donation_assignments

variable

-- highest_donation_assignments
WITH donation_details AS(
	SELECT	dns.assignment_id,
			ROUND(SUM(dns.amount),2) AS rounded_total_donation_amount,
			dn.donor_type
	FROM donations AS dns
	INNER JOIN donors AS dn
	ON dns.donor_id = dn.donor_id
	GROUP BY dns.assignment_id, dn.donor_type
)
SELECT a.assignment_name,
		a.region,
		dd.rounded_total_donation_amount,
		dd.donor_type
FROM assignments AS a
INNER JOIN donation_details AS dd
ON a.assignment_id = dd.assignment_id
ORDER BY dd.rounded_total_donation_amount DESC
LIMIT 5;

DataFrameas

top_regional_impact_assignments

variable

-- top_regional_impact_assignments
WITH number_of_donations AS (
	SELECT assignment_id,
		COUNT(donation_id) AS num_total_donations
	FROM donations
	GROUP BY assignment_id
),

rank_region AS (
	SELECT a.assignment_name,
			a.region,
			a.impact_score,
			nod.num_total_donations,
			ROW_NUMBER() OVER(PARTITION BY a.region ORDER BY a.impact_score DESC) AS reg_rank
	FROM assignments AS a
	INNER JOIN number_of_donations AS nod
	ON a.assignment_id = nod.assignment_id
	WHERE nod.num_total_donations > 0
)

SELECT assignment_name,
		region,
		impact_score,
		num_total_donations
FROM rank_region AS rr
WHERE reg_rank = 1
ORDER BY region ASC