Day-One Return Rate

A language-learning app logs every lesson a learner completes. The growth team wants the day-one return rate: the share of learners who completed at least one lesson on the calendar day right after their first-ever lesson day.

Doing several lessons on the first day does not count as returning, and coming back only on later days does not count either.

Return a single row with one column day1_return_rate: returning learners divided by all learners, as a fraction between 0 and 1 rounded to 2 decimal places (for example 0.4). If the log is empty, return that row with NULL.

Tables

lesson_log — One row per completed lesson; a learner can complete many lessons on the same day.

Example

lesson_log

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Aggregation & Grouping · problem 15 of 70 · SQLite

The idea

The denominator is the number of learners, so begin by collapsing the log to one row per learner with MIN(lesson_date) as their first day. ISO dates sort as text, so MIN works directly.

For each learner, the question is a yes/no: is there any lesson on date(first_day, '+1 day')? EXISTS answers that without being affected by how many lessons happened that day. SQLite’s date() modifier handles month and year boundaries, which string arithmetic would not.

Turning the answer into 1.0 or 0.0 and taking AVG gives the fraction of learners directly, with real division. On an empty log there are no learners, AVG of nothing is NULL, and the aggregate still returns one row.

The alternative left joins each learner to their next-day lessons and divides COUNT(DISTINCT l.learner_id) by COUNT(DISTINCT f.learner_id). The DISTINCT on the numerator is what stops a busy second day from being counted twice.

Solution

WITH firsts AS (
  SELECT learner_id, MIN(lesson_date) AS first_day
  FROM lesson_log
  GROUP BY learner_id
)
SELECT
  ROUND(AVG(
    CASE WHEN EXISTS (
      SELECT 1
      FROM lesson_log AS l
      WHERE l.learner_id = f.learner_id
        AND l.lesson_date = date(f.first_day, '+1 day')
    ) THEN 1.0 ELSE 0.0 END
  ), 2) AS day1_return_rate
FROM firsts AS f;

Another way

WITH firsts AS (
  SELECT learner_id, MIN(lesson_date) AS first_day
  FROM lesson_log
  GROUP BY learner_id
)
SELECT
  ROUND(COUNT(DISTINCT l.learner_id) * 1.0 / COUNT(DISTINCT f.learner_id), 2) AS day1_return_rate
FROM firsts AS f
LEFT JOIN lesson_log AS l
  ON l.learner_id = f.learner_id
 AND l.lesson_date = date(f.first_day, '+1 day');

Why common answers fail

Counts any pair of consecutive days, not specifically the day after the first lesson day.

WITH g AS (
  SELECT learner_id, lesson_date,
         LAG(lesson_date) OVER (PARTITION BY learner_id ORDER BY lesson_date) AS prev_day
  FROM lesson_log
)
SELECT ROUND(COUNT(DISTINCT CASE WHEN julianday(lesson_date) - julianday(prev_day) = 1 THEN learner_id END) * 1.0
             / COUNT(DISTINCT learner_id), 2) AS day1_return_rate
FROM g;

Counts next-day lessons instead of learners, so two lessons on day two count the learner twice.

WITH firsts AS (
  SELECT learner_id, MIN(lesson_date) AS first_day FROM lesson_log GROUP BY learner_id
)
SELECT ROUND(COUNT(l.learner_id) * 1.0 / COUNT(DISTINCT f.learner_id), 2) AS day1_return_rate
FROM firsts AS f
LEFT JOIN lesson_log AS l
  ON l.learner_id = f.learner_id AND l.lesson_date = date(f.first_day, '+1 day');

Integer division: any rate below 1 is truncated to 0.

WITH firsts AS (
  SELECT learner_id, MIN(lesson_date) AS first_day FROM lesson_log GROUP BY learner_id
)
SELECT ROUND(COUNT(DISTINCT l.learner_id) / COUNT(DISTINCT f.learner_id), 2) AS day1_return_rate
FROM firsts AS f
LEFT JOIN lesson_log AS l
  ON l.learner_id = f.learner_id AND l.lesson_date = date(f.first_day, '+1 day');

What the interviewer is checking

A classic retention metric: the interviewer wants to see you anchor on the first event, use proper date arithmetic, and count users rather than rows.

Mistakes to avoid

• Detecting any consecutive-day pair instead of the day after the first.
• Counting lessons instead of learners.
• Integer division returning 0.
• Adding 1 to a date string instead of using date(..., '+1 day').

Follow-ups to expect

• Compute day-7 retention (date(first_day, '+7 day') or a window of days).
• Break the rate down by sign-up week (group firsts by strftime('%W', first_day)).
• Return the rate per cohort only when the cohort has at least 30 learners (HAVING).

In real work

Day-1, day-7 and day-30 retention are the headline numbers on most consumer-app growth dashboards.

Practice next