Resolved: SQLite Window functions

Question:

This is a simplified ER diagram of my database:

What I’d like to retrieve is, for each vendor_item:

• The highest price (excluding the last capture)
• The lowest price (excluding the last capture)
• The current price (i.e. the last capture)

This is some sample data of the PRICE_DATA table to give you an idea:

This is the SQL statement I have so far:

select distinct vendor_item_id
      ,last_value(price) over win as curr_price
      ,min(price) over win as low_price
      ,max(price) over win as high_price
from price_data
window win as (partition by vendor_item_id 
               order by capture_ts 
               rows between unbounded preceding 
                        and unbounded following);

While this gives me more or less what I’m looking for, there are a couple of issues:

• The highest and lowest price take into account all records, instead of excluding the most recent capture.

• If I don’t add distinct to the query, I end up with duplicate records (this is probably my fault, for failing to properly grasp the windowing functionality).

Desired result:

Thanks for your help!

Answer:

Use a CTE that returns the max capture_ts for each vendor_item_id and then get low_price and high_price with conditional aggregation:

WITH cte AS (
  SELECT *, MAX(capture_ts) OVER (PARTITION BY vendor_item_id) max_capture_ts
  FROM price_data
)
SELECT DISTINCT vendor_item_id,
       FIRST_VALUE(price) OVER (PARTITION BY vendor_item_id ORDER BY capture_ts DESC) curr_price,
       MIN(CASE WHEN capture_ts < max_capture_ts THEN price END) OVER (PARTITION BY vendor_item_id) low_price, 
       MAX(CASE WHEN capture_ts < max_capture_ts THEN price END) OVER (PARTITION BY vendor_item_id) high_price
FROM cte;

See the demo.

If you have better answer, please add a comment about this, thank you!