# The Freshest Record > Duplicates everywhere. Only the most recent version of the truth survives. Canonical URL: 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. ## Tables ### server_logs - log_id INTEGER - server_name TEXT - log_level TEXT - message TEXT - response_time_ms INTEGER - log_timestamp TEXT ## 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. > **Trick to Solving** > > Two stages, in this order: > > 1. CTE: `ROW_NUMBER`() OVER (PARTITION BY `server_name`, message ORDER BY `log_timestamp` ASC) labels rows; `occurrence_rank` = 1 is the earliest row per pair, every column intact > 2. Outer: keep `occurrence_rank` = 1, then require that earliest row to sit within 90 days of the latest log. Applying the window to the survivor, not the base table, is what keeps old recurrences out. --- ### 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 **First-occurrence dedup, then the rolling 90-day filter** ```sql 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 ``` > **Time and Space Complexity** > > **Time:** O(n log n) for the in-partition sort across the log table. The hash partitioner builds groups of (`server_name`, message), and within each the ascending sort finds the earliest row. A composite index on (`server_name`, message, `log_timestamp`) turns that sort into a single ordered scan. The scalar MAX(`log_timestamp`) is one cheap pass, a single seek with an index on `log_timestamp`. > > Space: O(g) for the partition state, where g is the number of distinct (`server_name`, message) pairs. > **Interviewers Watch For** > > Strong candidates dedup first, filter second, and they reach for `ROW_NUMBER` rather than MIN because they need the whole first-occurrence ROW, not just its timestamp. They also apply the 90-day cutoff to the survivor, never to the base table, so a recurrence cannot masquerade as a debut. > **Common Pitfall** > > Pushing the 90-day window onto the base table before dedup. That admits any pair with a recent copy, so a two-year-old incident that flared up this week is counted as new. The other trap: keying the partition on message alone (or on `log_id`), which merges unrelated servers or never groups anything at all. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/deduplicate_and_keep_latest) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.