Postgres 13

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

select version();


version


PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table stock_1min(stock_id int , open int , date_value timestamp);
insert into stock_1min values
(1,10,'2020-12-01 09:31'),
(1,12,'2020-12-01 09:46'),
(1,14,'2020-12-02 09:31'),
(1,20,'2020-12-02 09:46'),
(2,14,'2020-12-01 09:31'),
(2,16,'2020-12-01 09:46'),
(2,2,'2020-12-02 09:31'),
(2,16,'2020-12-02 09:46');
8 rows affected

select * from stock_1min;


stock_id
open
date_value


1
10
2020-12-01 09:31:00

1
12
2020-12-01 09:46:00

1
14
2020-12-02 09:31:00

1
20
2020-12-02 09:46:00

2
14
2020-12-01 09:31:00

2
16
2020-12-01 09:46:00

2
2
2020-12-02 09:31:00

2
16
2020-12-02 09:46:00


WITH 
day_min_time AS
(
         SELECT   Min(date_value)  min_time,
                  Date(date_value) dt,
                  stock_id
         FROM     stock_1min
         GROUP BY Date(date_value),
                  stock_id ), 
day_max_time AS
(
         SELECT   Max(sd.date_value)  max_time ,
                  Date(sd.date_value) dt,
                  sd.stock_id
         FROM     stock_1min sd
         JOIN     day_min_time dmt
         ON       sd.stock_id = dmt.stock_id
         AND      Date(sd.date_value) = dmt.dt
         WHERE    sd.date_value <= dmt.min_time + interval '15 minutes'
         GROUP BY date(sd.date_value) ,
                  sd.stock_id )
SELECT   round(avg(price_difference)) average_difference,
         stock_id
FROM     (
                    SELECT     max(sd.OPEN)-min(sd.OPEN) price_difference,
                               sd.stock_id ,
                               date(sd.date_value)
                    FROM       stock_1min sd
                    INNER JOIN day_min_time min_dmt
                    ON         min_dmt.stock_id = sd.stock_id
                    AND        date(sd.date_value) = min_dmt.dt
                    INNER JOIN day_max_time max_dmt
                    ON         max_dmt.stock_id = sd.stock_id
                    AND        date(sd.date_value) = max_dmt.dt
                    WHERE      sd.date_value BETWEEN min_dmt.min_time AND        max_dmt.max_time
                    GROUP BY   sd.stock_id ,
                               date(sd.date_value) ) final_inline_view
GROUP BY stock_id;


average_difference
stock_id


4
1

8
2

stock_id	open	date_value
1	10	2020-12-01 09:31:00
1	12	2020-12-01 09:46:00
1	14	2020-12-02 09:31:00
1	20	2020-12-02 09:46:00
2	14	2020-12-01 09:31:00
2	16	2020-12-01 09:46:00
2	2	2020-12-02 09:31:00
2	16	2020-12-02 09:46:00