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