First Duplicate Transaction Within Ten Minutes
Reported by candidates from Lenskart's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
Lenskart reported this SQL question in September 2026, and the 10^5 row cap is the first thing to read. A self-join comparing every pair of rows with the same ID looks fine on a sample and crawls on a big table. The real task is smaller. For each row, look at the most recent earlier row with the same transaction_id, check if the gap is 10 minutes or less, then return the first sequence_no that qualifies. StealthCoder is the safety net on the live OA if the window function syntax slips out of your head, but the idea is short enough to hold.
The problem
The table transactions stores transaction records in processing order. A row is a duplicate when an earlier row has the same transaction_id and occurred no more than 10 minutes before it. Scan rows by sequence_no. Return the transaction_id from the first duplicate row encountered. If no duplicate exists, return NONE. Transaction IDs may recur; each recurrence is compared with the most recent earlier occurrence of the same ID. Input table transactionsColumnMeaning sequence_noOne-based processing position transaction_idTransaction identifier transaction_minuteTimestamp in minutes Tables transactions: sequence_no PK (Integer), transaction_id (Text), transaction_minute (Integer) Constraints The transactions table has between 1 and 10^5 rows. sequence_no is unique, non-null, and records processing order. transaction_id is a non-null string of length from 1 through 50 and may appear more than once. 0 <= transaction_minute <= 10^9. transaction_minute values are non-decreasing in sequence_no order.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is LAG. Partition by transaction_id, order by sequence_no, and pull the previous transaction_minute. That previous row is exactly the most recent earlier occurrence the problem asks you to compare against. Then keep rows where transaction_minute minus the lagged value is at most 10. Order by sequence_no, take the first one, and return its transaction_id. The pitfall is the NONE case. An empty result set is not the string NONE, so wrap the query as a scalar subquery with COALESCE, or use a UNION ALL fallback with LIMIT 1. Another trap is comparing against the first occurrence instead of the latest, which breaks on chains of repeats. The non-decreasing minutes guarantee means you never get a negative gap. If you blank on the syntax during the live OA, StealthCoder can supply the LAG plus COALESCE shape while you verify the boundary at exactly 10 minutes.
Drill it cold or hedge it with StealthCoder. Either way, don't walk into the OA hoping you remember the trick.
You can drill First Duplicate Transaction Within Ten Minutes 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. Made for the candidate who got the OA invite this morning and has 72 hours, not six months.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Lenskart's OA.
Lenskart reuses patterns across OAs. Made for the candidate who got the OA invite this morning and has 72 hours, not six months. Works on HackerRank, CodeSignal, CoderPad, and Karat.
First Duplicate Transaction Within Ten Minutes FAQ
What's the trick to the Lenskart duplicate transaction query?+
Use LAG(transaction_minute) partitioned by transaction_id and ordered by sequence_no. That gives you the most recent earlier occurrence of the same ID. Filter where the difference is 10 or less, order by sequence_no, and take the first row.
Why not just self-join the table?+
With up to 10^5 rows, a self-join on transaction_id can blow up when one ID repeats many times. It also compares against every earlier row, not just the latest one. LAG reads each row once after sorting within the partition, so it's cleaner and faster.
How do I return NONE when there's no duplicate?+
A query with no matching rows returns nothing, not the text NONE. Wrap your query in a scalar subquery and use COALESCE((subquery), 'NONE'). Another route is a UNION ALL with a fallback row and LIMIT 1 after ordering.
Is the 10 minute boundary inclusive?+
Yes. The statement says no more than 10 minutes before, so a gap of exactly 10 counts as a duplicate. Use the condition gap <= 10, not < 10. This is the most common off-by-one on this question, so test it with a gap of exactly 10.
How do I prepare for this in 48 hours?+
Practice LAG and LEAD with PARTITION BY and ORDER BY until you can write them without looking. Then rehearse the scalar subquery plus COALESCE pattern for empty results. Run a few small tables by hand, including repeated IDs and a gap of exactly 10 minutes.