The Cannibalization Report

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

Domain

SQL

Difficulty

hard

Seniority

L6

Asked in

Thumbtack

Problem

A retail marketplace wants a cannibalization read on its newest arrivals, the products whose very first sale landed in the last 9 months, comparing how the whole category was selling in the 30 days before that first sale against the 30 days after. Show the product name, category, the before and after category sales, and the percentage change between them, counting a launch into a category that had no sales at all beforehand as entirely new demand worth a 100% gain.

The new product launched. The old one suffered.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

transactions

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

Expected output

Worked solution and explanation

What this problem really is

Strip the marketing gloss and this is a per-entrant event study: the event is each product's OWN first sale, and what you measure around it is the ENTIRE category's revenue, not the product's. That kills a single GROUP BY category on a fixed month, because every entrant's windows hang off a different day. The move that cracks it: join products back to itself on category so each entrant fans out to every sibling's transactions, then sum those into two 30-day windows anchored to that entrant's launch. And the launches that matter most, the ones that created their category's first-ever sales, have a before-window of exactly zero. Divide by it naively and SQL quietly drops every one of them from the report. Keep those rows with a LEFT JOIN, fill the gap with COALESCE, and guard the divide with NULLIF so a zero base reads as a clean 100% of net-new demand.

Build it in five moves

Step 1: Pin each product's launch date

first_sale: group transactions by product_id, take MIN(transaction_date). The HAVING MIN(transaction_date) >= DATE('2026-09-16', '-9 months') keeps genuine new entrants, whose FIRST-ever sale is recent, not products that merely sold lately.

Step 2: Attach name and category

entrant: inner-join first_sale back to products for product_name and category, carrying the entry_date anchor forward. This is the row we report on.

Step 3: Fan out to the whole category for the after window

post_win: join each entrant to products cp ON cp.category = e.category then to transactions, so every entrant fans out to every sibling's sales, and conditionally SUM total_amount over [entry, entry+30d). Every entrant appears here, because its own first sale lands in this window.

Step 4: Build the before window so empty categories drop out

pre_win: same fan-out, but the WHERE filters to [entry-30d, entry). The key difference is that this is a filtered SUM, not a conditional one, so an entrant whose category sold NOTHING before its launch produces NO row. Those category-creating launches are exactly the rows a careless inner join loses.

Step 5: Keep the zero-baseline launches and compute the change

merged: JOIN post_win (always present) but LEFT JOIN pre_win so zero-baseline entrants survive, then COALESCE(pre, 0). The final SELECT rounds to 2dp and computes the percent change; NULLIF blanks a zero base and COALESCE swaps in post so the row reads a clean 100% instead of dividing by zero.

The solution

WITH first_sale AS (
    SELECT product_id,
           MIN(transaction_date) AS entry_date
    FROM transactions
    GROUP BY product_id
    HAVING MIN(transaction_date) >= DATE('2026-09-16', '-9 months')
),
entrant AS (
    SELECT fs.product_id,
           p.product_name,
           p.category,
           fs.entry_date
    FROM first_sale fs
    JOIN products p ON p.product_id = fs.product_id
),
post_win AS (
    SELECT e.product_id,
           SUM(CASE WHEN t.transaction_date >= e.entry_date
                     AND t.transaction_date <  DATE(e.entry_date, '+30 days')
                    THEN t.total_amount ELSE 0 END) AS post_entry_sales
    FROM entrant e
    JOIN products cp ON cp.category = e.category
    JOIN transactions t ON t.product_id = cp.product_id
    GROUP BY e.product_id
),
pre_win AS (
    SELECT e.product_id,
           SUM(t.total_amount) AS pre_entry_sales
    FROM entrant e
    JOIN products cp ON cp.category = e.category
    JOIN transactions t ON t.product_id = cp.product_id
    WHERE t.transaction_date >= DATE(e.entry_date, '-30 days')
      AND t.transaction_date <  e.entry_date
    GROUP BY e.product_id
),
merged AS (
    SELECT e.product_name,
           e.category,
           COALESCE(pre.pre_entry_sales, 0) AS pre_entry_sales,
           po.post_entry_sales              AS post_entry_sales
    FROM entrant e
    JOIN post_win po ON po.product_id = e.product_id
    LEFT JOIN pre_win pre ON pre.product_id = e.product_id
)
SELECT product_name,
       category,
       ROUND(pre_entry_sales, 2)  AS pre_entry_sales,
       ROUND(post_entry_sales, 2) AS post_entry_sales,
       ROUND((post_entry_sales - pre_entry_sales) * 100.0
             / COALESCE(NULLIF(pre_entry_sales, 0), post_entry_sales), 2) AS pct_change
FROM merged;

Common follow-up questions

• How would you exclude the launched product's own sales to measure pure sibling cannibalization? (Tests adding a predicate like `cp.product_id <> e.product_id`.)
• Would you rather show a blank than 100% for a zero baseline, and how would that change the query? (Probes whether reporting NULL vs. 100% reshapes the `NULLIF`/`COALESCE` guard.)
• What if you needed rolling 7-day granularity instead of one 30-day window? (Probes date-series generation and windowed aggregation at finer grain.)
• What if `transactions` had 10B rows and this ran daily? (Probes materialized views, incremental computation, or partition-level pre-aggregation.)