Learn Advanced Cohort Analysis with DATE Functions | Cohort Analysis with DATE Functions

Cohort analysis becomes much more powerful when you can measure user activity across custom time periods rather than just by signup date or static calendar windows. By using SQL's DATE_DIFF and interval functions, you can analyze how user engagement changes over time, compare cohorts on a day-by-day basis, and build flexible retention or activity curves tailored to your business questions. This approach allows you to understand not only if users return, but when and how their behavior evolves after signup.

Definition

DATE_DIFF returns the difference between two dates, measured in the specified unit (such as "day" or "month"). This function is especially useful for tracking user engagement over time, as it allows you to calculate how long it has been since a user signed up or performed a key action.


              1234567891011
            
-- Calculate the number of days since each user's signup for every event
SELECT
  e.event_id,
  e.user_id,
  e.event_type,
  e.event_timestamp,
  u.signup_date,
  (e.event_timestamp::date - u.signup_date) AS days_since_signup
FROM events e
JOIN users u ON e.user_id = u.user_id
ORDER BY e.user_id, e.event_timestamp;

To go beyond simple day counts, you can use intervals to define custom cohort windows—such as "week 1," "week 2," or "days 0–6," "days 7–13," and so on. By grouping users or their events into these flexible time buckets, you can compare behavior more meaningfully across different signup dates. Intervals let you align your analysis with business cycles, marketing campaigns, or product milestones, providing a clearer picture of how user engagement and retention shift over time.


              12345678910
            
-- Group events by user and days since signup, creating cohort windows
SELECT
  e.user_id,
  u.signup_date,
  (e.event_timestamp::date - u.signup_date::date) AS days_since_signup,
  COUNT(*) AS events_in_window
FROM events e
JOIN users u ON e.user_id = u.user_id
GROUP BY e.user_id, u.signup_date, days_since_signup
ORDER BY e.user_id, days_since_signup;

This query analyzes user engagement over time relative to signup by grouping events into day-based activity windows.

It joins the events table with the users table to associate each event with the user’s signup date. Using date arithmetic, it calculates days_since_signup by subtracting the signup date from the event date. In PostgreSQL, this subtraction returns the number of days between the two dates, creating a consistent, relative timeline for all users.

A filter ensures that only events occurring on or after signup are included, which preserves correct lifecycle analysis and excludes invalid pre-signup activity.

The query then applies grouped aggregation, counting how many events each user generated on each day since signup. Grouping by user_id, signup_date, and the calculated day offset ensures that events are bucketed into precise daily cohorts.

Finally, the results are ordered by user and by days since signup, producing a chronological view of post-signup behavior. This output is commonly used for cohort analysis, retention and engagement decay tracking, and building user activity curves that show how engagement changes over time after onboarding.

Task

Swipe to start coding

Your goal is to analyze how users engage with your product over time by dividing their activity into weekly cohorts after signup.

Write a SQL query using the users and events tables to:

Calculate, for each event, how many weeks have passed since the user's signup date (week 0 = days 0-6, week 1 = days 7-13, etc.).
Group the results by user_id, signup_date, and the calculated week number since signup.
Count the number of events each user performed in each week after signup.
Output the columns: user_id, signup_date, week_number, and events_in_week.

This analysis will help you understand user engagement patterns by weekly cohort windows relative to signup.

Solution

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 3

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Swipe to show menu