Only this week, get the SQL Complete Track of 9 courses in a special prize of $330$89!
Introduction
Nested CTEs
5. Simple nested CTEs
Summary

Instruction

All right, let's get started! We're going to tackle nested CTEs.

If you take a look into SQL Server's documentation, you will find out that you cannot put one CTE inside the parentheses of another CTE. Luckily, we can still use a simple technique to create nested CTEs. Take a look at the query:

WITH TotalSalesman AS (
SELECT
S.Id AS SId,
C.Id AS CId,
SUM(Distance) AS SumKilometers
FROM Salesman S
INNER JOIN DailySales DS
ON S.Id = DS.SalesmanId
INNER JOIN City C
ON S.CityId = C.Id
GROUP BY S.Id,
C.Id
),
MaxSalesman AS (
SELECT
CId,
MAX(SumKilometers) AS MaxKilometers
FROM TotalSalesman
GROUP BY CId
)

SELECT
AVG(MaxKilometers) AS AvgMaxKilometers
FROM MaxSalesman;


The trick is that the second query, MaxSalesman, uses the first CTE, TotalSalesman, in the FROM clause (FROM TotalSalesman). That means that once we define the first CTE, we can freely use it in subsequent CTEs.

Now, what does that query do? In the first CTE, we find the total number of kilometers driven in each city by each salesperson (on all days). In the second CTE, we take these sums and choose the person with the maximal distance in each city. Finally, in the outer query, we find the average maximal distance across all cities.

As you know, it's impossible to use one aggregate function inside another. Nested CTEs are a simple way to get rid of that restriction.

Exercise

Now it's your turn! In this part, we will clearly state each step you need to take so that you don't get confused.

First, find the daily sums of the AmountEarned for each city. Then, find the average daily amount for all cities for all days. Finally, show the Id and Name of each city plus the number of daily sums that exceeded the average daily amount across all cities. Name the last column BigDailySums.

Stuck? Here's a hint!

SUM the amounts earned in the first CTE, then find the average in the second CTE. In the outer query, use COUNT and make use of the results from both CTEs in the FROM and the WHERE clauses:

FROM DailySumCity, AvgAllCities
WHERE SumAmount > AvgAmount
{"extraction_info": {"found_math": true, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 1, "mathjax_display_tex": 0, "mathjax_asciimath": 1, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.3798947036266327, "perplexity": 2784.605761798464}, "config": {"markdown_headings": false, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.18, "absolute_threshold": 10, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2019-18/segments/1555578532929.54/warc/CC-MAIN-20190421215917-20190422001917-00224.warc.gz"}