Find Top-Funded Villages and Their Top Projects

Problem Statement

You are tasked with auditing rural development initiatives. You are given three tables: villages, projects, and project_fundings.

Write a SQL query that finds every village tied for the highest total funding disbursed in calendar year 2024 (from 2024-01-01 00:00:00 up to but not including 2025-01-01 00:00:00). For each winning village, find the project (or projects, in case of a tie) that received the highest total funding in 2024 within that village.

Schema

CREATE TABLE villages (
    village_id INT PRIMARY KEY,
    village_name VARCHAR(100) NOT NULL
);

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    village_id INT NOT NULL,
    project_name VARCHAR(100) NOT NULL
);

CREATE TABLE project_fundings (
    funding_id INT PRIMARY KEY,
    project_id INT NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    funded_at TIMESTAMP NOT NULL
);

Requirements

1. Year Boundary: Filter records strictly to calendar year 2024: funded_at >= '2024-01-01 00:00:00' and funded_at < '2025-01-01 00:00:00'.
2. Aggregation: Projects may receive multiple funding disbursements in 2024; sum them to determine each project's 2024 total. Sum all project totals in a village to get the village total.
3. Preserve Ties:
   • If multiple villages tie for the highest total 2024 funding, include all of them.
   • Within each winning village, if multiple projects tie for the highest funding in 2024, include all of them.
4. Output Columns:
   • village_id (INT)
   • village_name (VARCHAR)
   • village_total_funding (DECIMAL/NUMERIC)
   • project_id (INT)
   • project_name (VARCHAR)
   • project_total_funding (DECIMAL/NUMERIC)
5. Ordering: Sort the output deterministically by village_id ASC, then project_id ASC.

Example

Input:

villages:

projects:

project_fundings:

Explanation:

• Oakridge (Village 3) received 9000.00 in 2023, so its 2024 total is 0.
• Greenfield (Village 1): Project 101 received 5000.00, Project 102 received 5000.00. Total village funding = 10,000.00.
• Riverdale (Village 2): Project 103 received 6000.00, Project 104 received 4000.00. Total village funding = 10,000.00.
• Both Village 1 and Village 2 tie for the top spot at 10,000.00.
• In Village 1, Projects 101 and 102 tie for top project (5000.00 each).
• In Village 2, Project 103 is the sole top project (6000.00).

Output: