Bytedance·Software Engineer·Technical Phone Screen·Intermediate

Intermediate

Apr 2026

Summary

Bytedance data engineer interview with a SQL problem around consecutive login days. Pretty focused, just the one question but they wanted a full walkthrough of the logic.

Questions Asked (1)

Given a table of user login activity, write a SQL query to find users who logged in on consecutive days. Walk through your approach.

Algorithms & Data StructuresData Modeling

Author's notes

I knew the general shape of the answer (date subtraction, row_number trick) but fumbled explaining it out loud.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and assumptions (e.g., one row per user per day, date format). Then explain the window function approach: use LAG or LEAD to compare each login date with the previous/next login date, and filter where the difference is exactly 1 day. Finally, discuss edge cases like multiple logins per day and timezone considerations.

Pro tip: Mention that you'd deduplicate dates per user first (using DISTINCT or GROUP BY) to avoid false positives from multiple logins on the same day, and note that window functions like LAG are standard in modern SQL dialects (e.g., MySQL 8+, PostgreSQL).

1. Clarify requirements and assumptions

Ask about the table schema, whether each row represents a unique user-day, and the date format. Confirm that 'consecutive days' means calendar days, not 24-hour periods.

2. Deduplicate login dates per user

Use a subquery or CTE to get distinct (user_id, login_date) pairs, ensuring multiple logins on the same day don't skew the analysis.

3. Apply window function to compare dates

Use LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) to get the previous login date for each row.

4. Filter for consecutive days

Select rows where the difference between the current login_date and the previous login_date is exactly 1 day (e.g., DATEDIFF(day, prev_date, login_date) = 1).

5. Return distinct users and discuss alternatives

Return DISTINCT user_id. Optionally, mention alternative approaches like self-join or using LEAD, and discuss performance considerations.

Key Points to Mention

Use of window functions (LAG/LEAD) for comparing consecutive rows
Deduplication of dates per user to handle multiple logins per day
Date arithmetic and functions (e.g., DATEDIFF, DATE_SUB) to check 1-day difference
Partitioning by user_id and ordering by login_date
Handling edge cases: first login (NULL previous date), timezone, and date format
Alternative approaches: self-join or using EXISTS, and their trade-offs

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