how can i find unique conversation from table in sql

I Have this table:

[Messages table]

I need to find the number of uniqe conversation – conversation is define as senderid sent msg to reciverid, and reciverid has replied (no matter how many times or the thread length, it will be count as 1 conversation). so if senderid = 1, reeiver id =2 and in the next row senderid = 2 and reciever id =1 this is one conversation (till the end of time) Im really stock and not sure how to proceed.

Thanks!

Answer

You can use the functions LEAST() and GREATEST() to create unique combinations of the 2 ids and aggregate:

SELECT COUNT(DISTINCT LEAST(m1.senderid, m1.receiverid), GREATEST(m1.senderid, m1.receiverid)) counter
FROM Messages m1
WHERE EXISTS (SELECT 1 FROM Messages m2 WHERE (m2.receiverid, m2.senderid) = (m1.senderid, m1.receiverid))

See the demo.
Results (for your sample data):

counter
2