PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Finding the average waiting time in a chat conversation

I would like to find out the average time that a client waits on a response from a project manager in a chat conversation.

I need to make sure it only calculates the waiting time for the last client message in a row. E.g. the client writes five messages in a row and then the account manager answers two hours later, then we should ignore the first four messages from the client.

I am also only interested in project conversations, so I need to ignore task conversations.

We are using PostgreSQL 14.7.

The tables looks like this (simplified):

users

id role
153 account_manager
157 client

role is either client or account_manager

conversations

id object_type
12 project
13 task

object_type is either project or task

messages

id user_id conversation_id created_at
1133 157 12 2023-04-19 13:05:48
1157 157 12 2023-04-19 13:06:00
1163 157 12 2023-04-19 13:15:00
1178 153 12 2023-04-19 14:15:00
1209 157 12 2023-04-20 10:23:00
1327 153 12 2023-04-20 14:23:00

In the example above the average time would be (1+4)/2= 2,5 hours

I would also like to know how that number changes over time, so I would need it grouped per month.

Thanks a lot for any help on this!

I have tried various approaches without success.

Tags:

2

Answers


  1. 0

    Take rows where with role account_amanger and previous role client, use lag(). Then count average:

    select avg (created_at - prev_time)
from (
  select m.*, u.role, lag(u.role) over (order by created_at) prev_role,
         lag(created_at) over (order by created_at) prev_time
  from messages m join users u on u.id = m.user_id
  where m.conversation_id = 12) m
where role = 'account_manager' and prev_role = 'client'

    dbfiddle demo

    Login or Signup to reply.
  2. 0

    Here is the test data :

    create table users (id integer, role varchar(50));

insert into users values
(153, 'account_manager'),
(157, 'client');

-- select * from users;

create table conversations (id integer, object_type varchar(50));

insert into conversations values
(12, 'project'),
(13, 'task');

-- select * from conversations;

create table messages (id integer, user_id integer, conversation_id integer,
created_at timestamp);

insert into messages values
(1133,  157,    12, '2023-04-19 13:05:48'),
(1157,  157,    12, '2023-04-19 13:06:00'),
(1163,  157,    12, '2023-04-19 13:15:00'),
(1178,  153,    12, '2023-04-19 14:15:00'),
(1209,  157,    12, '2023-04-20 10:23:00'),
(1327,  153,    12, '2023-04-20 14:23:00');

-- select * from messages;

    And now the query :

    with user_messages as (
     select * from messages
     where user_id = (select id from users where role = 'client' limit 1)),
manager_messages as (
     select * from messages
     where user_id = (select id from users where role = 'account_manager' limit 1))
select avg(tm.created_at - tu.created_at)
from manager_messages tm LEFT OUTER JOIN user_messages tu
on tu.created_at = (select coalesce(max(um.created_at),tm.created_at)
                       from user_messages um
                       where um.created_at < tm.created_at);

    avg
----------
 02:30:00
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search