The Freshest Record

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

Target

Problem

The reliability team wants the genuinely new incidents from the last 90 days: server-and-message pairs whose very first appearance anywhere in the logs lands inside that window, so a long-running problem that merely flared up again is left out. For each qualifying pair, return every column of that first sighting, most recently emerged first.

Duplicates everywhere. Only the most recent version of the truth survives.

Tables

server_logs

Columns: log_id INTEGER, server_name TEXT, log_level TEXT, message TEXT, response_time_ms INTEGER, log_timestamp TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Strip the costume and this is a per-key first-occurrence extraction welded to a rolling date window, and the whole game is the ORDER you apply them. A GROUP BY server_name, message with MIN(log_timestamp) tells you WHEN each pair debuted, but it cannot hand you back the log_id, log_level and response_time_ms of that exact first row. And if you date-filter the base table to the last 90 days BEFORE you collapse duplicates, you change the answer: an incident that started two years ago and merely flared up again this week sails through, because its recent copy passes the window even though its true debut is ancient. The move the interviewer is watching for: find each pair's earliest row across ALL history first, then keep only the ones whose earliest row lands inside the last 90 days.

Break down the requirements

Step 1: Number rows within each (server_name, message) partition, earliest first

In a CTE, assign ROW_NUMBER() per (server_name, message) ordered by log_timestamp ASC, so the very first occurrence of each pair gets occurrence_rank = 1 while carrying all of its original columns.

Step 2: Keep the first sighting, then apply the 90-day window to it

Keep only occurrence_rank = 1 rows (each pair's debut) and, on those survivors, require julianday(date((SELECT MAX(log_timestamp) FROM server_logs))) - julianday(log_timestamp) <= 90 so only pairs whose debut lands in the last 90 days remain. Anchoring the cutoff to the newest log via a scalar subquery keeps the window stable as data arrives, and applying the date test here, not inside the CTE, is what keeps old recurring incidents out.

Step 3: Project the original columns, newest first

Project the original columns and order by log_timestamp DESC, log_id so the most recently emerged debut sits on top and the ordering is total even when two debuts share a timestamp.

The solution

WITH first_seen AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY server_name, message
            ORDER BY log_timestamp ASC
        ) AS occurrence_rank
    FROM server_logs
)
SELECT log_id, server_name, log_level, message, response_time_ms, log_timestamp
FROM first_seen
WHERE occurrence_rank = 1
  AND julianday(date((SELECT MAX(log_timestamp) FROM server_logs))) - julianday(log_timestamp) <= 90
ORDER BY log_timestamp DESC, log_id

Common follow-up questions

• If you moved the 90-day filter inside the CTE, which extra pairs would wrongly appear in the result? (Tests understanding of how a pre-dedup filter changes which pairs qualify.)
• How would you write the first-occurrence step with DISTINCT ON (`server_name`, message) on Postgres? (Tests the Postgres-specific dedup idiom as a more concise alternative.)
• What changes if the team instead wanted each pair's most recent occurrence? (Tests flipping the window ordering for the inverse question.)