Lime Interview Questions

Lime’s consumer app team wants to measure the number of users who return to the app after their initial login. Given a user_logins table that records each user’s login by date, write a query to produce a daily summary with retention and engagement metrics.

For each date that appears in the user_logins table, calculate daily retention metrics including cohort size, 7-day retention, retention rate, and rolling 7-day active users. (A cohort for a given date consists of all users who logged in on that specific date (regardless of whether it was their first login or a subsequent login).

Notes:

• A user is considered retained if they log in again within the next 7 days after their cohort date (days 2-8), excluding the cohort day itself.
• The rolling 7-day users includes all distinct users who logged in during the 7-day window ending on the current date (from login_date - 6 days to login_date, inclusive).
• Each (user_id, login_date) pair is unique - users can only log in once per day. The output should be ordered by login_date ascending.
• Retention rate should be calculated as: (retained_within_7d / cohort_users) * 100, rounded to 1 decimal place.

Schema:

Input:

user_logins table

Output:

Example:

Input:

Output:

Explanation:

Retention is calculated for users who return within 7 days after their first login on a given day. Rolling 7-day user counts unique users over the current and previous 6 days.*

Suppose the current login_date is October 10, 2027.

• The cohort for this date includes all users who logged in on 2027-10-10.
• A user is considered retained if they log in again between 2027-10-11 and 2027-10-17 (inclusive).
• The cohort day itself (2027-10-10) is excluded from this retention window.