Unique Chat Conversations
MediumWhatsApp is a popular messaging platform that allows users to send and receive text messages, voice notes, and multimedia messages in real-time. Users can engage in one-on-one chats or group chats, and every message sent has a unique identifier.
You're given a table, messenger_sends, with the following columns:
date: the date the message was sent.ts: the timestamp of when the message was sent.sender_id: the unique identifier for the sender of the message.receiver_id: the unique identifier for the receiver of the message.message_id: a unique identifier for each message.
Given that a conversation thread between two users A and B remains the same whether A messages B or B messages A, write a SQL query to determine how many unique conversation (unique_conversations) threads are present in the messenger_sends table.
Within the subquery:
- The
LEAST(sender_id, receiver_id)function returns the smaller of the two IDs, ensuring a consistent order. - The
GREATEST(sender_id, receiver_id)function does the opposite, returning the larger of the two IDs.
The outer query counts the distinct pairs of these ordered IDs, yielding the number of unique conversation threads. By ordering the IDs consistently, any combination of sender and receiver will always be grouped as a single unique conversation.