Compléter
Drills to master window functions in SQL
1
FROM
SUM
BY
sale_date
amount
AS
sales
ORDER
running_total
SELECT
OVER
amount
sale_date
Problem
1
:
Calculate
Running
Total
Question
:
You
have
a
table
sales
(
sale_date
DATE
,
amount
DECIMAL
)
.
Write
a
SQL
query
to
calculate
a
running
total
of
amount
,
ordered
by
sale_date
.
Solution
:
,
,
(
)
(
)
;
2
sale_date
CURRENT
BY
AVG
FOLLOWING
PRECEDING
ROWS
BETWEEN
ORDER
ROWS
FROM
ROW
SELECT
sales
sale_date
BETWEEN
amount
3
3
BETWEEN
moving_avg
BY
sales
as
current_avg
as
amount
AND
as
ORDER
SELECT
BETWEEN
SUM
PRECEDING
AVG
sale_date
BETWEEN
AVG
amount
sale_date
running_total
AND
ORDER
ROWS
AND
sales
BY
sum_to_end
AND
OVER
amount
ROW
amount
SUM
FROM
PRECEDING
UNBOUNDED
ROW
ROWS
sales
ROW
sale_date
FOLLOWING
sale_date
OVER
CURRENT
amount
AND
FROM
amount
CURRENT
as
ROWS
BY
FROM
OVER
ROW
OVER
6
ORDER
OVER
sale_date
as
BY
sales
sale_date
amount
UNBOUNDED
CURRENT
moving_avg
CURRENT
SELECT
amount
FROM
sale_date
SELECT
ORDER
Problem
2
:
Calculate
Moving
Average
Question
:
Calculate
a
7
-
day
moving
average
of
sales
from
the
sales
table
.
Solution
:
,
,
(
)
(
)
;
Example
2
:
Fixed
Range
with
Both
PRECEDING
and
FOLLOWING
,
,
(
)
(
)
;
This
calculates
the
average
amount
using
a
window
that
includes
three
rows
before
,
the
current
row
,
and
three
rows
after
the
current
row
.
Example
3
:
From
Start
of
Data
to
Current
Row
,
,
(
)
(
)
;
This
query
computes
a
running
total
starting
from
the
first
row
in
the
partition
or
result
set
up
to
the
current
row
.
Example
4
:
Current
Row
to
End
of
Data
SELECT
sale_date
,
amount
,
(
)
(
)
;
This
sums
the
amount
from
the
current
row
to
the
last
row
of
the
partition
or
result
set
.
Example
5
:
Current
Row
Only
,
,
(
)
(
)
;
This
calculates
the
average
of
just
the
current
row's
amount
,
which
effectively
returns
the
amount
itself
.
3
total_purchases
FROM
id
RANK
name
DESC
OVER
total_purchases
SELECT
rank
customers
AS
ORDER
BY
Problem
3
:
Rank
Customers
by
Sales
Question
:
From
a
table
customers
(
id
INT
,
name
VARCHAR
,
total_purchases
DECIMAL
)
,
rank
customers
based
on
their
total_purchases
in
descending
order
.
Solution
:
,
,
,
(
)
(
)
;
Explanation
:
RANK
(
)
assigns
a
unique
rank
to
each
row
,
with
gaps
in
the
ranking
for
ties
,
based
on
the
total_purchases
in
descending
order
.
4
sales
sale_date
amount
ORDER
BY
SELECT
sale_date
ROW_NUMBER() OVER
row_num
FROM
AS
Problem
4
:
Row
Numbering
Question
:
Assign
a
unique
row
number
to
each
sale
in
the
sales
table
ordered
by
sale_date
.
Solution
:
,
,
(
)
;
Explanation
:
ROW_NUMBER
(
)
generates
a
unique
number
for
each
row
,
starting
at
1
,
based
on
the
ordering
of
sale_date
.
5
customer_id
BY
SELECT
AS
purchase_date
MIN
OVER
customer_id
first_purchase
FROM
purchases
PARTITION
Problem
5
:
Find
the
First
Purchase
Date
for
Each
Customer
Question
:
Given
a
table
purchases
(
customer_id
INT
,
purchase_date
DATE
)
,
write
a
SQL
query
to
find
the
first
purchase
date
for
each
customer
.
Solution
:
,
(
)
(
)
;
Explanation
:
MIN
(
)
window
function
is
used
here
,
partitioned
by
customer_id
so
that
the
minimum
purchase
date
is
calculated
for
each
customer
separately
.
6
1
amount
FROM
amount
change_in_amount
amount
previous_day_amount
LAG
OVER
AS
amount
sale_date
sale_date
1
ORDER
BY
sales_data
AS
BY
OVER
SELECT
sale_date
LAG
ORDER
The
LAG
function
is
very
useful
in
scenarios
where
you
need
to
compare
successive
entries
or
calculate
differences
between
them
.
For
example
,
calculating
day
-
over
-
day
sales
changes
:
SELECT
sale_date
,
amount
,
LAG
(
amount
,
1
)
OVER
(
ORDER
BY
sale_date
)
AS
previous_day_amount
,
amount
-
LAG
(
amount
,
1
)
OVER
(
ORDER
BY
sale_date
)
AS
change_in_amount
FROM
sales_data
;
,
,
(
,
)
(
)
,
-
(
,
)
(
)
;
In
this
query
,
the
change_in_amount
field
computes
the
difference
in
sales
between
consecutive
days
.
If
the
LAG
function
references
a
row
that
doesn't
exist
(
e
.
g
.
,
the
first
row
in
the
dataset
)
,
it
will
return
NULL
unless
a
default
value
is
specified
.
The
LAG
window
function
in
SQL
is
used
to
access
data
from
a
previous
row
in
the
same
result
set
without
the
need
for
a
self
-
join
.
It's
a
part
of
the
SQL
window
functions
that
provide
the
ability
to
perform
calculations
across
rows
that
are
related
to
the
current
row
.
LAG
is
particularly
useful
for
comparisons
between
records
in
ordered
data
.
How
LAG
Works
:
LAG
takes
up
to
three
arguments
:
Expression
:
The
column
or
expression
you
want
to
retrieve
from
a
preceding
row
.
Offset
:
An
optional
integer
specifying
how
many
rows
back
from
the
current
row
the
function
should
look
.
If
not
specified
,
the
default
is
1
,
meaning
the
immediate
previous
row
.
Default
:
An
optional
argument
that
provides
a
default
value
to
return
if
the
LAG
function
attempts
to
go
beyond
the
first
row
of
the
dataset
.
Syntax
:
LAG
(
expression
,
offset
,
default
)
OVER
(
[
PARTITION
BY
partition_expression
]
ORDER
BY
sort_expression
)
|
FFA Creed Paragraph 3
Complete the lyrics of the song with the words that appear on the right side