𝗦𝗤𝗟 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀 𝗔𝘁 𝗦𝗼𝗹𝘃𝗲𝗿𝗶𝗻𝗲𝘀 𝗧𝗲𝗰𝗵𝗻𝗼𝗹𝗼𝗴𝘆
I recently finished a technical interview at Solverines Technology Solutions. The SQL round focused on retrieving records and row numbering. I also faced a logic problem about toggling bulb states.
Here are the SQL patterns you should know.
Assume an Employee table with emp_id and emp_name.
Get the last record: SELECT * FROM Employee ORDER BY emp_id DESC LIMIT 1;
Get the 3rd row: SELECT * FROM Employee ORDER BY emp_id LIMIT 1 OFFSET 2; Formula: LIMIT 1 OFFSET (N - 1).
Get the 3rd row from the end: SELECT * FROM Employee ORDER BY emp_id DESC LIMIT 1 OFFSET 2;
Use ROW_NUMBER() for specific row patterns.
Get the 2nd odd row: SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY emp_id) AS rn FROM Employee) t WHERE rn = (2 * 2 - 1); Formula for Nth odd row: rn = (2 * N - 1).
Get the 3rd even row: SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY emp_id) AS rn FROM Employee) t WHERE rn = (2 * 3); Formula for Nth even row: rn = (2 * N).
Logic Problem: Bulb Toggling
The problem asks you to toggle N bulbs over K operations. Each operation gives you a start index i and an end index j. A 0 becomes 1. A 1 becomes 0.
Example: N = 5, K = 2. Operation 1: index 1 to 3. Operation 2: index 2 to 4.
Initial: 0 0 0 0 0 After Op 1: 0 1 1 1 0 After Op 2: 0 1 0 0 1 Final result: 0 1 0 0 1.
Interview Takeaways:
The interview tested these SQL topics:
- Finding the last record.
- Finding the Nth row from the start or end.
- Using ROW_NUMBER() for even or odd rows.
- Array manipulation for logic problems.
Practice these patterns if you are a fresher or junior developer.