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.
Overview: 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.
Sellers Blocked or Suspended After Reinstate
Using the SELLER_STATUS table, list seller_id values that experienced a 'block' or 'suspend' status at any time after a 'reinstate' status for that same seller.
Tables
SELLER_STATUS(seller_id INTEGER, date DATE, status VARCHAR(20))
Hints
- Self-join the SELLER_STATUS table by seller_id.
- Compare dates to ensure the 'block' or 'suspend' occurs after a 'reinstate'.
First Status After Most Recent Reinstate
Using the SELLER_STATUS table, for each seller return the first status and its date that occurs strictly after that seller's most recent 'reinstate' status. Exclude sellers who do not have any status after their most recent 'reinstate'.
Tables
SELLER_STATUS(seller_id INTEGER, date DATE, status VARCHAR(20))
Hints
- First find each seller's most recent 'reinstate' date using aggregation.
- Use ROW_NUMBER() partitioned by seller_id and ordered by date to pick the first status after that date.
Customers Buying A in 2010 and B in 2018
Using the PURCHASE_HISTORY table, return id values for customers who purchased product 'A' at least once during the year 2010 and product 'B' at least once during the year 2018.
Tables
PURCHASE_HISTORY(id INTEGER, order_date DATE, product VARCHAR(20))
Hints
- Use conditional aggregation in the HAVING clause to test for purchases of each product in the required year.
- Filter by year using explicit date ranges or an EXTRACT(YEAR FROM order_date) expression.