[SQL] `lag()` with `over` and `range between` returns a value if even the previous record is...

I want to get the previous value using lag(), over a partition defined with RANGE BETWEEN. I followed an example from the documentation:

WITH orders (custkey, orderdate, totalprice)
AS
(
VALUES
('cust_1', DATE '2020-10-10', 100),
('cust_2', DATE '2020-10-10', 15),
('cust_1', DATE '2020-10-15', 200),
('cust_1', DATE '2020-10-16', 240),
('cust_2', DATE '2020-12-20', 25),
('cust_1', DATE '2020-12-25', 140),
('cust_2', DATE '2021-01-01', 5)
)

SELECT *,
avg(totalprice) OVER (
PARTITION BY custkey
ORDER BY orderdate
RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND CURRENT ROW) AS past_month_avg,

lag(totalprice) OVER ( PARTITION BY custkey
ORDER BY orderdate
RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND CURRENT ROW) AS previous_total_price_within_last_month
FROM orders


-- custkey|orderdate |totalprice|past_month_avg|previous_total_price_within_last_month|
-- -------+----------+----------+--------------+--------------------------------------+
-- cust_2 |2020-10-10| 15| 15.0| NULL|
-- cust_2 |2020-12-20| 25| 25.0| 15|
-- cust_2 |2021-01-01| 5| 15.0| 25|
-- cust_1 |2020-10-10| 100| 100.0| NULL|
-- cust_1 |2020-10-15| 200| 150.0| 100|
-- cust_1 |2020-10-16| 240| 180.0| 200|
-- cust_1 |2020-12-25| 140| 140.0| 240|


Whereas the calculation for past_month_avg works as expected, the previous_total_price_within_last_month result is not as expected.

Expected Output

Given that I defined a window that ranges at the last month, I expect that lag() will return null if the "previous" totalprice value is out of the window.

-- custkey|orderdate |totalprice|past_month_avg|previous_total_price_within_last_month|
-- -------+----------+----------+--------------+--------------------------------------+
-- cust_2 |2020-10-10| 15| 15.0| NULL|
-- cust_2 |2020-12-20| 25| 25.0| NULL| <~~
-- cust_2 |2021-01-01| 5| 15.0| 25|
-- cust_1 |2020-10-10| 100| 100.0| NULL|
-- cust_1 |2020-10-15| 200| 150.0| 100|
-- cust_1 |2020-10-16| 240| 180.0| 200|
-- cust_1 |2020-12-25| 140| 140.0| NULL| <~~

Continue reading...