Verify subscriptions and analyze orders with SQL/Python

You are given two tables. Write SQL and Python (pandas) to answer the sub-questions precisely, handling edge cases, ties, and missing data.

Schema

• subscriptions(subscription_id INT, status VARCHAR(20), status_date DATE)
• orders(order_id INT, cust_id INT, order_date DATE, product VARCHAR(30), order_amount DECIMAL(10,2), shipping_option_id INT)

Sample data (small, intentionally messy) subscriptions

orders

Tasks A) SQL data-quality checks on subscriptions

• Write one or more queries that would rigorously confirm or refute all of the following assumptions about the subscriptions table, returning concrete violating rows if any exist:

1. Allowed statuses are only {'active','inactive'}; surface any unexpected statuses (e.g., 'paused').
2. (subscription_id, status_date) is unique; list duplicates if present.
3. For each subscription_id, status_date values are strictly increasing over time; surface any non-monotonic back-dated rows.
4. For each subscription_id and calendar date, there is at most one status; surface overlapping same-day multi-status cases.

• Additionally, as of reference_date = '2025-09-01', return for each subscription_id: its latest known status and the timestamp of that status.

B) Pandas on subscriptions

• Create a pandas DataFrame with columns [subscription_id, first_active_date, last_inactive_date], where: • first_active_date is the earliest status_date with status = 'active'. • last_inactive_date is the most recent status_date with status = 'inactive' on or before '2025-09-01'.
• Requirements: handle ties/duplicates deterministically (pick MIN for first_active_date, MAX for last_inactive_date), ignore unexpected statuses, and allow nulls when a subscription never became active or inactive.

C) Python (pandas) on orders

1. Return the list of cust_id who either (a) placed fewer than 2 total orders, or (b) have total order_amount across all time < 100.00.
2. For each calendar month (YYYY-MM based on order_date), compute two leaderboards: • Top 5 customers by order count. • Top 5 customers by total order_amount. Use tie-breakers: higher total_amount first, then lower cust_id; if fewer than 5 exist, return all available.
3. Compute each customer's total spend per product type and present a wide table with columns exactly: cust_id | camera | shoes | laptop | clothes. Missing combinations should be filled with 0.00.