Get entries with most recent timestamp per month

Question

I have a table prices that stores prices for various products as they update throughout the day, structured as below:

+---------+--------+-------------------+
| product | price  | creationTimestamp |
+---------+--------+-------------------+
| A       | 34.234 | 30/10/20 01:48:48 |
| A       | 34.598 | 30/10/20 21:43:12 |
| A       | 33.129 | 31/10/20 08:52:58 |
| B       | 13.440 | 31/10/20 15:22:01 |
| B       | 12.914 | 31/10/20 23:24:42 |
| C       |  1.494 | 31/10/20 04:36:00 |
+---------+--------+-------------------+

What I would like to do is for the past 5 years, extract the last price for each product, per month based on the most recent create timestamp. e.g.

+----------------+---------+-----------------+
| endOfMonthDate | product | endOfMonthPrice |
+----------------+---------+-----------------+
| 31/10/2020     | A       |          33.129 |
| 31/10/2020     | B       |          12.914 |
| 31/10/2020     | C       |           1.494 |
+----------------+---------+-----------------+

(this only displays the last month as an example, but i want to show prices for the preceding 5 years)

My query so far is

select 
*
from
prices p1
where creationTimestamp >= sysdate - interval '5' year
and creationTimestamp in (select max(creationTimestamp) from prices p2 where p1.product = p2.prices)

which returns the most recent timestamp price per product. However I am unsure how to group this expression per month and year. can anybody help correct my query?

Answer 1

You can use window functions:

select p.*
from (select p.*,
             row_number() over (partition by product, trunc(creationTimestamp, 'MON')
                                order by creationTimestamp desc
                               ) as seqnum
      from prices p
      where creationTimestamp >= sysdate - interval '5' year
     ) p
where seqnum = 1;