Solving a Multi-Level SQL Aggregation Problem

Posted
11 Aug 2026
47 reads

Summary

I encountered a multi‑level SQL aggregation problem in an Adobe online assessment and solved it using CTEs.

How it went

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.

Questions asked (1)

  1. 01

    Multi-Level SQL Aggregation: Top Village and Project Funding

    Data Structures & Algorithms

    Given tables Projects (ProjectID, VillageID, ProjectName, Year) and Funds (ProjectID, Amount), find the village(s) that received the maximum total funding in 2024. For each such village, find the project(s) that received the maximum funding within that village. Return the village name, project name, and the funding amount.

Know someone preparing? Send it to them:WhatsAppLinkedInX

Interviewed at Interview Experience recently?

Write up your rounds while you still remember the questions. It helps the next person more than you would think.

Share your experience
Discussion 0

Questions for the author, or how your own interview compared.

Join the discussion

Sign in with Google to comment or ask the author a question.

No comments yet

Be the first to share your thoughts and start the discussion!