Analyze Seller Compliance and Customer Purchase Patterns

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.

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.

Table SELLER_STATUS

column	type	constraints
seller_id	INTEGER	NOT NULL
date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

date	seller_id	status
2019-01-01	1	reinstate
2020-06-01	1	block
2020-01-01	2	reinstate
2021-03-01	2	suspend

Expected output

All seller_id values that have at least one 'block' or 'suspend' record dated after any of their 'reinstate' records.

seller_id
1
2

Hints

1. Self-join the SELLER_STATUS table by seller_id.
2. 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'.

Table SELLER_STATUS

column	type	constraints
seller_id	INTEGER	NOT NULL
date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

date	seller_id	status
2019-01-01	1	reinstate
2020-06-01	1	block
2020-01-01	2	reinstate
2021-03-01	2	suspend

Expected output

For each seller, the earliest status (with its date) that occurs after their most recent 'reinstate' status.

first_status_after_reinstate	seller_id	status_date
block	1	2020-06-01
suspend	2	2021-03-01

Hints

1. First find each seller's most recent 'reinstate' date using aggregation.
2. 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.

Table PURCHASE_HISTORY

column	type	constraints
id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
product	VARCHAR(20)	NOT NULL

Sample data:

id	order_date	product
10	2010-05-02	A
10	2018-07-19	B
11	2010-03-11	A

Expected output

Customer ids that bought product 'A' at least once in 2010 and product 'B' at least once in 2018.

id
10

Hints

1. Use conditional aggregation in the HAVING clause to test for purchases of each product in the required year.
2. Filter by year using explicit date ranges or an EXTRACT(YEAR FROM order_date) expression.