Spring Era is Open 😎

Time to update the design with warm shades that warm the soul. I put together a new design so that every post radiates spring energy 🔥

Okay, now let's talk about product questions 👇

You are given two tables - users and events. And 20 minutes for the task:
"Calculate retention by cohorts M0–M3, my dear fellow"

And that's where people get stuck.
Where do you even start? 🤔

The easiest way is not with SQL, but with a mental picture. Imagine a gym.
100 people bought a membership - that's your cohort.
Usually, this cohort is taken as 100%. That's M0.
But here's the nuance: it's not a law of nature, just an unwritten agreement.
The next day, 60 people came - Day 1 = 60%.
A week later, 40 came - Day 7 = 40%.

The logic doesn't change.
Just instead of days, it's months.
And instead of a gym, it's a product.

1️⃣ Junior level - understand how many people actually came to each cohort
SELECT
  DATE_TRUNC('month', registration_date) AS cohort_month,
  COUNT(DISTINCT user_id) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

If there's a mistake here, it's game over. Because DATE_TRUNC and DISTINCT are the basics of the product block.
At least for PostgreSQL and similar dialects.

2️⃣ Next level - understand who came back
WITH cohort_dec AS (
  SELECT user_id, registration_date
  FROM users
  WHERE registration_date >= '2023-12-01'
    AND registration_date < '2024-01-01'
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 1 THEN user_id END) AS day1,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 7 THEN user_id END) AS day7
FROM cohort_dec u
LEFT JOIN events e ON u.user_id = e.user_id;

Here come the rakes 🤔

This code is fine if your dates are stored as DATE, without time.
But if it's TIMESTAMP, simple equality can break the calculation.
Because one event is at 2023-12-08 00:01, another at 2023-12-08 19:42, and formally they are not the same value.

So you need to either cast to date or calculate using ranges.
The logic is always the same:
✅ fixed the cohort → looked at who came back

For M1, M2, M3, it's almost the same, but there is a key point:
👉 they compare not just dates, but the offset in calendar months relative to the registration month (sounds complicated, I'll explain now)

That is, not +30 days. Months have different lengths, so such a calculation drifts.
For monthly retention, they look at offset by calendar months, not just add 30 days.

❌ Where else do rakes await

1️⃣ Counting events instead of people
One user made 5 events.
And suddenly your retention is over 100%.
That means you're counting activity, not return of people.

2️⃣ Using INNER JOIN
And only those who returned remain in the selection.
Everyone who dropped out simply disappears.
You get a nice number. And a completely false picture.

3️⃣ Not fixing the calculation base
The cohort size is usually taken as 100%, and everything else is calculated from it.
If your base fluctuates, percentages turn into garbage.

And then the main trouble begins... a test of thinking and soft skills. That's where the thorniest path lies if you have little experience.
Because in an interview, they won't ask you:
"How to calculate retention?"

But they will ask:
"D30 dropped. Why?"

And there you go... And here SQL no longer impresses anyone.

✅ A normal thought process is:
- break down retention by cohorts
- look at acquisition channels
- check activation
- understand if onboarding was broken
- compare behavior before and after releases
You can dig deeper, but these basics are enough to start.

❌ An answer that will immediately end your conversation
"Well... retention decreased because users started returning less..."

Thank you, Captain Obvious. But that's not an analyst's answer, it's a guess.

In simple terms:
Retention is not about SQL, it's about behavior.
SQL is just a shovel you use to dig up the number.

If you can't explain why people stopped coming back,
then your percentages themselves tell you nothing 🤷‍♀️

Have you ever had problems with the product block? And do you generally like the product direction or do you prefer the engineering part? 👇