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