Fix the plan total check for a dashboard query

Review the AI's codeEasy10 min

Your task

You asked an AI for a SQL query that counts active users per plan over the last 30 days, and it gave you one with a JOIN and a GROUP BY. Before execs read the numbers on a dashboard, your team checks that the per-plan counts add up to an independently counted total.

An assistant wrote reconcile_plan_totals(plan_counts, independent_total). It is in the editor. It has a bug. Find it and fix it.

Input and output:

  • plan_counts: the query's count for each plan.
  • independent_total: the number of active users counted another way, straight from the users table.
  • Return "match", "fan_out:<n>", "dropped:<n>" or "invalid".

Rules the tests check:

  • "invalid" when independent_total or any plan count is negative.
  • Otherwise add up the plan counts. An empty list adds up to 0.
  • "match" when the sum equals independent_total.
  • "fan_out:<n>" when the sum is larger, where <n> is how much larger. Users were counted more than once.
  • "dropped:<n>" when the sum is smaller, where <n> is how much smaller. Users went missing from the join.

Examples

reconcile_plan_totals(plan_counts: list[int], independent_total: int) -> str
Example 1
Inputreconcile_plan_totals([120, 80, 50], 250)
Output"match"
Example 2
Inputreconcile_plan_totals([120, 95, 50], 250)
Output"fan_out:15"