Group by or partition by multiple columns and a pair of values of a single column

user_3233810 0
Viewed 35

I have the following table in a postgres table that represents the movement of entries on and off a list:

Entry Event Timestamp
Dog Add 2021-01-01
Dog Remove 2021-01-02
Dog Add 2021-01-31
Dog Remove 2021-02-01
Cat Add 2021-01-31
Cat Remove 2021-02-01

I'd like to group the rows by the "Entry" column and "Event" column by "add"/"remove" value pairs to have a row that would denote the time that the record was on the list.

Output table:

Entry Added Removed
Dog 2021-01-01 2021-01-02
Dog 2021-01-31 2021-02-01
Cat 2021-01-31 2021-02-01

That way you could query the results of the table to see if "dog" was on the list at a given time easily with the following:

SELECT * FROM table WHERE Entry = 'Dog' AND '2021-01-01' between Added AND Removed

Looking for a little SQL help.

postgresql sql
1 Answers

You can use a subquery to get the removal date for an Add entry:

select e.entry, e.tmsp, (select min(e1.tmsp) from events e1 
     where e1.entry = e.entry and e1.event = 'Remove' and e1.tmsp > e.tmsp) 
from events e where e.event = 'Add'

See fiddle.

user_73267380
Related