The Subscription Ghost

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

PayPal

Problem

A billing-integrity team is chasing accidental recurring charges: a customer billed the same amount for the same product about a month after the last time, usually a duplicate subscription or a botched retry. Within each user and product pairing, compare every charge to the one immediately before it in time, and surface the charges that repeat the previous amount exactly and land 35 days or fewer after it, so the team can clear that backlog.

Some charges come back to haunt the same card a month later.

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

Why this problem exists in real interviews

Strip the billing costume and this is a consecutive-row comparison inside each (user_id, product_id) group: does this charge repeat the amount of the one right before it, close enough in time to look like a duplicate? Anyone can filter on equal amounts. The separator is scoping the 'previous charge' to the SAME user and product with LAG, and doing real date arithmetic on the gap. Partition by user alone and you conflate two different products; compare against any earlier charge instead of the immediately previous one and you over-count.

Break down the requirements

Step 1: Isolate the previous-charge values in a CTE

The lagged CTE pulls each transaction's previous amount and previous date for the same (user_id, product_id) combination using LAG. This separation keeps the row-to-row comparison out of the final filtering step.

Step 2: Filter to ghost charges and surface them

The outer query keeps only the ghost charges, where total_amount matches the previous charge's amount AND the two charges sit 35 days or fewer apart, then returns those flagged transactions ordered by transaction_date. Each surfaced row is one accidental repeat the billing team can investigate.

The solution

WITH lagged AS (
    SELECT *, LAG(total_amount) OVER (PARTITION BY user_id, product_id ORDER BY transaction_date) AS prev_amount, LAG(transaction_date) OVER (PARTITION BY user_id, product_id ORDER BY transaction_date) AS prev_date
    FROM transactions
)
SELECT transaction_id, user_id, product_id, total_amount, transaction_date
FROM lagged
WHERE total_amount = prev_amount AND (julianday(transaction_date) - julianday(prev_date)) <= 35

Common follow-up questions

• What would happen to your flagged charges if `transactions.transaction_id` contained duplicate rows that you did not expect? (Tests whether the candidate considers data quality issues in `transaction_id` and uses deduplication where needed before flagging ghost charges.)
• `transactions.transaction_id` has roughly 80,000,000 distinct values. What index strategy would you use to avoid a full scan when partitioning by `user_id` and `product_id`? (Tests indexing knowledge specific to the high-cardinality `transaction_id` column in `transactions`.)
• Your query uses LAG to compare each charge to the prior one. What happens for the first transaction in a (`user_id`, `product_id`) partition where there is no previous charge, and how would you handle it? (Tests edge-case handling when LAG returns NULL for the first charge in a (`user_id`, `product_id`) partition.)