Compléter
Drills to master window functions in SQL
1
FROM
running_total
sale_date
AS
ORDER
OVER
amount
sales
SUM
sale_date
SELECT
BY
amount
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
sales
ROW
CURRENT
ORDER
ROWS
sale_date
sale_date
sales
SUM
amount
sale_date
amount
ROWS
BY
ROW
BY
FROM
6
AVG
SUM
PRECEDING
ROWS
SELECT
OVER
sale_date
SELECT
ROWS
ROW
moving_avg
OVER
sum_to_end
CURRENT
sales
ROW
amount
sale_date
BY
AND
amount
SELECT
UNBOUNDED
BETWEEN
ROW
FOLLOWING
3
ROWS
FROM
sales
OVER
CURRENT
AND
AND
sales
sale_date
AVG
sale_date
current_avg
OVER
amount
BETWEEN
moving_avg
amount
as
ORDER
amount
FROM
CURRENT
amount
CURRENT
sale_date
FROM
BY
OVER
PRECEDING
SELECT
PRECEDING
ORDER
amount
as
BY
BETWEEN
AND
sale_date
as
BETWEEN
BETWEEN
running_total
AVG
as
UNBOUNDED
ORDER
FOLLOWING
3
ORDER
AND
FROM
as
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
RANK
OVER
DESC
name
total_purchases
AS
SELECT
rank
ORDER
customers
BY
FROM
id
total_purchases
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
BY
sale_date
ORDER
SELECT
AS
amount
sale_date
FROM
row_num
sales
ROW_NUMBER() OVER
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
FROM
purchase_date
first_purchase
AS
SELECT
purchases
OVER
customer_id
MIN
BY
PARTITION
customer_id
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
AS
LAG
1
ORDER
sale_date
1
amount
change_in_amount
previous_day_amount
amount
SELECT
amount
amount
BY
sales_data
ORDER
OVER
FROM
sale_date
OVER
sale_date
AS
LAG
BY
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