Solving a Multi-Level SQL Aggregation Problem

I came across this SQL problem during an Adobe OA. The interesting part wasn't the aggregation itself, but combining multiple levels of aggregation to arrive at the final result.

The problem required finding:

1. The village(s) that received the maximum total funding in 2024.
2. For those villages, the project(s) that received the maximum funding within that village.
3. Return the village name, project name, and the corresponding funding amount.

I approached it using CTEs (Common Table Expressions) to break the problem into smaller steps.

1. Calculate total funding per village

WITH village_totals AS (
    SELECT
        p.VillageID,
        SUM(f.Amount) AS total_fund
    FROM Projects p
    JOIN Funds f
        ON f.ProjectID = p.ProjectID
    WHERE p.Year = 2024
    GROUP BY p.VillageID
)

2. Find the village(s) with the highest funding

best_villages AS (
    SELECT VillageID
    FROM village_totals
    WHERE total_fund = (
        SELECT MAX(total_fund)
        FROM village_totals
    )
)

3. Calculate funding for each project

project_totals AS (
    SELECT
        p.VillageID,
        p.ProjectID,
        p.ProjectName,
        SUM(f.Amount) AS fund
    FROM Projects p
    JOIN Funds f
        ON f.ProjectID = p.ProjectID
    WHERE p.Year = 2024
    GROUP BY
        p.VillageID,
        p.ProjectID,
        p.ProjectName
)

4. Find the highest‑funded project within each selected village

SELECT
    v.VillageName,
    pt.ProjectName,
    pt.fund AS TopProjectFund
FROM project_totals pt
JOIN best_villages bv
    ON bv.VillageID = pt.VillageID
JOIN Villages v
    ON v.VillageID = pt.VillageID
WHERE pt.fund = (
    SELECT MAX(pt2.fund)
    FROM project_totals pt2
    WHERE pt2.VillageID = pt.VillageID
)
ORDER BY v.VillageName, pt.ProjectName;

Complete solution

WITH village_totals AS (
    SELECT
        p.VillageID,
        SUM(f.Amount) AS total_fund
    FROM Projects p
    JOIN Funds f
        ON f.ProjectID = p.ProjectID
    WHERE p.Year = 2024
    GROUP BY p.VillageID
),
best_villages AS (
    SELECT VillageID
    FROM village_totals
    WHERE total_fund = (
        SELECT MAX(total_fund)
        FROM village_totals
    )
),
project_totals AS (
    SELECT
        p.VillageID,
        p.ProjectID,
        p.ProjectName,
        SUM(f.Amount) AS fund
    FROM Projects p
    JOIN Funds f
        ON f.ProjectID = p.ProjectID
    WHERE p.Year = 2024
    GROUP BY p.VillageID, p.ProjectID, p.ProjectName
)
SELECT
    v.VillageName,
    pt.ProjectName,
    pt.fund AS TopProjectFund
FROM project_totals pt
JOIN best_villages bv
    ON bv.VillageID = pt.VillageID
JOIN Villages v
    ON v.VillageID = pt.VillageID
WHERE pt.fund = (
    SELECT MAX(pt2.fund)
    FROM project_totals pt2
    WHERE pt2.VillageID = pt.VillageID
)
ORDER BY v.VillageName, pt.ProjectName;

Key takeaway

The main idea I used was to decompose the problem into aggregation layers:

• Funds → Projects → Villages → Maximum village → Maximum project within that village

Using CTEs made each step independently understandable and the final query much easier to reason about.

This was one of the more interesting SQL questions I encountered in an OA because it tested more than just GROUP BY, it required thinking about hierarchical aggregation and correlated subqueries.