Katkat
Back

Analyze Seller Compliance and Customer Purchase Patterns

AmazonBusiness Intelligence Engineer · Onsite
MediumBusiness Intelligence EngineerOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.