0Pricing
SQL Interview Prep · Lesson

Defining a Cohort by First Action

Assigning each user a cohort based on their first event date.

Defining a Cohort by First Action is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Cohorts Show Up in Interviews

When a product-analytics interviewer says "build a cohort", they are testing whether you can assign every user to a group based on when they first did something, then track that group over time.

A cohort is a set of users who share a starting event in the same period, usually their first purchase, signup, or login. The power of cohorts is that they let you compare users on equal footing: everyone in the January cohort is measured from their own January start.

The first sub-skill, and the one this lesson drills, is computing each user's first action date reliably.

The Source Table

Nearly every cohort question starts from an events table: one row per user action with a timestamp. Picture an events table:

  • user_id — who acted
  • event_type — what they did
  • event_at — when, as a timestamp

In an interview, clarify the grain out loud: "Is this one row per event, and can a user appear many times?" The answer is almost always yes, which is exactly why you need aggregation to collapse to a per-user first action.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

First Action = MIN of the Timestamp

The core move is simple: group by user_id and take MIN(event_at). That minimum is the user's first action, the moment that places them into a cohort.

This is the answer interviewers want to hear first, before any window-function flourish. A plain GROUP BY is correct, readable, and fast.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Filtering to a Defining Event

Often the cohort is defined by a specific action, not any event. "Cohort users by their first purchase" means you must filter to purchase rows before taking the minimum.

Put the filter in WHERE so the MIN only sees qualifying rows. A common interview trap is taking MIN over all events and then filtering afterward, which would assign the wrong start date to anyone who browsed before they bought.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Bucketing Into a Cohort Period

A cohort is usually a period, not an exact timestamp: the "2024-03 cohort" or "week of 2024-03-04". Truncate the first-action date down to the period grain.

In Postgres use DATE_TRUNC('month', ...). In MySQL you might use DATE_FORMAT(d, '%Y-%m-01'); in SQL Server, DATETRUNC(month, d) or a computed first-of-month. State your dialect in the interview so the syntax choice looks deliberate.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Wrapping It in a CTE

The per-user cohort assignment is a building block you will reuse in retention queries, so package it in a CTE named clearly. This keeps the next steps readable and shows the interviewer you think in composable pieces.

From here, every downstream query can join back to user_cohort to know which group a user belongs to.

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Cohort Size: Counting Members

The first sanity check an interviewer expects is the cohort size: how many users belong to each cohort. Group the assignment CTE by cohort_month and count distinct users.

Use COUNT(DISTINCT user_id) defensively even though the CTE already has one row per user; it signals you are thinking about grain. This count becomes the denominator for every retention percentage later.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Window-Function Alternative

Interviewers sometimes ask for the cohort label attached to every event row, not a collapsed table. Here a window function shines: MIN(event_at) OVER (PARTITION BY user_id) computes the first action without removing rows.

This is handy when you need both the detail events and the cohort tag in one pass, which is the setup for retention counting.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

The Ties and Duplicates Gotcha

What if a user has two events at the exact same earliest timestamp? MIN handles this cleanly: it returns that single minimum value regardless of how many rows tie, so cohort assignment stays one-per-user.

Contrast this with a ROW_NUMBER() ... ORDER BY event_at approach, where ties are broken arbitrarily and you must add a deterministic tiebreaker (such as event_id) to get a stable result. Mentioning this trade-off unprompted reads as senior.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Time Zones and the Day Boundary

A subtle interview probe: a purchase at 11:30 PM in New York is the next day in UTC. If cohorts are bucketed by calendar day, the time zone decides which cohort the user lands in.

The safe answer: store timestamps in UTC, then convert to the business time zone before truncating. Say explicitly which zone defines "day" for the metric, because that single decision can shift thousands of users between cohorts.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Excluding Pre-Window Users

Real analyses bound the cohort to a date range, for example "cohorts that started in Q1". Filter on the aggregated first-action date, which means a HAVING clause or an outer filter on the CTE, not a WHERE on raw events.

Filtering raw events by date would wrongly let a user who first purchased in December but also acted in Q1 sneak into a Q1 cohort. Always gate on the computed first action.

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Quick Check

An interviewer asks: "Cohort each user by their first purchase month. Users may browse before buying." Which approach is correct?

Recap: Defining a Cohort

Key takeaways for the cohort-definition interview question:

  • A cohort groups users by their first qualifying action.
  • Compute it with MIN(event_at) after filtering to the defining event in WHERE.
  • Bucket into a period with DATE_TRUNC (or the dialect equivalent).
  • Package the assignment in a CTE for reuse; COUNT(DISTINCT user_id) gives cohort size.
  • Watch the time-zone day boundary and gate date ranges on the computed first action, never on raw events.

Nail this and the retention matrix in the next lesson becomes a join.

Frequently asked questions

Is the “Defining a Cohort by First Action” lesson free?

Yes — the full text of “Defining a Cohort by First Action” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Defining a Cohort by First Action”?

Assigning each user a cohort based on their first event date. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Defining a Cohort by First Action” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Defining a Cohort by First Action
  2. Building a Retention Matrix
  3. Day-N and Rolling Retention
  4. Churn and Resurrection Queries
← Back to SQL Interview Prep