Compléter
Drills to master SQL Common Table Expressions
1
id
INNER
FROM
AS
0
e.id
e.manager_id
e.manager_id
employees
SELECT
FROM
e
eh
AS
eh.id
e.name
level
WITH
name
manager_id
name
eh.level + 1
ON
level
FROM
id
EmployeeHierarchy
EmployeeHierarchy
EmployeeHierarchy
SELECT
UNION
manager_id
IS
JOIN
SELECT
employees
ALL
WHERE
NULL
RECURSIVE
Problem
1
:
Recursive
Employee
Hierarchy
Question
:
For
a
table
employees
(
id
INT
,
name
VARCHAR
,
manager_id
INT
)
,
write
a
SQL
query
using
a
CTE
to
list
all
employees
along
with
their
level
in
the
hierarchy
(
0
for
the
top
manager
,
1
for
their
direct
reports
,
etc
.
)
.
Solution
:
WITH
RECURSIVE
EmployeeHierarchy
AS
(
SELECT
id
,
name
,
manager_id
,
0
AS
level
FROM
employees
WHERE
manager_id
IS
NULL
UNION
ALL
SELECT
e
.
id
,
e
.
name
,
e
.
manager_id
,
eh
.
level
+
1
FROM
employees
e
INNER
JOIN
EmployeeHierarchy
eh
ON
e
.
manager_id
=
eh
.
id
)
SELECT
id
,
name
,
level
FROM
EmployeeHierarchy
;
(
,
,
,
,
,
,
=
)
,
,
;
Explanation
:
This
recursive
CTE
starts
with
the
top
-
level
manager
(
where
manager_id
is
null
)
and
recursively
joins
to
its
own
defined
result
set
to
navigate
down
the
employee
hierarchy
,
incrementing
the
level
by
one
with
each
recursion
.
2
employees
SELECT
COALESCE
TotalSales
FROM
ts
FROM
sale_amount
e.id = ts.emp_id
JOIN
sales
LEFT
0
e
AS
AS
e.name
total_sales
TotalSales
ts
AS
emp_id
SUM
total_sales
total_sales
BY
SELECT
emp_id
GROUP
ON
WITH
Problem
2
:
Summarizing
Sales
Question
:
Calculate
the
total
sales
for
each
employee
,
assuming
tables
employees
(
id
,
name
)
and
sales
(
emp_id
,
sale_amount
)
.
Solution
:
WITH
TotalSales
AS
(
SELECT
emp_id
,
SUM
(
sale_amount
)
AS
total_sales
FROM
sales
GROUP
BY
emp_id
)
SELECT
e
.
name
,
COALESCE
(
ts
.
total_sales
,
0
)
AS
total_sales
FROM
employees
e
LEFT
JOIN
TotalSales
ts
ON
e
.
id
=
ts
.
emp_id
;
(
,
(
)
)
,
(
.
,
)
;
Explanation
:
The
CTE
TotalSales
calculates
the
total
sales
per
employee
.
The
main
query
then
joins
this
CTE
with
the
employees
table
to
display
all
employees
,
including
those
with
no
sales
(
using
COALESCE
to
handle
NULLs
)
.
3
ON
emp_id
FROM
SELECT
emp_id
BY
rank
FROM
WITH
SUM
e
SalesRank
sr.rank
sale_amount
e.id = sr.emp_id
ORDER
SalesRank
e.name
DESC
sr
GROUP
AS
BY
SELECT
JOIN
RANK() OVER
employees
AS
sales
Problem
3
:
Ranking
Sales
by
Employee
Question
:
Rank
employees
based
on
their
total
sales
in
descending
order
.
Solution
:
(
,
(
(
)
)
)
,
;
WITH
SalesRank
AS
(
SELECT
emp_id
,
RANK
(
)
OVER
(
ORDER
BY
SUM
(
sale_amount
)
DESC
)
AS
rank
FROM
sales
GROUP
BY
emp_id
)
SELECT
e
.
name
,
sr
.
rank
FROM
employees
e
JOIN
SalesRank
sr
ON
e
.
id
=
sr
.
emp_id
;
Explanation
:
The
CTE
SalesRank
calculates
the
total
sales
for
each
employee
and
assigns
a
rank
based
on
these
totals
.
The
main
query
joins
this
CTE
with
the
employees
table
to
list
employees
along
with
their
sales
rank
.
4
EXTRACT
BY
MONTH
AS
year
AS
COALESCE
MonthlyGrowth
LAG
AS
previous_month_sales
FROM
AS
YEAR
EXTRACT
previous_month_sales
EXTRACT
0
growth
year
AS
sale_date
EXTRACT
total_sales
SELECT
OVER
SUM
month
FROM
MonthlySales
MONTH
total_sales
FROM
total_sales
AS
total_sales
month
MonthlySales
ORDER
MonthlyGrowth
GROUP
sale_date
SELECT
YEAR
sale_date
year
sale_amount
month
month
FROM
WITH
FROM
AS
total_sales
sales
BY
year
FROM
SELECT
sale_date
FROM
Problem
4
:
Monthly
Sales
Analysis
Question
:
Analyze
monthly
sales
growth
by
comparing
current
month's
sales
with
the
previous
month
.
Solution
:
(
(
)
,
(
)
,
(
)
(
)
,
(
)
)
,
(
,
,
,
(
)
(
,
)
)
,
,
,
(
-
,
)
;
WITH
MonthlySales
AS
(
SELECT
EXTRACT
(
YEAR
FROM
sale_date
)
AS
year
,
EXTRACT
(
MONTH
FROM
sale_date
)
AS
month
,
SUM
(
sale_amount
)
AS
total_sales
FROM
sales
GROUP
BY
EXTRACT
(
YEAR
FROM
sale_date
)
,
EXTRACT
(
MONTH
FROM
sale_date
)
)
,
MonthlyGrowth
AS
(
SELECT
year
,
month
,
total_sales
,
LAG
(
total_sales
)
OVER
(
ORDER
BY
year
,
month
)
AS
previous_month_sales
FROM
MonthlySales
)
SELECT
year
,
month
,
total_sales
,
COALESCE
(
total_sales
-
previous_month_sales
,
0
)
AS
growth
FROM
MonthlyGrowth
;
Explanation
:
Two
CTEs
are
used
here
.
MonthlySales
calculates
the
total
sales
for
each
month
.
MonthlyGrowth
then
uses
the
LAG
window
function
within
the
CTE
to
find
the
previous
month's
sales
,
and
the
main
query
calculates
the
growth
by
subtracting
the
previous
month's
sales
from
the
current
month's
.
5
sales
emp_id
COUNT(*) AS
WITH
BY
AS
3
GROUP
avg_sale_amount
FROM
AS
sale_amount
emp_id
SELECT
e.id = esc.emp_id
employees
e
SELECT
e.name
EmployeeSalesCount
num_sales
esc
JOIN
esc
AVG
ON
FROM
HAVING
avg_sale_amount
EmployeeSalesCount
COUNT(*) >
Problem
5
:
Filtered
Aggregation
Question
:
Compute
the
average
sales
per
employee
only
for
those
employees
who
made
more
than
3
sales
.
Solution
:
(
,
,
(
)
)
,
.
;
WITH
EmployeeSalesCount
AS
(
SELECT
emp_id
,
COUNT
(
*
)
AS
num_sales
,
AVG
(
sale_amount
)
AS
avg_sale_amount
FROM
sales
GROUP
BY
emp_id
HAVING
COUNT
(
*
)
>
3
)
SELECT
e
.
name
,
esc
.
avg_sale_amount
FROM
employees
e
JOIN
EmployeeSalesCount
esc
ON
e
.
id
=
esc
.
emp_id
;
Explanation
:
The
CTE
EmployeeSalesCount
calculates
the
number
of
sales
and
average
sale
amount
per
employee
,
filtering
to
include
only
those
with
more
than
three
sales
.
The
main
query
then
joins
this
CTE
to
the
employees
table
to
fetch
the
employee
names
and
their
average
sales
.
6
LIKE
employees
AS
WHERE
SELECT
name
EmployeeCTE
WITH
FROM
EmployeeCTE
'J%'
id
name
SELECT
FROM
Problem
6
:
Simple
CTE
Question
:
Create
a
CTE
that
selects
all
employees
from
the
employees
table
and
then
use
it
to
select
all
employees
whose
name
starts
with
'J'
.
Solution
:
(
,
)
*
;
WITH
EmployeeCTE
AS
(
SELECT
id
,
name
FROM
employees
)
SELECT
*
FROM
EmployeeCTE
WHERE
name
LIKE
'J%'
;
Explanation
:
This
CTE
,
EmployeeCTE
,
acts
as
a
temporary
table
containing
all
employee
records
.
The
main
query
then
filters
this
CTE
for
employees
whose
names
start
with
'J'
.
7
UNION
WITH
<
NumberSequence
number
number
WHERE
AS
SELECT
ALL
NumberSequence
AS
FROM
SELECT
NumberSequence
RECURSIVE
number + 1
10
FROM
SELECT
1
Problem
7
:
Recursive
CTE
Question
:
Write
a
recursive
CTE
to
generate
a
sequence
of
numbers
from
1
to
10
.
Solution
:
(
)
*
;
WITH
RECURSIVE
NumberSequence
AS
(
SELECT
1
AS
number
UNION
ALL
SELECT
number
+
1
FROM
NumberSequence
WHERE
number
<
10
)
SELECT
*
FROM
NumberSequence
;
Explanation
:
The
recursive
CTE
starts
with
a
base
case
(
selecting
the
number
1
)
,
then
recursively
adds
1
to
the
previous
number
until
the
number
10
is
reached
.
8
amount
FROM
sales
AS
SELECT
AS
WITH
sale_date
SalesCTE
running_total
FROM
BY
OVER
SUM
ORDER
sale_date
SELECT
amount
SalesCTE
Problem
8
:
CTE
for
Running
Total
Question
:
Use
a
CTE
to
calculate
the
running
total
of
sales
from
the
sales
table
.
Solution
:
WITH
SalesCTE
AS
(
SELECT
sale_date
,
amount
,
SUM
(
amount
)
OVER
(
ORDER
BY
sale_date
)
AS
running_total
FROM
sales
)
SELECT
*
FROM
SalesCTE
;
(
,
,
(
)
(
)
)
*
;
Explanation
:
The
CTE
calculates
the
running
total
of
sales
amounts
,
ordered
by
the
sale
date
.
The
window
function
SUM
(
)
is
used
with
the
OVER
clause
to
accumulate
the
sales
.
9
status = 'High value'
WHERE
SELECT
IN
orders
order_id
1000
FROM
SET
SELECT
HighValueOrders
FROM
total_amount
orders
UPDATE
AS
WHERE
>
WITH
HighValueOrders
order_id
order_id
Problem
9
:
CTE
in
UPDATE
Statement
Question
:
Use
a
CTE
to
update
the
status
of
orders
in
the
orders
table
where
the
total
amount
is
greater
than
1000
.
Solution
:
WITH
HighValueOrders
AS
(
SELECT
order_id
FROM
orders
WHERE
total_amount
>
1000
)
UPDATE
orders
SET
status
=
'High
value'
WHERE
order_id
IN
(
SELECT
order_id
FROM
HighValueOrders
)
;
(
)
(
)
;
Explanation
:
The
CTE
identifies
orders
with
a
total
amount
greater
than
1000
.
The
main
UPDATE
query
then
uses
this
result
to
set
the
status
of
these
orders
.
10
50000
WHEN
<
SalaryCategories
employees
THEN
THEN
salary
AND
WITH
BETWEEN
'High'
50000
FROM
END
name
AS
'Low'
FROM
WHEN
salary
ELSE
CASE
salary
id
SalaryCategories
SELECT
SELECT
100000
'Medium'
category
AS
Problem
10
:
CTE
for
Data
Partitioning
Question
:
Partition
the
employees
table
into
three
categories
based
on
salary
:
Low
,
Medium
,
and
High
.
Use
a
CTE
for
classification
.
Solution
:
WITH
SalaryCategories
AS
(
SELECT
id
,
name
,
salary
,
CASE
WHEN
salary
<
50000
THEN
'Low'
WHEN
salary
BETWEEN
50000
AND
100000
THEN
'Medium'
ELSE
'High'
END
AS
category
FROM
employees
)
SELECT
*
FROM
SalaryCategories
;
(
,
,
,
)
*
;
Explanation
:
The
CTE
classifies
each
employee
into
a
salary
category
based
on
their
salary
.
The
CASE
statement
is
used
for
conditional
logic
within
the
CTE
to
assign
categories
.
|
Créer jeu