Postgres 11

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

select version();


version


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



SELECT 1


CREATE TABLE tbl6 (col1 int, col2 text, dt date);
INSERT INTO tbl6(col1, col2, dt)
VALUES
    (1, 'a', '2022-07-01'),
    (2, 'b', '2022-07-03'),
    (3, 'c', '2022-07-10'),
    (4, 'd', '2022-07-11'),
    (5, 'e', '2022-07-11'),
    (6, 'f', '2022-07-13');

CREATE TABLE



INSERT 0 6


select *, 
    count(dt) over(order by dt RANGE BETWEEN INTERVAL '3 DAYS' PRECEDING AND CURRENT ROW)
from tbl6;


col1
col2
dt
count


1
a
2022-07-01
1

2
b
2022-07-03
2

3
c
2022-07-10
1

4
d
2022-07-11
3

5
e
2022-07-11
3

6
f
2022-07-13
4



SELECT 6

col1	col2	dt	count
1	a	2022-07-01	1
2	b	2022-07-03	2
3	c	2022-07-10	1
4	d	2022-07-11	3
5	e	2022-07-11	3
6	f	2022-07-13	4