How to have the min, max and latest price with the time it came in one row for each symbol? (postgres)

This is quite easy with window functions – you just need to set the proper ordering:

SELECT DISTINCT symbol,
FIRST_VALUE(tradetime) OVER(PARTITION BY symbol ORDER BY price,tradetime DESC)      AS minTrade,
FIRST_VALUE(price)     OVER(PARTITION BY symbol ORDER BY price,tradetime DESC)      AS minPrice,
FIRST_VALUE(tradetime) OVER(PARTITION BY symbol ORDER BY price DESC,tradetime DESC) AS maxTrade,
FIRST_VALUE(price)     OVER(PARTITION BY symbol ORDER BY price DESC,tradetime DESC) AS maxPrice,
FIRST_VALUE(tradetime) OVER(PARTITION BY symbol ORDER BY tradetime DESC)            AS lastTrade,
FIRST_VALUE(price)     OVER(PARTITION BY symbol ORDER BY tradetime DESC)            AS lastPrice
FROM myTable

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top