Write the measurement contract before the query
This original project asks: among users with a complete seven-day observation window, how many activated and then made a qualifying paid purchase? The reporting cutoff is day 14. A user is mature when signup day plus seven is at or before that cutoff. Events qualify in the half-open window starting at signup and ending just before signup plus seven.
A qualifying purchase must occur on or after the first activation day, within that user's seven-day window, and have paid status. Fully refunded orders are excluded from this particular metric. Amounts are stored as integer cents. These are explicit teaching definitions, not universal definitions of conversion or recognized revenue.
CBRE's technology internship lists data and product-management tracks, illustrating that technical employers recruit for different analytical tasks. This is independent preparation, not that employer's assessment. The downloadable SQL creates its own synthetic database and does not need an account, customer data or a paid analytics product.
Sources[1]
Inspect what the small dataset was designed to expose
There are eight synthetic users, nine raw activation-event rows and eight orders. One event is an exact duplicate of another, leaving eight unique event records. Two users have not yet completed seven observation days at the cutoff and therefore do not belong in the mature-cohort denominator.
| User | Signup day | First activation day | Mature at day 14? |
|---|---|---|---|
| u1 | 0 | 1 | Yes |
| u2 | 0 | 2 | Yes |
| u3 | 1 | 3 | Yes |
| u4 | 2 | None | Yes |
| u5 | 3 | 4 | Yes |
| u6 | 5 | 6 | Yes |
| u7 | 10 | 11 | No |
| u8 | 12 | 13 | No |
The dataset also contains a second activation for one user, a paid order before another user's first activation, a fully refunded order and two paid orders outside their users' windows. They are not all bad data. Some are legitimate records that do not qualify for the specific funnel being measured. Keep that distinction clear.
Build one trustworthy user-level row before calculating a rate
Start by checking whether repeated event IDs agree on their user and day. An exact duplicate can be removed under this fixture's contract. The same ID attached to contradictory values is a data-integrity failure that should stop the calculation, not be resolved by arbitrarily taking the first row.
| Stage | One row represents | Check |
|---|---|---|
| Clean events | One unique event ID | Contradictory repeated IDs fail validation |
| Mature users | One eligible user | Signup day + 7 is no later than cutoff |
| First activation | One activated user | Activation falls inside that user’s window |
| Qualifying orders | One buyer aggregate | Paid, after activation and before window end |
| Final user table | One mature user | Users without activity remain present |
Next, select mature users, then calculate each user's first qualifying activation. Aggregate qualifying orders separately to one row per user before joining the results. Otherwise, joining several activation events to several orders can multiply order amounts. Counting distinct users does not repair duplicated revenue in the same query.
SQLite's SELECT documentation explains the distinction between join conditions and filtering after a join. Here, a left join is needed to retain a mature user with no activation or purchase. A filter on the right-hand table placed in the final WHERE clause can accidentally remove that user and inflate conversion. The downloadable query keeps eligibility predicates in the appropriate stage rather than relying on a final filter to repair the result.
Sources[2]
Reconcile the expected answer
Six users are mature. Five of them activate in their window: u1, u2, u3, u5 and u6. Only u1, u2 and u5 make a qualifying purchase after activation and before their window ends. Their included paid amounts are 4,000, 5,000 and 6,000 cents, totaling 15,000 cents, or $150.
The mature-user activation rate is 5/6, approximately 83.33%. The mature-user purchase rate is 3/6, or 50%. Conditional on activation, the purchase rate is 3/5, or 60%. Each answers a different question; report its numerator and denominator rather than calling all three conversion.
User u3's day-two paid order is before the day-three activation, and the day-five order is fully refunded. Neither qualifies for this ordered funnel. That does not mean the earlier paid order is invalid or that the business never received money from u3. Similarly, u7's paid order is excluded because the cohort is immature, not because the order is false.
Test the boundaries that commonly produce a plausible wrong answer
The Python runner executes the SQL in an in-memory SQLite database and checks the expected totals. It also tests that an additional exact duplicate does not change the answer, that a contradictory event ID raises an error, that a purchase exactly at signup plus seven is excluded, and that an immature user does not enter the denominator.
Add a test for a purchase without activation and confirm it does not enter this ordered funnel. Keep the order in the underlying table so a different, clearly defined purchase metric could still include it. This avoids turning one report's eligibility rule into destructive data cleaning.
The exercise stores days, so it cannot establish whether two events on the same day occurred in a particular intraday order. The contract explicitly permits purchases on the activation day. A production analysis needing precise sequence would require timestamps, a timezone convention and a tie policy; it should not invent those details from day-level data.
Finish with the measurement decision, not just the SQL file
Deliver the query, input data, tests and a short memo explaining the cohort, window and exclusions. Include a user-level result table so another person can trace the $150 total to qualifying orders. State that the fixture tests logic rather than estimating how real users behave.
A useful next question is whether the business really needs an ordered activation funnel, any seven-day purchase, or eventual retained revenue. Those are different measures and may require different queries. Do not select the metric that makes a product change look successful after seeing the result.
In an interview, explain why the pre-activation purchase remains in the data, why the mature denominator is six, and how a many-to-many join could corrupt the sum. Those concrete decisions demonstrate more analytical understanding than saying you built a dashboard or know advanced SQL.