Another SQL question that has me stumped. Does this require a self-join?

Question

Here's the question from an interview I'm prepping for...

Given a table messenger_sends: date | ts | sender_id | receiver_id| message_id | has_reaction

Q: how many unique conversation threads are there ?

I'm not even sure where to start!!

Answer 1

Here is one solution

create table table_name (sender_id int, receiver_id int);
insert into table_name values
(1,2),(1,2),(2,1),(1,3);

select count(ids)
from
(select distinct case when sender_id < receiver_id 
then concat(sender_id ,receiver_id) 
else concat(receiver_id, sender_id) end ids
from table_name) sub_query;

| (No column name) |
| ---------------: |
|                2 |

db<>fiddle here

Answer 2

You want to count the number of distinct {sender, receiver} sets -- not pairs because the order is not important.

For me this translates into:

SELECT COUNT(*)
FROM (
    SELECT DISTINCT a, b
    FROM (
        SELECT
            CASE WHEN sender_id < receiver_id THEN sender_id ELSE receiver_id END AS a, -- the smaller id
            CASE WHEN sender_id < receiver_id THEN receiver_id ELSE sender_id END AS b -- the larger id
            FROM Messages
        ) T
) U