100% activation, three inactive users: a SQL interview trap

Suppose you're analysing a project-management app. People sign up to organise their work, but some leave without creating a project. You want to know how many new users take that first step within seven days of registering.

In this example, creating at least one project during those seven days counts as activation. The activation rate is the percentage of registered users who do it.

We'll compare people who signed up in two different weeks:

Signup week beginning	People who signed up	People who created a project within seven days
3 August 2026	4	3
10 August 2026	6	4

Among the people who signed up in the first week, three created a project; among those who signed up in the second, four did. The product manager sees the increase and says: "More people created a project. Activation improved."

The counts did rise. The percentages tell a different story: 3 out of 4 is 75.0%, while 4 out of 6 is 66.7%. To compare the rates, we need to count everyone who registered in each week, including those who never created a project.

Now imagine the SQL report returns 100% for both weeks. Below, we'll reproduce that result, find the users it left out, and correct the calculation. The example uses PostgreSQL; it assumes you can read a SELECT with a join and GROUP BY.

The data: registrations and recorded actions

We need two tables. interview_users lists every registered user once. Its signup_week column stores the Monday of the week when that person registered. This lets us compare people by when they joined, regardless of when they later created a project.

interview_events records actions reported by the app: project_created for creating a project and page_view for viewing a page. The same user can have several rows. A delivery retry can also record the same action again, under a new receipt_id, so counting rows does not tell us how many people acted.

The data is invented for this exercise. Each person has been observed for a full seven days after signup, and their event rows have already been limited to that period. We can therefore concentrate on counting users. There are no timestamp columns in this simplified dataset; these queries cannot apply the seven-day limit to an unfiltered event log.

Run this setup once in an empty PostgreSQL database:

CREATE TABLE interview_users (
  user_id TEXT PRIMARY KEY,
  signup_week TEXT NOT NULL
);
CREATE TABLE interview_events (
  receipt_id INTEGER PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES interview_users,
  event_name TEXT NOT NULL
);
INSERT INTO interview_users VALUES
('U01','2026-08-03'),('U02','2026-08-03'),
('U03','2026-08-03'),('U04','2026-08-03'),
('U05','2026-08-10'),('U06','2026-08-10'),
('U07','2026-08-10'),('U08','2026-08-10'),
('U09','2026-08-10'),('U10','2026-08-10');
INSERT INTO interview_events VALUES
(1,'U01','project_created'),(2,'U01','project_created'),
(3,'U02','project_created'),(4,'U03','project_created'),
(5,'U05','project_created'),(6,'U05','project_created'),
(7,'U05','project_created'),(8,'U06','project_created'),
(9,'U07','project_created'),(10,'U08','project_created'),
(11,'U04','page_view'),(12,'U09','page_view'),
(13,'U05','page_view');

Read a few rows before writing the report. U01 has two project_created records, but is still one person. U05 has three such records and one page view. U04 and U09 have only page views, so neither activated. U10 appears in the users table but has no event rows at all.

How the query arrives at 100%

This first attempt starts from the users table and uses a LEFT JOIN to attach their events. It then selects project-creation events in WHERE and counts distinct users:

SELECT
  u.signup_week,
  COUNT(DISTINCT u.user_id) AS registered_users,
  COUNT(DISTINCT e.user_id) AS activated_users,
  ROUND(
    100.0 * COUNT(DISTINCT e.user_id)
    / COUNT(DISTINCT u.user_id), 1
  ) AS activation_pct
FROM interview_users u
LEFT JOIN interview_events e ON e.user_id = u.user_id
WHERE e.event_name = 'project_created'
GROUP BY u.signup_week
ORDER BY u.signup_week;

signup_week	registered_users	activated_users	activation_pct
2026-08-03	3	3	100.0
2026-08-10	4	4	100.0

Compare the registered_users column with the registration totals at the start: it says 3 instead of 4, and 4 instead of 6. The percentages are wrong because the denominator is already missing people.

To see where they disappeared, follow U04, U09 and U10 through the join and the filter:

User	Row after LEFT JOIN	Row after the project filter
U04	A page_view event	Removed: page_view does not equal project_created
U09	A page_view event	Removed for the same reason
U10	NULL in the event columns	Removed: comparing NULL with project_created does not return true

The join itself kept all three. For U10, it supplied NULL event columns because it found no matching event. The later WHERE condition kept only rows where e.event_name = 'project_created' was true. U04 and U09 failed that comparison; U10's comparison was unknown because the event name was NULL. All three were removed before the counts were calculated.

That leaves only people who created a project. Both counts now describe the same people, so dividing one by the other gives 100%.

COUNT(DISTINCT ...) does prevent several event rows from counting a person twice. It cannot restore someone removed by WHERE. PostgreSQL's join documentation shows why placing an event restriction in ON and placing it in WHERE produce different results with a left join.

Why allowing NULLs only fixes one of the three users

Once you notice U10's NULL event columns, it is tempting to add OR e.user_id IS NULL. That should allow users with no events to remain. Rather than calculate a percentage yet, list the users this proposed filter would keep:

SELECT DISTINCT u.user_id
FROM interview_users u
LEFT JOIN interview_events e ON e.user_id = u.user_id
WHERE e.event_name = 'project_created'
   OR e.user_id IS NULL
ORDER BY u.user_id;

U10 returns, but U04 and U09 are still missing. Their page views matched the join, so their event columns contain values, including a non-NULL e.user_id. Neither part of the OR condition is true for them.

The query returns eight IDs: U01, U02, U03, U05, U06, U07, U08 and U10. A left join supplies NULL columns when it finds no match; it does not create a replacement NULL row after WHERE rejects an existing match.

This distinction matters when you test the fix. A person with no events and a person with only page views are both inactive, but this filter treats them differently. The denominator needs to include both.

Put the project condition in the join

We want to keep every registered user and attach an event only if it records a project creation. Put both requirements for an event match in ON: its user ID must match, and its name must be project_created. Remove the event condition from WHERE:

SELECT
  u.signup_week,
  COUNT(DISTINCT u.user_id) AS registered_users,
  COUNT(DISTINCT e.user_id) AS activated_users,
  ROUND(
    100.0 * COUNT(DISTINCT e.user_id)
    / COUNT(DISTINCT u.user_id), 1
  ) AS activation_pct
FROM interview_users u
LEFT JOIN interview_events e
  ON e.user_id = u.user_id
 AND e.event_name = 'project_created'
GROUP BY u.signup_week
ORDER BY u.signup_week;

The page views belonging to U04 and U09 no longer match this join condition. Those users now remain with NULL event columns, just like U10. Their IDs in u.user_id are still present and contribute to the registration count. The NULLs in e.user_id do not contribute to the activation count.

signup_week	registered_users	activated_users	activation_pct
2026-08-03	4	3	75.0
2026-08-10	6	4	66.7

The registration counts are back to 4 and 6, and the rates agree with the calculation by hand. We still need DISTINCT: U01 and U05 each have several project-creation records. Moving the condition into ON preserves inactive users, but does not remove repeated matches for active ones.

An alternative: calculate each user's activation first

Another way to make the calculation easier to inspect is to build a list with exactly one row per user. Give each person a 1 if any of their events records a project creation, or a 0 otherwise. Then count the people in each signup week and add their 1s.

The WITH per_user AS (...) part of this query names that intermediate result per_user. The final SELECT reads it:

WITH per_user AS (
  SELECT
    u.user_id,
    u.signup_week,
    MAX(CASE WHEN e.event_name = 'project_created'
      THEN 1 ELSE 0 END) AS activated
  FROM interview_users u
  LEFT JOIN interview_events e ON e.user_id = u.user_id
  GROUP BY u.user_id, u.signup_week
)
SELECT
  signup_week,
  COUNT(*) AS registered_users,
  SUM(activated) AS activated_users,
  ROUND(100.0 * SUM(activated) / COUNT(*), 1) AS activation_pct
FROM per_user
GROUP BY signup_week
ORDER BY signup_week;

Inside per_user, CASE produces 1 for a project event and 0 otherwise. MAX then chooses the largest value for each user. U05 has several 1s, but their maximum is still 1. U04 and U09 have only 0s. U10's NULL event name also takes the ELSE 0 branch.

Because per_user has one row per registered user, the outer COUNT(*) counts people. SUM(activated) counts those whose flag is 1. The result is again 75.0% and 66.7%.

Both solutions multiply by 100.0 before dividing so that PostgreSQL uses decimal arithmetic. Neither has been benchmarked here; the reason to show both is that one fixes the join directly, while the other makes each person's contribution explicit.

We can now answer the manager: "More people created a project among those who signed up in the week of 10 August: four instead of three. But more people also signed up, so the share who created a project fell from 75.0% to 66.7%."

These are descriptive results for ten users. They do not establish a lasting trend, or tell us why the percentages differ.

Check the fix by changing the data

Getting the expected percentages on the original rows is a useful start. We can also check whether the queries respond correctly when we add a repeated event, an inactive user, or a week with no activations.

Run the additions below in order, once each, after the original setup. After each addition, rerun either corrected query. The deliberately broken query near the start is not the one to reuse here.

First, add another project-creation record for U05, who already has several:

INSERT INTO interview_events
VALUES (14, 'U05', 'project_created');

U05 should still count as one activated person, so the rates should remain 75.0% and 66.7%. Next, add U11 to the registrations for the week of 10 August, without adding any events:

INSERT INTO interview_users VALUES ('U11', '2026-08-10');

There are now seven registered users in that week, and still four who created a project. Its rate should become 4 / 7, rounded to 57.1%. Unlike another event for U05, this addition changes the number of people we are measuring.

Finally, add U12 as the only registered user in the week of 17 August. Again, add no events:

INSERT INTO interview_users VALUES ('U12', '2026-08-17');

The report should gain a row for 17 August: one registered user, zero activated users and 0.0%. A query that starts from project events alone would miss this week entirely.

This is different from a week when nobody registered. With no users, there is no group to aggregate in these tables and no activation percentage to calculate. Zero out of one is 0%; zero out of zero is undefined.

Change	What must happen
Another project record for U05	Registered and activated user counts stay the same
U11 signs up in the week of 10 August, with no events	Registered users: 6 to 7; activated users: still 4; rate: 57.1%
U12 is the only signup in the week of 17 August, with no events	A new row: 1 registered, 0 activated, 0.0%

The examples were checked in PGlite, a PostgreSQL runtime. Both corrected queries produced the expected results on the original data and after each addition. The broken WHERE query and the OR IS NULL patch were also run to verify the missing users shown above.

What the example assumes about real event data

We started with events already selected from each person's first seven days after signup. In a real app, you would need signup and event timestamps to make that selection. You would also need to decide what "seven days" means: exactly 168 hours after registration, or a calendar-based period with an agreed timezone.

Make sure each person has had the whole period to act. Someone who registered an hour ago cannot yet be compared fairly with someone observed for a full week.

Finally, check whether event collection is complete. In this exercise, the absence of a project event means no activation was recorded. In a real system, an absent event could also mean a tracking failure or delayed delivery. Correct SQL cannot distinguish those causes from these two tables alone.

The useful habit to take from this example is to inspect who gets counted before checking the percentage. Include someone who acted, someone who did something else, and someone who did nothing. Then verify that repeated events do not become extra people.

Try the same case in the browser

I build SQLBrief, where this case is available as a free browser exercise in English. The two tables are already loaded. You choose who to count, write the SQL and answer the product manager, then open a review of the result. A Russian version is available too. No signup or database installation is needed.

You can use an optional 20-minute timer or work without one. Since this article gives the solution, use the exercise to rehearse the explanation, not to measure how you would do on an unfamiliar interview question. The browser starts with the original ten users; U11, U12 and the extra event above are experiments for your own database.

If you try it, tell me where you got stuck or which part of the explanation was hard to follow. You can comment here or use the feedback link in the exercise.