# The Subscription Ghost > Some charges come back to haunt the same card a month later. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 ## 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. ## 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. > **Trick to Solving** > > Whenever the prompt asks you to compare a row to its predecessor or successor, that is a `LAG`/`LEAD` signal. > > 1. Identify the comparison direction (previous charge for the same user and product) > 2. Partition by the grouping key (here, `user_id` and `product_id` together) > 3. Order by `transaction_date` so the previous row is the chronologically prior charge > 4. Compare amounts and compute the day gap in the outer query --- ### 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 **Lag-compare to flag ghost charges** ```sql 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 ORDER BY transaction_date ``` > **Cost Analysis** > > With ~80M rows, the window function partitions by (`user_id`, `product_id`) and orders by `transaction_date` before the outer filter narrows to the matching ghost charges; CTEs materialize intermediate results, which can be beneficial or costly depending on the engine. A composite index on (`user_id`, `product_id`, `transaction_date`) would let the partition and order step seek instead of fully scanning and sorting. > **Interviewers Watch For** > > Interviewers watch for whether you decompose the problem into named, testable stages rather than nesting everything; whether you reach for window functions or attempt a self-join for the charge-to-prior-charge comparison; how you handle the day-gap arithmetic and whether you account for edge cases like a 35-day window straddling month boundaries. > **Common Pitfall** > > Using string comparison instead of proper date arithmetic (julianday here) for the 35-day window can miss edge cases at midnight boundaries and silently mis-flag or skip ghost charges. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_subscription_ghost) - [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.