## Question Given these tables: ```text Villages(VillageId, VillageName) Projects(ProjectId, VillageId, ProjectName) Funds(FundId, ProjectId, FundedAt, Amount) ``` Write a SQL query that first finds every village tied for the largest total funding received during calendar year 2024. For each winning village, return every project tied for that village's largest 2024 funding total. Output `VillageName`, `ProjectName`, and the project's 2024 funding amount. ### Constraints & Assumptions - `Amount` is non-null and nonnegative. - A project may have multiple fund rows in 2024. - Villages and projects with no 2024 funding cannot be winners. - Ties must be preserved at both ranking levels. - Use a half-open date range for the year. ### Clarifying Questions to Ask - Is “project funding amount” one row or the project's 2024 sum? Sum all 2024 fund rows per project. - Can multiple villages tie for first? Yes. - Can multiple projects tie within a winning village? Yes. - Should zero-funded villages appear? No. - How should output ties be ordered? Village name, project name, then stable IDs. ```hint Aggregate once, rank twice First produce one row per village and project. Then derive village totals and use dense ranking at the village level and within each village. ``` ### Evaluation Focus - Correct joins and a sargable 2024 date predicate. - Project-level sums before village totals, avoiding double counting. - Tie-preserving ranking rather than `LIMIT 1`. - Independent partitioned ranking for top projects inside each winning village. - Deterministic presentation order and suitable indexes. ### Extensions to Discuss 1. How would you include villages whose total is zero when all villages are unfunded? 2. Which index best supports the date and project join? 3. How would the query change for each village's fiscal year?