Status Changes in Log Data
Reported by candidates from Mastercard's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The fastest way to fail Mastercard's "Status Changes in Log Data" SQL question, reported in July 2024, is to compare each row to the wrong neighbor. Most people reach for a self-join on time and get duplicates or skipped rows when timestamps tie. This one is a window function problem. You need the previous status per entity, in a strict order, and then you filter to rows where it changed. If you blank on LAG syntax during the OA, StealthCoder runs invisibly as a safety net and reads the schema for you.
The problem
Each row in status_logs records the status of one entity at an event time. Within each entity, order logs by event_time and then log_id. Return every noninitial row whose status differs from the immediately preceding row for that entity. Include both the previous and current status for each transition. Tables status_logs: log_id PK (Integer), entity_id (Integer), event_time (Timestamp), status (Text) Constraints log_id is unique. Status comparisons are exact and case-sensitive. Rows with the same event_time are ordered by log_id. The first row for an entity is not a status change. Sort by entity_id, event_time, and log_id ascending.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is LAG(status) OVER (PARTITION BY entity_id ORDER BY event_time, log_id). Partition by entity so one entity never sees another's rows. Order by both event_time and log_id, because the problem says ties on event_time break by log_id. Skip the log_id and you get nondeterministic results. Put LAG in a subquery or CTE, then filter in the outer query, since you can't use a window function in WHERE. Keep rows where prev_status IS NOT NULL AND prev_status <> status. That NULL check drops the first row per entity, which the problem says isn't a change. Comparison is case-sensitive, so don't wrap anything in LOWER. Final ORDER BY is entity_id, event_time, log_id. Return the previous and current status as separate columns. If the window syntax slips under pressure, StealthCoder is the hedge on the live OA.
StealthCoder is the hedge for the one pattern you didn't drill. It runs invisibly during the screen share.
You can drill Status Changes in Log Data cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. If you're reading this with an OA window open, you're who this was built for.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Mastercard's OA.
Mastercard reuses patterns across OAs. If you're reading this with an OA window open, you're who this was built for. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Status Changes in Log Data FAQ
What's the trick in Status Changes in Log Data?+
Use LAG(status) partitioned by entity_id and ordered by event_time, then log_id. Wrap it in a CTE or subquery, then filter where the previous status isn't NULL and differs from the current one. That handles first rows and real transitions in one pass.
Why can't I filter on LAG directly in WHERE?+
Window functions run after WHERE in SQL's evaluation order. So the alias doesn't exist yet at filter time. Compute LAG in a CTE or derived table, then apply your WHERE on the outer query. This is the most common syntax error on this type of question.
How do I handle the first row per entity?+
LAG returns NULL for the first row in each partition. Filter with prev_status IS NOT NULL AND prev_status <> status. Don't rely on the <> alone, because comparing NULL to anything yields NULL and silently drops rows. Being explicit makes the intent clear and keeps the logic safe.
Does case sensitivity matter here?+
Yes. The problem says comparisons are exact and case-sensitive, so 'Active' and 'active' count as a change. Don't apply LOWER or UPPER. Just compare the raw columns. Some databases use case-insensitive collations by default, but the problem spec rules that out.
How do I prep for this in 48 hours?+
Write three LAG and LEAD queries from scratch with PARTITION BY and a two-column ORDER BY. Practice the CTE-then-filter shape until it's automatic. Then check your final ORDER BY matches the spec exactly. That covers nearly everything this problem tests.