Replysis AI
Try free

Question bank / Data, Coding, Intermediate

How to answer: Write SQL to calculate seven-day retention by signup cohort.

Whether you can handle cohorts and date logic in SQL without errors.

What the interviewer is testing.

Cohorts, joins, date logic. Whether you can handle cohorts and date logic in SQL without errors.

An outline for this question.

  1. Define retention out loud: of the users who signed up on a day, the share who return exactly seven days later.
  2. Build a table of each user's signup date, then join to their activity dates.
  3. Count distinct users active on signup date plus seven, grouped by signup date, and divide by the cohort size.
  4. Check the edge cases: cohorts less than seven days old should not show a rate, and users with several events count once.

The mistake to avoid.

Dividing by all users instead of each cohort's size. The number looks fine and is wrong.

The general method: clarify, plan, code, test.

The same shape works for most coding questions, whatever the topic.

  • Restate the problem and ask about inputs, size limits and edge cases before you write anything.
  • Describe your approach and its complexity out loud, then check it makes sense with the interviewer.
  • Write clean code in small steps, narrating as you go.
  • Walk through a normal case and an edge case by hand, then say what you would test.

Practice it, out loud.

Reading an outline is not the same as saying it. Answer this question in a mock interview, in your own words, then compare it with the outline above.

Practice this questionSee live interview help

More to practice.

This is a Replysis practice question. It is not a leaked or official question from any company, and the outline is a guide to structure, not a script. Use your own experience and facts.