Repeat Buyers Across Halves

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

L4

Asked in

Amazon

Problem

With 2026 only partway through its second half, a retention analyst treats a shopper in transactions as holding steady when their purchases since July number at least half of what they bought from January through June. Return those users with each half's purchase count as a whole number.

First half buyer. Second half buyer. Same person.

Tables

transactions

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

Expected output

Worked solution and explanation

What this problem really is

This is a per-user comparison of two windows dressed up as retention analysis. The real skill: give every user two independent tallies inside one year, then judge the later tally against the earlier one for that same user. Anyone can count a user's rows. The trap is reaching for a fixed bar (four before July, two after) or measuring the post-July count against the whole year. On this data every active user clears a small fixed bar, so that list is just everyone, and measuring against the year doubles the bar and throws out the shoppers who actually held their pace.

Build it step by step

Step 1: Scope to 2026 first

transaction_date is text and spans several years. Pull the year out with strftime('%Y', ...) and CAST it to INTEGER before comparing to 2026: a text year never equals a number, so skipping the cast silently returns nothing. Doing this in WHERE means prior-year rows never reach the counts.

Step 2: Split each user into two counts

Same move for the month: CAST(strftime('%m', transaction_date) AS INTEGER). Then COUNT(CASE WHEN month <= 6 THEN 1 END) is the first half and COUNT(CASE WHEN month > 6 THEN 1 END) is the second. June lands in the first half, July in the second.

Step 3: One row per user_id

GROUP BY user_id collapses each user's year into one row, and the two counts ride along in the SELECT so the analyst sees how close each shopper sits to the line.

Step 4: Hold the back half to the front half

HAVING runs after grouping, so it can see both counts. Keep the user when twice the post-July count reaches the January-through-June count. Multiplying the second half by two keeps the comparison in integers, so no division and no rounding question.

The solution

SELECT
    user_id,
    COUNT(CASE WHEN CAST(strftime('%m', transaction_date) AS INTEGER) <= 6 THEN 1 END) AS first_half_purchases,
    COUNT(CASE WHEN CAST(strftime('%m', transaction_date) AS INTEGER) > 6 THEN 1 END) AS second_half_purchases
FROM transactions
WHERE CAST(strftime('%Y', transaction_date) AS INTEGER) = 2026
GROUP BY user_id
HAVING 2 * COUNT(CASE WHEN CAST(strftime('%m', transaction_date) AS INTEGER) > 6 THEN 1 END)
    >= COUNT(CASE WHEN CAST(strftime('%m', transaction_date) AS INTEGER) <= 6 THEN 1 END)

Measured against the year

2 * second_half >= COUNT(*) quietly means the second half must at least match the first. A shopper with six purchases before July and three after held exactly half their pace and still gets dropped.

Measured against the first half

Comparing 2 * second_half_purchases to first_half_purchases sets each user's bar from their own spring. Heavy spring buyers need more summer activity; light ones need less.

Common follow-up questions

• How would you return, for every year in `transactions`, the users whose second half held half their first-half pace? (Tests generalizing the bucketing to a per-year grain instead of a fixed year.)
• If the bar should scale with the months elapsed since July rather than a flat half, how would you compute it? (Tests whether the candidate can make the bar scale with elapsed time.)
• Given the table is partitioned by `transaction_date`, does wrapping the column in `strftime` still let the engine prune partitions, and how would you rewrite the filter if it does not? (Probes partition pruning on `transaction_date` at 120M rows.)