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

Your query returns 100% activation for both signup groups. It runs without an error. Three of the ten users never created a project.

Here is a small PostgreSQL exercise that makes the mistake visible. You'll need SELECT, JOIN and GROUP BY. The data is synthetic, and every user has a complete seven-day observation window after signup. Creating at least one project during that window counts as activation.

The product manager's claim is simple: "Four users activated in the newer signup group, compared with three in the older group. Activation improved."

Before checking the claim, we need a denominator: everyone who signed up in each group, including people with no project event.

Ten users, thirteen event receipts

Run this setup 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'); 

One user can have several event receipts, including repeated deliveries. U01 has two project receipts; U05 has three. U04 and U09 have only page views. U10 has no events.

The query that makes everyone successful

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; 

The column name registered_users is lying. WHERE has already removed U04, U09 and U10 before either count runs. Both counts now describe people who created a project.

COUNT(DISTINCT ...) prevents repeated receipts from counting the same user twice. It cannot bring back users removed by the filter. PostgreSQL's join documentation explains why a restriction in ON and a restriction in WHERE can produce different results for an outer join.

Give each user one row before calculating the rate

One approach is to keep all users and assign each a 0 or 1:

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; 

MAX makes repeated project receipts harmless: one or several matches still produce 1. A user with only page views, or no events at all, gets 0. The outer query now counts users rather than event receipts.

Four is more than three, but four out of six is a smaller share than three out of four. The manager can say that more users activated. These data do not support a higher activation rate, and they cannot tell us what caused the difference.

Test the meaning of the query

A useful check is to change something that should leave the answer alone:

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

Rerun the corrected query. The result should be unchanged. Then add a registered user with no events:

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

The newer group should now contain seven users and four activated users: 57.1%. The first edit added a receipt; the second added a person to the denominator. A query that treats those edits identically is measuring the wrong thing.

For a final check, add a user in a new signup week with no events. That group should appear with 0% activation, rather than disappear.

Use it as an interview rehearsal

At SQLBrief, we're building SQL practice around writing a query and explaining its result. This case is available as a free browser exercise in English, with the original data prepared, an optional 20-minute timer and a review. It also has a Russian version. There is no signup or database installation.

It's the same case, so after reading this solution, treat it as rehearsal rather than an unseen test. The browser starts with U01–U10; the extra rows above are local experiments, not part of its expected answer.

If you're practising for an analyst interview, we'd like feedback on this one attempt: what was unclear, what slowed you down, and whether the review helped. You can reply here or use the feedback link in the exercise. A critical answer is useful too.