Library › Coding AI › Product managers

Measure week-one retention for a signup cohort

Practice: Giving the right context. About 5 minutes.

About this exercise

Write one SQL query for the September 2026 signup cohort (users.signup_at from Sept 1 up to Oct 1). A user counts as retained if they have at least one session_start event from 7 days after signup up to, but not including, 14 days after. Return one row: cohort_size, retained and retained_pct rounded to 1 decimal, avoiding integer division. One code block, at most 28 lines, then under 25 words outside the code.

Common mistakes when you ask AI for this

  • Not defining retained. The AI picks its own meaning, like any session in the first week or exactly day 7. Try: Add: "Retained = a session_start from day 7 up to, not including, day 14."
  • Leaving the cohort vague. The AI uses this month or the last 30 days instead of September signups. Try: Add: "signup_at >= 2026-09-01 and < 2026-10-01."
  • Trusting integer division. Dividing two counts in SQL can round down to 0, so the percent shows 0. Try: Add: "Multiply by 100.0 and round to 1 decimal."
  • Getting a menu of options. The AI likes to offer two or three versions of the query, so you're left to pick and test them yourself. Try: Add: "Give one query only, in one code block, no alternatives."
  • Getting a tutorial. By default the AI explains every part of the code at length, which you then have to scroll past. Try: Add: "After the code, one line under 15 words. Nothing else."

What a strong prompt includes

  • Defines the retention window
  • Sets the cohort
  • Handles the percent
  • Shows the output columns
  • Asks for one answer only

Practice this now ↑

Related exercises