Fix the plan total check for a dashboard query
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"whenindependent_totalor any plan count is negative.- Otherwise add up the plan counts. An empty list adds up to 0.
"match"when the sum equalsindependent_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) -> strExample 1
Input
reconcile_plan_totals([120, 80, 50], 250)Output
"match"Example 2
Input
reconcile_plan_totals([120, 95, 50], 250)Output
"fan_out:15"