Get the first price on each quarter from a pricing table

Question

I have a pricing table which has these fields:

id
start_date
price

I'd like to get the first price of each Quarter, how can I achieve this?

I tried this:

SELECT id,price,EXTRACT(YEAR FROM start_date::TIMESTAMP) AS year, EXTRACT(QUARTER FROM start_date::TIMESTAMP) AS quarter,min(start_date) as start_date
FROM pricing
GROUP BY id,price,EXTRACT(YEAR FROM start_date::TIMESTAMP), EXTRACT(QUARTER FROM start_date::TIMESTAMP)

but I'm getting all the Quarter prices if the price changed, I just need the first price of each quarter.

Thanks!

Stefanov.sm · Answer 1 · 2023-01-22T16:38:08.607

1

Use distinct on with custom order.

select distinct on (quarter) 
    to_char(start_date, 'yyyy"Q"q') as quarter,* 
from pricing
order by quarter,start_date;

For the last price of each quarter change the order to order by quarter,start_date desc.

edited Jan 22 '23 at 16:38

answered Jan 22 '23 at 16:06

Stefanov.sm

11,215
2
21
21

Jim Jones · Answer 2 · 2021-03-17T12:13:35.500

There are maybe hundreds of ways to achieve it. You could start by creating the quarters you need in a CTE or Subquery, so that you can compare it to your pricing table, e.g.

Sample data

CREATE TEMPORARY TABLE pricing (id INT, start_date DATE,price NUMERIC);
INSERT INTO pricing VALUES 
  (5,CURRENT_DATE,1.99),
  (4,CURRENT_DATE-2,0.99),
  (3,CURRENT_DATE-3,0.42),
  (2,CURRENT_DATE-100,0.10),
  (1,CURRENT_DATE-121,0.82);

Query

WITH q (year,quarter) AS (
  SELECT DISTINCT
    EXTRACT(YEAR FROM start_date::TIMESTAMP),
    EXTRACT(QUARTER FROM start_date::TIMESTAMP) 
  FROM pricing
)
SELECT *, (SELECT price 
           FROM pricing 
           WHERE EXTRACT(QUARTER FROM start_date::TIMESTAMP) = q.quarter AND
                 EXTRACT(YEAR FROM start_date::TIMESTAMP) = q.year
           ORDER BY start_date LIMIT 1) 
FROM q ORDER BY q.year,q.quarter;

 year | quarter | price 
------+---------+-------
 2020 |       4 |  0.82
 2021 |       1 |  0.42

Demo: db<>fiddle

EDIT: Depending on your use case you might wanna generate the years and quarters using something like generate_series rather than running a full scan on pricing as shown in the example above, e.g.

SELECT 
  EXTRACT(YEAR FROM d) AS year,
  EXTRACT(QUARTER FROM d) AS quarter
FROM generate_series('2019-01-01'::DATE,CURRENT_DATE,'3 month') AS d
ORDER BY 1,2;

 year | quarter 
------+---------
 2019 |       1
 2019 |       2
 2019 |       3
 2019 |       4
 2020 |       1
 2020 |       2
 2020 |       3
 2020 |       4
 2021 |       1

Get the first price on each quarter from a pricing table

2 Answers2