← Pinduoduo Interview Insights
My first instinct was to just do a self-join on the date range and count rows, but that doesn't actually capture the 'consecutive' part properly.
First, clarify the exact definition of 'consecutive login days within any 15-day rolling window'—whether it means the maximum streak of consecutive days where the user logged in at least once, and whether the window is fixed or sliding. Then, outline a SQL solution using window functions: deduplicate login dates per user, compute gaps between consecutive logins, assign streak groups, and for each 15-day window calculate the longest consecutive run.
Pro tip: Mention that you would validate the solution with edge cases like users with a single login, gaps larger than 15 days, and multiple logins on the same day, and discuss performance considerations for large datasets (e.g., indexing on user_id and login_date).
Ask the interviewer to confirm the definition: is it the longest streak of consecutive days with at least one login, and does the 15-day window slide or is it fixed? Also clarify handling of duplicate logins on the same day.
Deduplicate login records to get distinct (user_id, login_date) pairs, ensuring each user-day combination appears once.
Use window functions (e.g., LAG or ROW_NUMBER) to compute the difference between login_date and a sequential row number; group by this difference to assign a streak ID for each consecutive sequence.
For each user, consider all possible 15-day windows (sliding) and compute the length of the longest consecutive streak that falls entirely within each window, then take the maximum across windows.
Discuss indexing, partitioning, and potential use of self-joins or window frames to improve performance; validate with edge cases and explain the time complexity.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.