PostgreSQL: RANGE BETWEEN INTERVAL '10 DAY' AND CURRENT ROW

Question

I have a table which stores, for every item, the daily price. If the price hasn't been updated, there isn't a record for that item on that day.

I need to write a query which retrieves, for every item, the most recent price with a lookback window of 10 days from the current row date otherwise return NULL. I was thinking to achieve that using a RANGE BETWEEN INTERVAL statement. Something like:

SELECT
    DATE(datetime),
    item_id,
    LAST(price) OVER(
        PARTITION BY item_id
        ORDER BY datetime DESC
        RANGE BETWEEN INTERVAL '10 DAYS' AND CURRENT ROW
    ) AS most_recent_price_within_last_10days
FROM ...
GROUP BY
    date,
    item_id,
    price

Unfortunately this query raises an error:

LINE 20:  RANGE BETWEEN INTERVAL '10 DAY' PRECEDING AND CURRENT ROW
          ^

I came across an old blog post saying such operation is not possible in Postgres. Is this still accurate?

Answer 1

You could use ROW_NUMBER() to pull out the most recent record within the last 10 days for each item:

SELECT * 
FROM (
    SELECT
        DATE(datetime),
        item_id,
        price AS most_recent_price_within_last_10days,
        ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY datetime DESC) rn
    FROM ...
    WHERE datetime > NOW() - INTERVAL '10 DAY'
) x WHERE rn = 1

In the subquery, the WHERE clause does the filtering on the date range; ROW_NUMBER() assigns a rank to each record within groups of records having the same item_id, with the most recent record first. Then, the outer query just filters in records having row number 1.

Answer 2

One method is to use LAG() and some comparison:

(CASE WHEN LAG(datetime) OVER (PARTITION BY item_id ORDER BY datetime) > datetime - interval '10 days'
      THEN LAG(price) OVER (PARTITION BY item_id ORDER BY datetime)
 END) as most_recent_price_within_last_10days

That is, the price you are looking for is on the preceding row. The only question is whether the date on that row is recent enough.