CODEWITHSUNDEEP
sqlpostgresql
MattnDo
MattnDo

Reputation: 444

postgresql - condition met in a time interval

I have the following table and I want to create the condition_met column. The condition_met column is my expected output.

            timestamp  | client_id| type_id | prospect_id | condition_met
'2015-06-13 13:45:58'  | 240      | 34.     | 480         |TRUE
'2015-06-13 13:45:59'  | 480      | 56.     | 480         |FALSE
'2015-06-13 13:46:00'  | 480      | 76.     | 480         |FALSE
'2015-06-13 13:46:01'  | 480      | 76.     | 480         |FALSE
'2015-06-13 13:46:02'  | 240      | 35.     | 240         |FALSE
'2015-06-13 13:47:01'  | 480      | 34.     | 240         |FALSE
'2015-06-13 13:47:03'  | 240      | 56.     | 240         |FALSE
'2015-06-13 13:47:04'  | 240      | 76.     | 240         |FALSE
'2015-06-13 13:47:06'  | 240      | 76.     | 240         |FALSE
'2015-06-13 13:47:09'  | 480      | 98.     | 480         |FALSE
 ...

Condition_met is TRUE when type_id = 34 and within 5 seconds after this type_id, the client_id of type_id = 34 becomes also prospect_id.

To say it differently: for each type_id = 34 the client_id that performed action type_id = 34 needs to become prospect_id in 5 seconds

Upvotes: 0

Views: 242

Answers (1)

Philippe
Philippe

Reputation: 1827

Does this query OK for you ? (Resul here)

with operations as (
select *,lead(client_id,1) over(order by client_id,timestamp_op),lead(prospect_id,1) over(order by client_id,timestamp_op),timestamp_op + interval '5s',
case 
    when type_id = 34 and lead(client_id,1) over(order by client_id,timestamp_op) = client_id 
    and lead(client_id,1) over(order by client_id,timestamp_op) = lead(prospect_id,1) over(order by client_id,timestamp_op) 
    and lead(timestamp_op,1) over(order by client_id,timestamp_op) <=timestamp_op + interval '5s' then true 
    else false 
end as condition_met
from operation
order by 2,1
)
select timestamp_op,client_id,type_id,prospect_id,condition_met from operations order by timestamp_op

Upvotes: 2

Related Questions