← Pinduoduo Interview InsightsSoftware Engineer
Classic streak problem and I still fumbled the window function approach for a minute.
Use a window function to group consecutive login dates by subtracting a row number from each date, then count the size of each group and take the maximum per user. This 'gaps and islands' technique efficiently identifies streaks without self-joins.
Pro tip: Mention that you'd first deduplicate dates per user (since multiple logins per day are possible) and clarify the SQL dialect (e.g., MySQL 8+, PostgreSQL) because window function syntax and date functions vary.
Confirm the table schema, whether multiple logins per day count as one, and the SQL dialect. This ensures the solution matches the expected environment.
Use DISTINCT or GROUP BY to get one row per user per day, so consecutive days are correctly identified.
Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) and subtract it from login_date to create a constant 'group key' for consecutive dates.
Group by user_id and the computed group key, then count the number of rows (days) in each group.
Use MAX() on the counts, grouped by user_id, to get the longest consecutive login streak for each user.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.