Next-Day Retention

HardSQLSQL

Solve this the way you would in the interview: out loud, on a timer, with an AI interviewer asking follow-ups while you code. You get a scored report at the end.

Practice this as a mock interviewFree to start, no account needed

Problem

Table logins: user_id INTEGER day TEXT 'YYYY-MM-DD' (a user can have several rows for the same day) A user's signup day is their earliest login day. A user is retained if they also logged in on the calendar day after their signup day. Return, for each signup day, how many users signed up and how many of them were retained. Columns, in this order: signup day, users who signed up that day, users retained. Order by signup day, ascending.

Examples

Input: logins user_id | day 1 | 2026-07-01 1 | 2026-07-02 2 | 2026-07-01 2 | 2026-07-03
Output: 2026-07-01 | 2 | 1
User 2 came back on the 3rd, which is not the day after signup.

Constraints

  • Several logins on one day count once.
  • A signup day where nobody came back shows 0, not a missing row.

What the interviewer grades

A correct solution is one of five scores. The report also grades problem understanding (did you clarify inputs and edge cases before coding), problem solving, communication while you work, code quality, and testing. See a sample report, or read up on the pattern in the lessons.

More SQL problems