# Honeymoon Phase > Every signup class starts hot. Which ones spend the year they arrive? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 ## Problem We track customers as signup cohorts, one per registration year, to gauge how sticky each class was right after joining. For every signup year, take the transactions made by that year's cohort and report the share that landed in the same calendar year the customer registered, as a percentage rounded to two decimals. ## Worked solution and explanation ### The problem under the costume Beneath the first-year enthusiasm framing, this is a cohort-scoped conditional aggregation. For each signup-year cohort you need a numerator (transactions made in that same year) and a denominator (all of that cohort's transactions), computed together in one pass. Anyone can join the two tables. The separator is seeing that the percentage lives INSIDE each cohort, not across the whole table, and that the match is a year-to-year comparison, not a date equality. Compare full dates and every cohort collapses to near zero; compute the ratio globally and you have answered a different question than the one asked. > **Trick to solving** > > When the prompt asks for a percentage of rows that satisfy a condition, and it asks for that percentage PER group, conditional aggregation gives you the numerator and denominator of every group in a single pass. > > 1. Spot the ratio: a subset count over a total count, evaluated within each cohort > 2. Use `SUM(CASE WHEN condition THEN 1 ELSE 0 END)` for the numerator and `COUNT(*)` for the denominator > 3. Multiply by `100.0` and `ROUND` to format the percentage --- ### Break down the requirements #### Step 1: Join `transactions` to `users` Join on `user_id` to bring each transaction's `transaction_date` next to its user's `signup_date`. The inner join also drops any transaction whose `user_id` has no registered user, so only matched transactions reach the aggregate. #### Step 2: Bucket customers by signup year Each output row is one signup cohort, so the grouping key is the signup year: `strftime('%Y', u.signup_date)`. Grouping on the extracted year (not the raw date) collapses everyone who registered in the same calendar year into a single bucket. #### Step 3: Compare signup year to transaction year within each cohort Inside each cohort, the numerator is the count of transactions whose year matches the signup year and the denominator is every transaction in that cohort. `SUM(CASE WHEN strftime('%Y', t.transaction_date) = strftime('%Y', u.signup_date) THEN 1 ELSE 0 END)` over `COUNT(*)` gets both in one pass; multiply by `100.0` and `ROUND` to 2 decimals. --- ### The solution **Same-year transaction rate per signup cohort** ```sql SELECT strftime('%Y', u.signup_date) AS signup_year, ROUND(100.0 * SUM(CASE WHEN strftime('%Y', t.transaction_date) = strftime('%Y', u.signup_date) THEN 1 ELSE 0 END) / COUNT(*), 2) AS same_year_pct FROM transactions t JOIN users u ON t.user_id = u.user_id GROUP BY strftime('%Y', u.signup_date) ORDER BY signup_year ``` > **Cost analysis** > > The join dominates at ~65M transaction rows; an index on `users.user_id` turns each lookup into a seek. The year extraction and grouping run per matched row, and because numerator and denominator share the same aggregate there is no second scan for the total. > **Interviewers watch for** > > Interviewers watch for whether you scope the numerator and denominator to the same cohort with conditional aggregation, instead of running one query for same-year counts, another for cohort totals, and dividing in application code. > **Common pitfall** > > Two ways to get the wrong shape: compute the ratio across the whole table instead of per cohort, which loses the grouping the question asks for; or compare full dates (`transaction_date = signup_date`) instead of years, which matches almost nothing and drives every cohort to near zero. --- ## Common follow-up questions - What would happen to a cohort's same-year percentage if `transactions` contained duplicate transaction rows you did not expect? _(Tests whether the candidate considers data quality issues in transaction_date and uses deduplication where needed.)_ - If `transactions` grew to billions of rows, which part of your query would become the bottleneck given the cardinality of `user_id`? _(Tests ability to identify performance hotspots related to the user_id join at scale.)_ - Your query counts only transactions that match a registered user. What happens to the cohort percentages if many transactions carry a `user_id` with no row in `users`? _(Tests awareness of how unmatched rows affect the cohort denominators, since the inner join only counts matched transactions.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/honeymoon_phase) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.