Summarize Team Performance with a CTE
Build a team_summary CTE, then return each team’s snapshot count, total revenue, and average quality score.
- Recursive CTE
- CTEs
- Subqueries
- Aggregation
- Numeric functions
Exercise brief
Understand the request
Operations director A portfolio review needs one reliable summary row per team without exposing intermediate aggregation details.
A portfolio review needs one reliable summary row per team without exposing intermediate aggregation details. Build a team_summary CTE, then return each team’s snapshot count, total revenue, and average quality score.
Return
- Return team_name, snapshot_count, total_revenue, and avg_quality_score.
- Round avg_quality_score to two decimals and order by team_name.
Constraints
- Use a non-recursive CTE named team_summary.
- Aggregate at team_name grain inside the CTE.
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
A CTE names an intermediate result for the statement that follows.
Hint 2
Group the source rows by team_name inside team_summary.
Hint 3
Select the four authored aliases from team_summary and sort by team_name.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH team_summary AS (SELECT team_name, COUNT(*) AS snapshot_count, SUM(revenue) AS total_revenue, ROUND(AVG(quality_score), 2) AS avg_quality_score FROM team_metrics GROUP BY team_name) SELECT team_name, snapshot_count, total_revenue, avg_quality_score FROM team_summary ORDER BY team_name;Why this works
The CTE fixes the reporting grain before the outer query presents the result, making the aggregation boundary explicit and reusable.
Success check
Every team appears exactly once with totals calculated from all of its snapshots.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| team_name | snapshot_count | total_revenue | avg_quality_score |
|---|---|---|---|
| alpha | 5 | 580 | 94 |
| beta | 5 | 510 | 93.8 |
| gamma | 4 | 770 | 95 |
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.