Compare Team Revenue with a Chained CTE Pipeline
Use one CTE for team totals and a second CTE that derives the portfolio average from those totals.
- CTEs
- Joins
- Subqueries
- Aggregation
- CASE expressions
Exercise brief
Understand the request
Finance business partner Leadership wants each team compared with the average total revenue across teams.
Leadership wants each team compared with the average total revenue across teams. Use one CTE for team totals and a second CTE that derives the portfolio average from those totals.
Return
- Return team_name, total_revenue, portfolio_avg_revenue, and revenue_position.
- Label totals above the average 'above'; otherwise label them 'at_or_below'.
- Round portfolio_avg_revenue to two decimals and order by team_name.
Constraints
- Declare team_totals before portfolio_average.
- Make portfolio_average read from team_totals rather than re-aggregating the base table.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
team_metrics
snapshot_idINTEGERteam_nameVARCHAR(40)period_noINTEGERrevenueINTEGERtickets_closedINTEGERquality_scoreINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Later CTEs may reference earlier CTEs in the same WITH block.
Hint 2
Compute the average from the three team total rows, not from raw snapshots.
Hint 3
Cross join the one-row benchmark CTE to team_totals.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH team_totals AS (SELECT team_name, SUM(revenue) AS total_revenue FROM team_metrics GROUP BY team_name), portfolio_average AS (SELECT ROUND(AVG(total_revenue), 2) AS portfolio_avg_revenue FROM team_totals) SELECT t.team_name, t.total_revenue, p.portfolio_avg_revenue, CASE WHEN t.total_revenue > p.portfolio_avg_revenue THEN 'above' ELSE 'at_or_below' END AS revenue_position FROM team_totals t CROSS JOIN portfolio_average p ORDER BY t.team_name;Why this works
Chained CTEs make transformation grain visible: raw snapshots become team totals, then the totals become a portfolio benchmark.
Success check
The comparison uses one row per team and a single portfolio benchmark.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| team_name | total_revenue | portfolio_avg_revenue | revenue_position |
|---|---|---|---|
| alpha | 580 | 620 | at_or_below |
| beta | 510 | 620 | at_or_below |
| gamma | 770 | 620 | above |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
Open the interactive workspace and practice across SQL topics.