Most Recent Active User Per Department
Reported by candidates from Lenskart's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The Lenskart OA reported in September 2026 is a SQL question, and the whole thing hinges on one structure: a ranked partition per department. If you've got an invite and your SQL is rusty, this is the one to know cold. You pick one row per department, the latest last_seen, break ties by smallest log_position, then order departments by where they first showed up. It looks like a simple group-by. It isn't. StealthCoder is the safety net if you blank mid-assessment, but the pattern below is short enough to memorize tonight.
The problem
The table activity_logs stores user-activity records in input order. For each department, return the user from the row with the largest last_seen timestamp. If several rows in one department share its largest timestamp, keep the row with the smallest log_position. Order departments by the smallest log_position at which each department appears. Return department and user_id. The reported exercise formats these ordered pairs as a semicolon-delimited string; the tabular workspace represents the same pairs as ordered result rows. Input table activity_logsColumnMeaning log_positionOne-based position of the record in the input departmentDepartment name user_idUser identifier last_seenActivity timestamp Tables activity_logs: log_position PK (Integer), department (Text), user_id (Text), last_seen (Integer) Constraints The activity_logs table has between 1 and 10^5 rows. log_position is unique, non-null, and records the input order. department and user_id are non-null strings of length from 1 through 50. 0 <= last_seen <= 10^9.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is ROW_NUMBER() OVER (PARTITION BY department ORDER BY last_seen DESC, log_position ASC), then keep rn = 1. That handles the tie rule in one pass. The second requirement is the ordering. Compute MIN(log_position) per department in a separate CTE or as a window function, MIN(log_position) OVER (PARTITION BY department), and sort by it. The common pitfall is using GROUP BY department with MAX(last_seen) and joining back. That returns duplicates when two rows share the max timestamp. Another miss is ordering by the winning row's log_position instead of the department's first appearance. Those differ. Return only department and user_id, so don't leak helper columns into the final select. With 10^5 rows, a single window pass is fine. If you freeze during the live OA, StealthCoder can read the schema and hand you the window query so you can check it against the tie and ordering rules.
If you see this problem in your OA tomorrow, the play is to recognize the pattern in 30 seconds. StealthCoder buys you that recognition.
You can drill Most Recent Active User Per Department 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. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Lenskart's OA.
Lenskart reuses patterns across OAs. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Most Recent Active User Per Department FAQ
How hard is the Lenskart most recent active user question really?+
Medium at most. The logic is one window function plus one ordering rule. Candidates lose points on the tie-break and the department ordering, not on syntax. If you know ROW_NUMBER with a multi-column ORDER BY, you're most of the way there.
What's the trick to the tie-break on last_seen?+
Put both rules inside the window ORDER BY: last_seen DESC, then log_position ASC. Then filter rn = 1. This picks exactly one row per department, so you never need a join back or a DISTINCT to clean up duplicates.
How do I order departments by first appearance?+
Take MIN(log_position) per department, either as a window function or in a CTE grouped by department. Sort the final result by that value. Don't sort by the winning row's log_position, because the winner is often not the department's first row.
Can I solve it with GROUP BY and MAX instead?+
You can, but it's riskier. Grouping gives you MAX(last_seen), then you join back to find the user. Ties on timestamp produce multiple rows, so you need another step to pick the smallest log_position. The window approach is cleaner and shorter.
How do I prepare in 48 hours for this kind of SQL OA?+
Write the top-row-per-group query from memory three times using ROW_NUMBER, then once with a CTE and MIN for ordering. Practice tie-break rules and NULL handling. This problem has non-null columns, so focus on partitioning and ordering correctness.