-1

I have a table in PostgreSQL and I'd like to get, for each row, the number of rows that are within a period if 30 days before the date of each row (including the event of the row at each moment). Example:

Event.         Date

A.                 2019-02-13

B.                 2019-02-15

C.                 2019-03-17

D.                 2019-03-19

E.                 2019-04-22

F.                  2019-04-23

G.                 2019-04-30

Expected output:

Event.         Date.                 Events occurred in last month

A.                 2019-02-13.     1

B.                 2019-02-15.     2

C.                 2019-03-17.     1

D.                 2019-03-19.     2

E.                 2019-04-22.     1

F.                  2019-04-23.     2

G.                 2019-04-30.     3

For example, for event B there are two events in last month: B itself and A, because it happened less than thirty days from 2019-02-15. For event G there are three events, because from the last month (from 2019-03-30) there have been three events (G itself, E and F).

How could I implement this in a PostgreSQL query?

Regards

M. Kanarkowski
  • 2,155
  • 8
  • 14
belfastcowboy24
  • 147
  • 2
  • 7
  • In your example data there is one entry per event. Have a look into `count` and `group by` – ideam Jun 05 '19 at 20:13

1 Answers1

1

Use window functions with a windowing clause:

select e.*,
       count(*) over (order by date range between interval '30 day' preceding and current row)
from events e;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786