For each ticket, return only its most recent status.

Ticketing, order and claims systems keep their history in event tables like this one, and “what’s the state now?” is among the first questions a customer’s team asks of one. The query is short. The work is showing that “most recent” is defined for every ticket, including two changes in the same second and a change with no timestamp. Say the grain of the input. One row per status change, many per ticket: an append-only event log. The output is one row per ticket. (With lead() you could turn the log into validity ranges, a slowly changing dimension of type two, but this question doesn’t need them.) Ask what “most recent” is ordered by. Usually a change timestamp. Then ask what happens on a tie: an automation can write two changes in the same second. You need a second sort key that is unique, such as an id from a sequence, and you should ask whether its order means anything in business time. Pick the pattern and name it. Rank rows within each ticket with row_number(), a window function, and keep rank one. Mention the dialect shortcuts you know, DISTINCT ON in PostgreSQL and QUALIFY in Snowflake, BigQuery, Databricks and DuckDB, and say they need the same tie-breaker. Handle the edges out loud. A null timestamp should not win, so sort nulls last. A ticket with no history rows is missing from the result; if the question is “every ticket”, start from the tickets table with a left join. Say how you would check it. No ticket_id appears twice in the result, and its row count equals the count of distinct tickets in the history. You need both: a query that duplicates one ticket and drops another passes the count alone. In Pro, the data platform migration case asks when each report was last used, from a log of every open, export and scheduled delivery, and puts month-end numbers in front of a finance lead whose close reports have to keep tying to the ledger, to the cent, on a new platform.

Latest status per ticket in SQL | FDE interview question

A practice prompt we wrote. No company or candidate report names it, so it carries no company tag.

How to answer

Ticketing, order and claims systems keep their history in event tables like this one, and “what’s the state now?” is among the first questions a customer’s team asks of one. The query is short. The work is showing that “most recent” is defined for every ticket, including two changes in the same second and a change with no timestamp.

Say the grain of the input. One row per status change, many per ticket: an append-only event log. The output is one row per ticket. (With lead() you could turn the log into validity ranges, a slowly changing dimension of type two, but this question doesn’t need them.)
Ask what “most recent” is ordered by. Usually a change timestamp. Then ask what happens on a tie: an automation can write two changes in the same second. You need a second sort key that is unique, such as an id from a sequence, and you should ask whether its order means anything in business time.
Pick the pattern and name it. Rank rows within each ticket with row_number(), a window function, and keep rank one. Mention the dialect shortcuts you know, DISTINCT ON in PostgreSQL and QUALIFY in Snowflake, BigQuery, Databricks and DuckDB, and say they need the same tie-breaker.
Handle the edges out loud. A null timestamp should not win, so sort nulls last. A ticket with no history rows is missing from the result; if the question is “every ticket”, start from the tickets table with a left join.
Say how you would check it. No ticket_id appears twice in the result, and its row count equals the count of distinct tickets in the history. You need both: a query that duplicates one ticket and drops another passes the count alone.

Practice it with a customer

In Pro, the data platform migration case asks when each report was last used, from a log of every open, export and scheduled delivery, and puts month-end numbers in front of a finance lead whose close reports have to keep tying to the ledger, to the cent, on a new platform.

Follow-ups

What the interviewer may ask next, once your first answer is on the table.

Two status rows for the same ticket have the same timestamp. Which one wins, and who decides?
The history table has hundreds of millions of rows and this runs on every dashboard load. What do you change?
Now return each ticket’s status as of the end of last month.

Where answers go wrong

Joins back on the maximum timestamp, so a ticket with two changes in the same second comes back twice and the ticket count is wrong.
Uses GROUP BY with max(changed_at) and max(status), which returns the latest time next to the alphabetically last status, from different rows.

Answer this in two minutes

Write the answer you would say out loud. The clock starts with your first word.

Compare with the model answer

Model answer

“I’ll assume ticket_status_events(event_id, ticket_id, status, changed_at), one row per change, where event_id comes from a sequence with the default cache of 1, so a higher id was assigned later. If it were a random UUID it would still break ties consistently, but it wouldn’t mean ‘later’, and I’d ask what does. And ‘assigned later’ only means ‘happened later’ if events are inserted as they happen. If this table is loaded from the customer’s ticketing system in batches, I’d ask whether the source has its own sequence or version number and break ties on that.”

SELECT ticket_id, status, changed_at
FROM (
  SELECT ticket_id, status, changed_at,
         row_number() OVER (
           PARTITION BY ticket_id
           ORDER BY changed_at DESC NULLS LAST, event_id DESC
         ) AS rn
  FROM ticket_status_events
) ranked
WHERE rn = 1
ORDER BY ticket_id;

“row_number() numbers the rows of each ticket without gaps or repeats, so exactly one row per ticket gets 1, even on a tie, which is why I use it and not rank(): rank() gives tied rows the same number and brings the duplicates back. NULLS LAST is explicit because in PostgreSQL a descending sort puts nulls first by default, so a change with a missing timestamp would otherwise win.”

“In PostgreSQL I could also write it with DISTINCT ON, which keeps the first row of each group in the ORDER BY, as the SELECT reference describes:”

SELECT DISTINCT ON (ticket_id) ticket_id, status, changed_at
FROM ticket_status_events
ORDER BY ticket_id, changed_at DESC NULLS LAST, event_id DESC;

“And in Snowflake, BigQuery or Databricks, QUALIFY row_number() OVER (...) = 1 drops the subquery. All three need the same tie-breaker.”

“What I’d avoid is joining back on the maximum timestamp. I test both on four tickets: ticket 1 has pending and solved both at 10:00, ticket 2 is ordinary, ticket 3’s only change has no timestamp, and ticket 4 has no history. The join-back returns three rows for two tickets. Ticket 1 comes back twice, and ticket 3 disappears, because max() ignores nulls and the equality join never matches it. The count is wrong in both directions, so a total can even look right. The query above returns one row each for tickets 1 to 3, with solved for ticket 1 because its event_id is higher.”

“If the question is ‘every ticket, including ones with no history’, I’d start from tickets and left join this result, so those show a null status rather than disappearing.”

“To check it: the result’s count(*) should equal its own count(DISTINCT ticket_id), so no ticket repeats, and should also equal count(DISTINCT ticket_id) in the events table, so none is missing. The join-back passes the second check on those four tickets and fails the first.”

“For scale, the ranking query still reads every history row. With an index on (ticket_id, changed_at DESC NULLS LAST, event_id DESC), I can fetch one row per ticket without scanning the whole history, starting from tickets, which also keeps tickets with no history:”

SELECT t.ticket_id, s.status, s.changed_at
FROM tickets t
LEFT JOIN LATERAL (
  SELECT e.status, e.changed_at
  FROM ticket_status_events e
  WHERE e.ticket_id = t.ticket_id
  ORDER BY e.changed_at DESC NULLS LAST, e.event_id DESC
  LIMIT 1
) s ON true;

“Each ticket is one short index descent. If this runs on every dashboard load, I’d keep a ticket_current_status table that the writer upserts in the same transaction as the event, with ON CONFLICT (ticket_id) DO UPDATE ... WHERE (excluded.changed_at, excluded.event_id) > (ticket_current_status.changed_at, ticket_current_status.event_id), so an event that arrives late can’t overwrite a newer status. I’d use the queries above to it and to check it nightly.”

“For status as of a date, the same query with WHERE changed_at < :as_of inside the subquery, before the ranking, gives each ticket’s last change before that point. The boundary is where this goes wrong. End of last month means strictly before the midnight that starts this month, in the customer’s reporting time zone, not UTC:”

WHERE changed_at < date_trunc('month', now() AT TIME ZONE 'America/Chicago')
                     AT TIME ZONE 'America/Chicago'

“<, not <=, so a change at exactly midnight belongs to the new month. And I’d ask which time zone their month-end report uses before I pick one, because a UTC month-end moves every evening change on the last day into the wrong month.”