Unique Chat Conversations
WhatsApp 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.
SELECT COUNT(DISTINCT least_value || '_' || greatest_value) AS unique_conversations
FROM (
SELECT
CASE WHEN sender_id < receiver_id THEN sender_id ELSE receiver_id END AS least_value,
CASE WHEN sender_id > receiver_id THEN sender_id ELSE receiver_id END AS greatest_value
FROM messenger_sends
) AS subquery;
SELECT COUNT(*) unique_conversations FROM messenger_sends WHERE sender_id < receiver_id