Bytedance·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL round for a Data Engineer role at Bytedance. One meaty question, about 30 minutes, felt like they wanted to see if you actually think in sequences and not just joins.

Questions Asked (1)

Given a users table and a logs table containing login attempt records, write a query to find users who had at least 3 consecutive failed login attempts on a specific date. Return the user's email, the count of consecutive failures, and the timestamps of the first and last failure in that streak.

Data ModelingAlgorithms & Data Structures

Author's notes

This tripped me up more than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a window function to group consecutive failed attempts by calculating the difference between the row number and a sequence of timestamps, then filter groups with at least 3 failures on the given date. Finally, aggregate to get the count, first and last timestamps, and join with the users table to return the email.

Pro tip: Clarify whether 'consecutive' means consecutive in time regardless of other users' attempts, or consecutive per user; also confirm if the streak must be entirely within the specific date or can span across dates. Mentioning these edge cases shows attention to detail.

1. Filter and prepare data

Select login attempts for the specific date and only failed attempts, ensuring you have user_id and timestamp. Order by user_id and timestamp.

2. Identify consecutive groups

Use a window function like ROW_NUMBER() partitioned by user_id and ordered by timestamp, then subtract it from the timestamp (or use a sequence) to create a group identifier for consecutive attempts.

3. Aggregate and filter streaks

Group by user_id and the group identifier, count the number of failures, and filter groups with count >= 3. Also compute MIN(timestamp) and MAX(timestamp) for the streak.

4. Join with users and format output

Join the result with the users table on user_id to get the email, and select the required columns: email, count, first_failure, last_failure.

Key Points to Mention

Use of window functions (ROW_NUMBER, LAG/LEAD) to detect consecutive sequences.
Handling of time-based ordering and potential ties in timestamps.
Filtering by date and status (failed) before grouping.
Ensuring the streak is per user and not across different users.
Performance considerations: indexing on (user_id, timestamp) and filtering early.
Edge cases: multiple streaks per user, streaks spanning midnight, and definition of 'consecutive'.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.