sql - Postgres: average user response time from interaction with a bot -

i have table stores messages between users , bot (basically state machine), , i'm trying find pairs of message/response table, in order calculate each user's average response time. caveat is, not outgoing messages response.

each row stores message_id, user_id, created_at (timestamp), state_code , outgoing (boolean).

i have been looking @ window functions, intention of using lag , lead find relevant pairs of messages , calculate difference between created_at values, averaged on each user give each user's avg. response time. problem have no way of assuring both messages issued same sate_code. ideas?

update: can assure user's message response given outgoing message if have same state code. so, example

╔════════════╦═════════╦════════════╦════════════╦══════════╗ ║ message_id ║ user_id ║ created_at ║ state_code ║ outgoing ║ ╠════════════╬═════════╬════════════╬════════════╬══════════╣ ║          1 ║      11 ║ mm/dd/yy   ║         20 ║ t        ║ ║          2 ║      11 ║ mm/dd/yy   ║         20 ║ f        ║ ║          3 ║      11 ║ mm/dd/yy   ║         22 ║ t        ║ ║          4 ║      11 ║ mm/dd/yy   ║         21 ║ t        ║ ║          5 ║      12 ║ mm/dd/yy   ║         45 ║ t        ║ ║          6 ║      12 ║ mm/dd/yy   ║         46 ║ f        ║ ║          7 ║      12 ║ mm/dd/yy   ║         46 ║ t        ║ ║          8 ║      12 ║ mm/dd/yy   ║         20 ║ f        ║ ║          9 ║      12 ║ mm/dd/yy   ║         43 ║ t        ║ ║         10 ║      13 ║ mm/dd/yy   ║         20 ║ t        ║ ╚════════════╩═════════╩════════════╩════════════╩══════════╝ 

in case pairs are, messages 1 , 2, , messages 6 , 7. nevertheless, messages 1 , 2 matter, since user 1 responding state 20 1 of our outgoing messages received while in state 20.

if understand correctly, each time outgoing false, want created_at preceding row same user_id , state_code.

i'm not sure how use windows functions this. here approach using correlated subqueries:

here 1 way:

select t.*,        (select created_at         t t2         t2.user_id = t.user_id ,               t2.state_code = t.state_code ,               t2.outgoing = 't' ,               t2.created_at < t.created_at         order t2.created_at desc         limit 1        ) prev_created_at  t 

you can date arithmetic want (mostly prev_created_at not null).

if know previous row "one before", can similar lag():

 select t.*  (select t.*,               lag(created_at) on (partition user_id, state_code order created_at) prev_created_at,               lag(outgoing) on (partition user_id, state_code order created_at) prev_outgoing        t       ) t t.outgoing = 'f' , t.prev_outgoing = 't';