Abnormal Security Software Engineer Interview Questions

There were 4 rounds in total. The third round was code review + system design. Before this round, you were given a link for a pre-review code review. The interview was divided into two parts. The firs

There were four rounds in total. This was the final round, and like the third round, it was divided into two parts. This round involved coding and a deep dive into a past project. The coding question

## Problem You are given a Python snippet that implements a user session cache. Identify all bugs, performance issues, and design problems. Be prepared to explain the impact of each issue and propose fixes. ```python sessions = {} def get_session(user_id): if user_id not in sessions: sessions[user_id] = {"created": time.time(), "data": load_from_db(user_id)} return sessions[user_id] def invalidate_session(user_id): del sessions[user_id] def cleanup_old_sessions(): for uid, session in sessions.items(): if time.time() - session["created"] > 3600: del sessions[uid] ``` **Your task:** List every issue you can find in 10 minutes. Rewrite the module addressing all concerns. ## Follow-ups 1. `cleanup_old_sessions` modifies a dict while iterating — what error does this cause and how do you fix it? 2. The global dict is not thread-safe. How would you make this safe for a multi-threaded web server? 3. If this service runs across 10 instances, what breaks and how do you fix it? 4. How would you add an LRU eviction policy when memory usage exceeds a threshold?

## Problem You have a table of user events with timestamps. A session is a sequence of events from the same user with no gap exceeding 30 minutes. Compute per-user: number of sessions, average session duration in minutes, and the session with the most events. ```sql -- Schema events(event_id, user_id, event_type, created_at TIMESTAMP) ``` **Expected output:** ``` user_id | num_sessions | avg_duration_min | max_events_in_session u1 | 3 | 22.5 | 8 u2 | 1 | 45.0 | 15 ``` ## Approach Use window functions: `LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)` to compute gaps, then mark session boundaries, assign session IDs with a running sum, and aggregate. ## Follow-ups 1. Write the full SQL using CTEs. Walk through each step. 2. How does your query perform on a 1B-row events table? What indexes help? 3. If sessions should expire after 30 minutes of inactivity OR at midnight, how do you add the midnight boundary? 4. How would you detect bot sessions (e.g., events arriving faster than 1 per second for > 5 minutes)?