Analyze Seller Compliance and Customer Purchase Patterns
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
SELLER_STATUS
+-----------+------------+-----------+
| seller_id | date | status |
+-----------+------------+-----------+
| 1 |2019-01-01 |reinstate |
| 1 |2020-06-01 |block |
| 2 |2020-01-01 |reinstate |
| 2 |2021-03-01 |suspend |
+-----------+------------+-----------+
PURCHASE_HISTORY
+----+------------+---------+
| id | order_date | product |
+----+------------+---------+
| 10 |2010-05-02 |A |
| 10 |2018-07-19 |B |
| 11 |2010-03-11 |A |
+----+------------+---------+
##### Scenario
E-commerce marketplace wants to analyze seller compliance actions and customer purchasing behavior.
##### Question
List seller_id values that experienced a 'block' or 'suspend' status AFTER any 'reinstate' status. 2. For each seller, return the first status that occurs AFTER their most recent 'reinstate'. 3. Return id values that purchased product 'A' at least once in 2010 AND product 'B' at least once in 2018.
##### Hints
Use window functions or self-joins to compare status dates; apply HAVING with MIN/MAX on filtered dates for purchase conditions.
Quick Answer: This question evaluates a candidate's ability to manipulate and analyze temporal and categorical datasets, covering competencies such as event sequencing, set-based filtering, and cross-table aggregation in SQL/Python.