Marketplace Price War
e Bay, Amazon
Problem Statement
On eBay, price wars can occur when sellers use automated repricing tools. We want to identify sellers who are "reactively" lowering their prices.
A reactive price drop is defined as:
- Seller A (Competitor) lowers the price of Product X.
- Seller B (Subject) lowers the price of the same Product X within 10 minutes after Seller A change.
- Seller B and Seller A must be different sellers.
Write a query to find the list of sellers who have engaged in at least one such reactive price drop.
Rules:
- A price drop is identified when the new_price is strictly less than the previous_price in the price_changes table.
- The reaction must occur between 0 and 10 minutes (inclusive) after the competitor drop.
- Output columns: seller_id, reactive_drops_count.
- Result must be sorted by reactive_drops_count descending, then seller_id ascending.
Tests your understanding of
Self Joins, Time Series and Window Functions
Input Tables
price_changes
| change_id(INTEGER) | seller_id(INTEGER) | product_id(INTEGER) | previous_price(DECIMAL) | new_price(DECIMAL) | change_timestamp(TIMESTAMP) |
|---|---|---|---|---|---|
| 1 | 10 | 1 | 100 | 95 | 2026-03-04 10:00:00 |
| 2 | 20 | 1 | 100 | 94 | 2026-03-04 10:05:00 |
| 3 | 10 | 1 | 95 | 90 | 2026-03-04 10:08:00 |
| 4 | 30 | 2 | 50 | 45 | 2026-03-04 11:00:00 |
| 5 | 40 | 2 | 50 | 44 | 2026-03-04 11:02:00 |
| 6 | 50 | 2 | 50 | 48 | 2026-03-04 11:20:00 |
Expected Output
| seller_id(INTEGER) | reactive_drops_count(INTEGER) |
|---|---|
| 10 | 1 |
| 20 | 1 |
| 40 | 1 |
Tags
HardSelf JoinsTime SeriesWindow Functions
35-45 min
28%