CODEWITHSUNDEEP
postgresqlwindow-functionscase-when
Tom Tom
Tom Tom

Reputation: 352

Any better way to Return 1 value for multiple row in case when by window Function?

data like this:

timestamp id  roas 
day 5     id1  1.3
day 4     id1  2.1
day 3     id1  1.9
day 2     id1  1.4
day 1     id1  3.0

I want to get data of the first day when roas < = 1.5 for all row have 'id1' that is day 2, desired result:

timestamp id  roas  min_date
day 5     id1  1.3  day 2
day 4     id1  2.1  day 2
day 3     id1  1.9  day 2
day 2     id1  1.4  day 2
day 1     id1  3.0  day 2

Currently, I use:

, t2 as (select *, case when roas < = 1.5 then timestamp else null end as day_roas from t1)
select *, min(day_roas) over (partition by id) as min_date
from t2

Upvotes: 0

Views: 95

Answers (1)

Bergi
Bergi

Reputation: 664589

You can use a FILTER clause with your window function:

SELECT *, min(timestamp) FILTER (WHERE roas <= 1.5) OVER (PARTITION BY id) FROM t1

(online demo)

Upvotes: 1

Related Questions