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