#0030

Day-One Retention

MediumSQL22 min15 XP

Problem

Given users and logins tables, return user_ids who logged in exactly one day after their signup_date.

Retention is one of the most important product metrics. The logic is simple only after you clearly define the event dates.

users(user_id, signup_date)
logins(user_id, login_date)

Example 1

Inputuser 1 signup Jan 1 login Jan 2; user 2 signup Jan 1 login Jan 3

Outputuser 1

Only user 1 returned exactly one day after signup.

CodeSQL

SELECT DISTINCT
  -- user id
FROM users u
JOIN logins l
  ON -- join condition
WHERE -- day one condition

SELECT DISTINCT
  -- user id
FROM users u
JOIN logins l
  ON -- join condition
WHERE -- day one condition

Visible browser tests run here when available.

Testcases1 visible / 4 hidden categories

Exact next-day login

Inputthree users with different login timing

Expectedusers 1 and 3

Only logins exactly one day after signup count.

Hidden Test Categories

Duplicate next-day loginsNo retained usersLogin on signup dayLogin after two or more days