StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPostgres: average user response time from interaction with a bot
text
Body
copied!<p>I have a table that stores all messages between users and a bot (basically a state machine), and I'm trying to find all pairs of message/response from this table, in order to calculate each user's average response time. The caveat is, that not all outgoing messages get a response. </p> <p>Each row stores message_id, user_id, created_at (timestamp), state_code and outgoing (boolean).</p> <p>I have been looking at window functions, with the intention of using lag and lead to find the relevant pairs of messages and then calculate the difference between their created_at values, which averaged over each user would give us each user's avg. response time. The problem with this is that I have no way of assuring that both messages were issued with the same sate_code. Ideas?</p> <p><strong>UPDATE:</strong> you can assure that a user's message is a response to a given outgoing message if they have the same state code. So, for example</p> <pre><code>╔════════════╦═════════╦════════════╦════════════╦══════════╗ ║ 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 ║ ╚════════════╩═════════╩════════════╩════════════╩══════════╝ </code></pre> <p>In this case the pairs are, messages 1 and 2, and messages 6 and 7. Nevertheless, only messages 1 and 2 matter, since user 1 is responding from state 20 to one of our outgoing messages received while in state 20.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload