SQL Join for Efficiency Leaders

MediumSQL · PostgreSQL

Problem

AXA XL wants to compare operational efficiency across teams. Write a PostgreSQL query that joins operational records with customer and performance data, then calculates each team's average efficiency for the last completed quarter and the quarter before it, using July 1, 2026 as the reporting anchor.

Output

Return one row per team with team_id, team_name, prior_quarter_efficiency, last_quarter_efficiency, and efficiency_improvement.
Include only teams with a non-NULL efficiency average in both quarters. NULL efficiency scores are ignored by the averages.
Treat April through June 2026 as the last completed quarter and January through March 2026 as the prior quarter.
Sort by efficiency_improvement descending, then team_id ascending, and return at most 10 teams.

Schema

operational

Column	Type	Description
operation_idPK	INT	Unique operational record identifier
team_id	VARCHAR(10)	AXA XL team identifier
team_name	VARCHAR(100)	AXA XL team name
customer_id	INT	Customer associated with the operation
operation_date	DATE	Date the operation occurred

customer

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
segment	VARCHAR(50)	Customer market segment

performance

Column	Type	Description
performance_idPK	INT	Unique performance record identifier
operation_id	INT	Operation measured by the performance record
performance_date	DATE	Date of the efficiency measurement
efficiency_score	DECIMAL(5,2)	Efficiency score for the operation

Tablesoperationalcustomerperformance