Honeymoon Phase

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

Lyft

Problem

We track customers as signup cohorts, one per registration year, to gauge how sticky each class was right after joining. Walk the cohorts from the oldest signup class forward, and for each 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.

Every signup class starts hot. Which ones spend the year they arrive?

Tables

users

Columns: user_id INTEGER, signup_date TEXT, username TEXT, email TEXT, account_status TEXT, age_bucket TEXT

transactions

Columns: transaction_id INTEGER, user_id INTEGER, transaction_date TEXT, product_id INTEGER, quantity INTEGER, total_amount REAL

Expected output

Worked solution and explanation

The problem under the costume

This is a conditional ratio scoped to a cohort, dressed up as a stickiness report. Every signup year needs two counts from the same rows: the transactions that landed in the signup year, and all of that cohort's transactions. Anyone can join transactions to users. What separates candidates is where the year condition goes. Put it in WHERE and the denominator dies with the rows it removed. Only same-year transactions survive, so every cohort reports 100.00 and the report hides the very signal it exists to measure. The condition belongs inside the aggregate, where it decides what gets counted without deciding what exists.

Break down the requirements

Step 1: Join transactions to users

Join on user_id to put each transaction's transaction_date next to its customer'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 cohort, so the grouping key is strftime('%Y', u.signup_date), not the raw date. Two customers in the seed signed up with a time part on signup_date, and reading the year still puts them in the right bucket.

Step 3: Count same-year transactions inside the aggregate

SUM(CASE WHEN strftime('%Y', t.transaction_date) = strftime('%Y', u.signup_date) THEN 1 ELSE 0 END) counts the matches while COUNT(*) still sees the cohort's cross-year transactions. That keeps 2024 at 10.61 instead of 100.00.

The solution

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

Filter first

WHERE keeps only rows whose years match, so COUNT(*) equals the numerator and every cohort prints 100.00.

Count conditionally

The CASE inside SUM marks matches while COUNT(*) still sees every transaction, so 2024, 2025 and 2026 come out at 10.61, 10.61 and 60.29.

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 in `transactions` and deduplicates on `transaction_id` 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 the join on `user_id` as the hotspot 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 under an inner join.)