I have a table with the following data:
id | client_id | message | user_id | incoming
1 | 1 | Hi, anybody there? | 2 | True
2 | 1 | I need help | 2 | True
3 | 1 | Yes, I am here to help you. | 2 | False
4 | 2 | Did you solve it yet? | 5 | True
5 | 3 | Is my issue resolved? | 5 | True
6 | 2 | yes, it is solved | 5 | False
7 | 5 | Are you happy with us? | 3 | False
8 | 5 | yes, very much | 3 | True
The clients are talking with users and incoming=True means that the message is from the client whereas False means that the user has sent the message. I want the result to be like this:
client_id | client_message | user_id | user_message
1 | Hi, anybody there? I need help | 2 | Yes, I am here to help you.
2 | Did you solve it yet? | 5 | yes, it is solved
I want the conversations attached in one row where the client has sent the message first and then the user has replied to that. Notice, that at the end, in rows 7,8 it seems like there is the conversation but since the user started it, it is not added in the result. The row with ID 5 is discarded because it doesn't have any reply.
My query currently is like this:
SELECT client_id, t1.message as client_message, user_id, t2.message as user_message
FROM conversations c1 INNER JOIN conversations c2
ON c1.client_id=c2.client_id AND c1.user_id=c2.user_id AND c1.incoming=True
but it is not resulting in a proper response. Any help will be highly appreciated. Thanks!
This is an interesting problem.
See this dbfiddle. After running it the first time, uncomment the insert for id 15 to see what dialog_id is all about.
with responses as (
select id, client_id, user_id,
row_number()
over (order by client_id, user_id, id)
as dialog_id
from conversations
where incoming = false
), dialogs as (
select c.*, min(dialog_id) as dialog_id
from conversations c
join responses r
on r.client_id = c.client_id
and r.user_id = c.user_id
and r.id >= c.id
group by c.id, c.client_id, c.message, c.user_id, c.incoming
)
select dialog_id, client_id,
array_agg(message order by id)
filter (where incoming = true)
as client_message,
user_id,
max(message)
filter (where incoming = false)
as user_message
from dialogs
group by dialog_id, client_id, user_id
order by dialog_id;
This is IMHO textbook example to demonstrate analytic functions. I assume multiple messages can repeat between same client-user pair in both directions and you want to group subsequent messages going in same direction together, utilizing id as sequence number. Possible solution might be (some rows added by myself):
with t (id, client_id, message, user_id, incoming) as (values
(1 , 1 , 'Hi, anybody there?' , 2 , True),
(2 , 1 , 'I need help' , 2 , True),
(3 , 1 , 'Yes, I am here to help you.' , 2 , False),
(4 , 2 , 'Did you solve it yet?' , 5 , True),
(5 , 3 , 'Is my issue resolved?' , 5 , True),
(6 , 2 , 'yes, it is solved' , 5 , False),
(7 , 5 , 'Are you happy with us?' , 3 , False),
(8 , 5 , 'yes, very much' , 3 , True),
(9 , 1 , 'Hi, anybody there again?' , 2 , True),
(10 , 1 , 'I need help' , 2 , True),
(11 , 1 , 'Yes, I am here to help you.' , 2 , False),
(12 , 1 , 'Again.' , 2 , False)
), ch as (
select t.*
, case coalesce(incoming !=
lag(incoming) over (
partition by client_id, user_id
order by id
)
, true)
when true then 1
else 0
end as incoming_changed
from t
), groups as (
select ch.*
, sum(incoming_changed) over (
partition by client_id, user_id
order by id
) as grp
from ch
), grouped as (
select client_id
, string_agg(message, ' ') as message
, user_id
, incoming
, min(id) as min_id
from groups
group by client_id, user_id, grp, incoming
order by client_id, user_id, min_id
), paired as (
select grouped.*
, lead(min_id) over (
partition by client_id, user_id
order by min_id
) as response_id
from grouped
)
select pc.client_id
, pc.message as client_message
, pu.user_id
, pu.message as user_message
from paired pc
join paired pu on pc.response_id = pu.min_id
where pc.incoming
Explanation: first detect when conversation direction changes. It divides rows into groups. Messages are aggregated within each group (see CTE select * from grouped). Then pair each client's message with user response (if any) which is the next by id (I hope this column has a role of timestamp).