SQL for Branch Performance Trends

HardSQL · PostgreSQL

Problem

Write a SQL query to measure branch performance by month at Byline Bank, joining transactions, customer satisfaction, and staffing tables to identify trends and outliers.

Calculate completed transaction volume, transaction counts, satisfaction, staffing, month-over-month change, and an outlier indicator for each branch-month. Preserve branch-months with transactions even when related satisfaction or staffing records are missing.

Output

One row per branch and calendar month with transactions.
Include branch_id, month, total_transaction_amount, transaction_count, average_satisfaction, average_staffing, month_over_month_change, trend, and is_outlier.
Sort by branch_id, then month ascending.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
branch_id	INT	Byline Bank branch identifier
transaction_date	DATE	Transaction date
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

customer_satisfaction

Column	Type	Description
survey_idPK	INT	Unique survey identifier
branch_id	INT	Branch associated with the survey
survey_date	DATE	Survey submission date
rating	NUMERIC(3,1)	Customer satisfaction rating from 1 to 5

staffing

Column	Type	Description
staffing_idPK	INT	Unique staffing record identifier
branch_id	INT	Branch associated with the staffing record
staffing_date	DATE	Staffing measurement date
employee_count	INT	Employees scheduled at the branch

Tablestransactionscustomer_satisfactionstaffing