Project: Impact Analysis of GoodThought NGO Initiatives

DataFrameas

highest_donation_assignments

variable

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

DataFrameas

top_regional_impact_assignments

variable

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

ranking AS(
SELECT assignment_name,
       region,
       impact_score,
	   num_total_donations,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY impact_score DESC) AS rank_per_region
FROM public.assignments AS a
INNER JOIN donation_count AS dc
ON a.assignment_id = dc.assignment_id
WHERE num_total_donations > 0
)

SELECT assignment_name,
       region,
       impact_score,
       num_total_donations
FROM ranking
WHERE rank_per_region = 1
ORDER BY region ASC;

DataFrameas

df

variable

SELECT *
FROM public.assignments;

DataFrameas

df1

variable

SELECT *
FROM public.donations;

DataFrameas

df2

variable

SELECT *
FROM public.donors;

DataFrameas

df3

variable

SELECT assignment_name, region
FROM public.assignments;

DataFrameas

df4

variable

SELECT ROUND(SUM(amount), 2) AS rounded_total_donation_amount
FROM public.donations;

DataFrameas

df5

variable

SELECT donor_type
FROM public.donors;

DataFrameas

TASK_1

variable

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

DataFrameas

df6

variable

SELECT assignment_id,
       COUNT(donation_id) AS num_total_donations
FROM public.donations
GROUP BY assignment_id;

DataFrameas

df7

variable

SELECT assignment_name,
       region,
       impact_score,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY impact_score DESC) AS rank_per_region
FROM public.assignments;

DataFrameas

TASK_2

variable

WITH donation_count AS(
SELECT assignment_id,
       COUNT(donation_id) AS num_total_donations
FROM public.donations
GROUP BY assignment_id),
ranking AS(
SELECT assignment_name,
       region,
       impact_score,
	   num_total_donations,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY impact_score DESC) AS rank_per_region
FROM public.assignments AS a
INNER JOIN donation_count AS dc
ON a.assignment_id = dc.assignment_id
WHERE num_total_donations > 0
)

SELECT assignment_name,
       region,
       impact_score,
       num_total_donations
FROM ranking
WHERE rank_per_region = 1
ORDER BY region ASC;