Days with More Edited Than Unedited Messages

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

L4

Asked in

Google

Problem

A team chat product's admin view flags the days when message editing ran hot. From the message log, return every field of every message posted on days where more messages were edited than were left unedited.

Some days, more messages get edited than sent.

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 really tests

Beneath the chat-analytics framing this is a per-day conditional aggregation with a rejoin. The real question: can you compare two counts computed over the SAME group (edited versus unedited within one calendar date), then hand back the raw messages instead of the summary? Anyone can GROUP BY a date. The separator is expressing 'edited outnumbered unedited' as two conditional sums in HAVING, and then remembering the ask was for the original rows, so you must join the qualifying dates back to chat_msgs. Miss that rejoin and you return per-day counts nobody asked for; use >= and you leak the days where edits merely tied.

Break down the requirements

Step 1: Collapse to calendar day

sent_at is a timestamp, so collapse it to a calendar date with DATE(sent_at) and GROUP BY that. Grouping on the raw timestamp would make almost every message its own group.

Step 2: Compare the two counts per day

Within each day, count edited messages (edited = 1) and unedited messages (edited = 0) with two conditional sums, and keep the day in HAVING only when the edited count is strictly greater than the unedited count.

Step 3: Rejoin to recover the rows

The ask is for the raw messages, not the counts. Join the surviving dates back to chat_msgs to recover every column for every message on those days, then order by sent_at with msg_id as the tiebreaker for a deterministic result.

The solution

WITH qualifying_days AS (
    SELECT DATE(sent_at) AS msg_date
    FROM chat_msgs
    GROUP BY DATE(sent_at)
    HAVING SUM(CASE WHEN edited = 0 THEN 0 ELSE 1 END)
         > SUM(CASE WHEN edited = 0 THEN 1 ELSE 0 END)
)
SELECT cm.*
FROM chat_msgs cm
JOIN qualifying_days qd ON DATE(cm.sent_at) = qd.msg_date

Common follow-up questions

• What if edited were a boolean flag instead of 0/1? (Change the CASE to WHEN edited THEN 1, or compare boolean sums directly.)
• How would you optimize this for repeated queries? (Materialize the qualifying dates or add an indexed computed date column.)
• What if 'edited' meant the number of times a message was edited? (Tests whether they treat edited as a count of edits rather than a binary flag.)