Same First and Last Reply Target

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

General Assembly

Problem

We're auditing how each channel's conversations open and close over time. For every channel, one calendar month at a time, return the recipient of the first message and the recipient of the last message, skipping any message that wasn't addressed to someone; a message's recipient is its reply_to target. Sort the channels alphabetically and the months from earliest to latest.

They started and ended the month messaging the same person.

Tables

chat_msgs

Columns: msg_id INTEGER, channel TEXT, sender_id INTEGER, content TEXT, msg_type TEXT, sent_at TEXT, edited INTEGER, reply_to INTEGER

Expected output

Worked solution and explanation

What this problem is really testing

Beneath the audit framing this is a first-and-last-by-time pick inside each group, and the trap is that the obvious tool gets it wrong. MIN(reply_to) and MAX(reply_to) over a GROUP BY channel, month hand you the smallest and largest recipient id, which has nothing to do with who was messaged first or last. The earliest and latest recipient only fall out of FIRST_VALUE(reply_to) and LAST_VALUE(reply_to) ordered by sent_at, and LAST_VALUE needs a frame that reaches the end of the partition or it returns each row's own recipient. Get the frame wrong and every last-recipient value is quietly the current row, so almost nothing in the output is right.

Walking the solution

Step 1: Drop messages with no recipient

Filter reply_to IS NOT NULL first. Roughly seven in ten messages here are addressed to no one, and if one of them is the earliest or latest in a channel-month, an unfiltered FIRST_VALUE or LAST_VALUE picks up its NULL and reports a phantom recipient.

Step 2: Bucket by channel and calendar month

sent_at is a full timestamp, so partitioning on it raw would put every second in its own bucket. Take substr(sent_at, 1, 7) to key on the YYYY-MM prefix and partition by channel plus that month.

Step 3: Pick the first and last recipient by time

Order each partition by sent_at and read FIRST_VALUE(reply_to) and LAST_VALUE(reply_to). Both windows carry the full ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame so every row in the group agrees on the same first and last recipient.

Step 4: Collapse to one row per group

The window functions tag every surviving message with its group's first and last recipient, so a channel-month with five messages yields five identical rows. DISTINCT folds them to the one row per channel and month you actually want, then sort by channel and month.

The solution

SELECT DISTINCT
  channel,
  substr(sent_at, 1, 7) AS msg_month,
  FIRST_VALUE(reply_to) OVER (
    PARTITION BY channel, substr(sent_at, 1, 7)
    ORDER BY sent_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_recipient,
  LAST_VALUE(reply_to) OVER (
    PARTITION BY channel, substr(sent_at, 1, 7)
    ORDER BY sent_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_recipient
FROM chat_msgs
WHERE reply_to IS NOT NULL
ORDER BY channel, msg_month

Plain MIN/MAX aggregate

MIN(reply_to), MAX(reply_to) grouped by channel, month returns the lowest and highest recipient id. On this data that is often a different person from whoever opened or closed the month, so the answer is simple and wrong.

Ordered `FIRST_VALUE`/`LAST_VALUE`

FIRST_VALUE and LAST_VALUE ordered by sent_at return the recipient of the chronologically first and last message. This is the only form that matches 'first and last', and the frame on LAST_VALUE is what makes it correct.

Common follow-up questions

• What changes if you also need the sender of that first and last message, not just the recipient? (Tests whether they reach for a second pair of windows on `sender_id` or a join back to `chat_msgs`.)
• How would you keep only the channel-months where the first and last recipient are the same person? (Brings back the sameness idea as a post-filter comparing the two window columns.)
• `sent_at` is stored as text here. What breaks in the month bucket if it were a genuine timestamp column in another engine? (Probes date-vs-timestamp handling and whether the `substr` month trick survives a real timestamp type.)