Compute User Login Streaks
Company: Pinduoduo
Role: Data Engineer
Category: Coding & Algorithms
Difficulty: easy
Interview Round: Technical Screen
Given a table `user_login_log` with the following columns:
- `user_id`: the user identifier
- `login_date`: the calendar date of a login event
A user may have multiple login events on the same date; treat multiple logins by the same user on the same date as one login day. A login streak is a sequence of consecutive calendar dates on which the user logged in at least once.
Write SQL queries and discuss the final output schemas for the following tasks:
1. For a given 15-day analysis window, compute each user's consecutive login streaks within that window.
2. Compute each user's maximum consecutive login streak length across the available data.
3. Return the users who have at least one login streak of two or more consecutive days.
Quick Answer: This question evaluates SQL proficiency in processing event logs for time-series analysis, encompassing date arithmetic, deduplication of same-day events, consecutive-date streak detection, and result-schema design within the Data Engineering and Coding & Algorithms domain.