Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE coin_history (
  id SERIAL PRIMARY KEY, 
  coin_id INT NOT NULL, 
  exchange_rate FLOAT NOT NULL, 
  timestamp TIMESTAMP
);

CREATE TABLE


INSERT INTO coin_history(coin_id, exchange_rate, timestamp)
VALUES 
  (1, 1.3, '2007-01-01 00:00:00'), 
  (1, 2.3, '2007-01-02 00:00:00'), 
  (1, 3.3, '2007-01-12 00:00:00'),
  (1, 4.3, '2007-02-01 00:00:00'), 
  (1, 5.3, '2007-02-12 00:00:00'), 
  (2, 1.3, '2007-01-01 00:00:00'), 
  (2, 2.3, '2007-01-12 00:00:00'), 
  (2, 3.3, '2007-02-01 00:00:00'), 
  (2, 4.3, '2007-02-12 00:00:00'), 
  (3, 1.3, '2007-01-01 00:00:00'), 
  (3, 2.3, '2007-01-12 00:00:00'),
  (3, 3.3, '2007-02-01 00:00:00'), 
  (3, 4.3, '2007-02-12 00:00:00'),
  (3, 5.3, '2008-01-01 00:00:00'), 
  (3, 6.3, '2008-01-12 00:00:00');

INSERT 0 15


SELECT 
  id,
  coin_id,
  AVG(exchange_rate) OVER (PARTITION BY coin_id, DATE_TRUNC('WEEK', timestamp)) AS by_week,
  AVG(exchange_rate) OVER (PARTITION BY coin_id, DATE_TRUNC('MONTH', timestamp)) AS by_month,
  AVG(exchange_rate) OVER (PARTITION BY coin_id, DATE_TRUNC('YEAR', timestamp)) AS by_year
FROM coin_history
ORDER BY id


id
coin_id
by_week
by_month
by_year


1
1
1.7999999999999998
2.3
3.3

2
1
1.7999999999999998
2.3
3.3

3
1
3.3
2.3
3.3

4
1
4.3
4.8
3.3

5
1
5.3
4.8
3.3

6
2
1.3
1.7999999999999998
2.8

7
2
2.3
1.7999999999999998
2.8

8
2
3.3
3.8
2.8

9
2
4.3
3.8
2.8

10
3
1.3
1.7999999999999998
2.8

11
3
2.3
1.7999999999999998
2.8

12
3
3.3
3.8
2.8

13
3
4.3
3.8
2.8

14
3
5.3
5.8
5.8

15
3
6.3
5.8
5.8



SELECT 15

id	coin_id	by_week	by_month	by_year
1	1	1.7999999999999998	2.3	3.3
2	1	1.7999999999999998	2.3	3.3
3	1	3.3	2.3	3.3
4	1	4.3	4.8	3.3
5	1	5.3	4.8	3.3
6	2	1.3	1.7999999999999998	2.8
7	2	2.3	1.7999999999999998	2.8
8	2	3.3	3.8	2.8
9	2	4.3	3.8	2.8
10	3	1.3	1.7999999999999998	2.8
11	3	2.3	1.7999999999999998	2.8
12	3	3.3	3.8	2.8
13	3	4.3	3.8	2.8
14	3	5.3	5.8	5.8
15	3	6.3	5.8	5.8