Multi-criteria ranking with regulatory exclusion
Strategy team is sizing the 2026 branch footprint. I want a shortlist of 5 consolidation candidates. Two constraints: (1) the branch must NOT be in an LMI tract (closing those triggers a CRA review), (2) rank by lowest average daily foot_traffic across the 90-day activity window. Pull from `retail_branches` LEFT JOIN to `retailops_branch_activity`. Output `branch_id`, `branch_name`, `region`, `avg_foot_traffic` (rounded to 1 decimal), `avg_deposits` (rounded to 1 decimal). Sort foot_traffic ascending, limit 5.
| Column | Type | Key |
|---|---|---|
| branch_id | INT | PK |
| branch_name | TEXT | |
| region | TEXT | |
| state | TEXT | |
| lmi_tract_flag | INT | |
| open_date | TEXT | |
| close_date | TEXT | |
| branch_type | TEXT |
| Column | Type | Key |
|---|---|---|
| activity_id | INT | PK |
| branch_id | INT | FK → retail_branches |
| activity_date | TEXT | |
| teller_count | INT | |
| account_openings | INT | |
| foot_traffic | INT | |
| deposits_taken_count | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
`avg_foot_traffic` is an aggregate — you'll need to GROUP BY the branch fields and AVG the activity columns. LEFT JOIN guards against branches that have zero activity rows from being silently dropped.
The LMI exclusion lives in WHERE on `retail_branches.lmi_tract_flag = 0`. Putting it on the branches side (not the activity side) is what makes the LEFT JOIN behave correctly.
GROUP BY the branch fields, ORDER BY the AVG ascending so the lowest-traffic branches surface first, then LIMIT to the shortlist size in the briefing. Round both AVG outputs to 1 decimal.