Verify subscriptions and analyze orders with SQL/Python
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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
subscription_id | status | status_date
--------------: | -------- | -----------
1001 | inactive | 2025-01-10
1001 | active | 2025-02-01
1001 | paused | 2025-03-15
1001 | inactive | 2025-04-01
1002 | active | 2025-01-05
1002 | active | 2025-01-05
1003 | active | 2025-02-10
1003 | inactive | 2025-02-08
orders
order_id | cust_id | order_date | product | order_amount | shipping_option_id
-------: | ------: | ---------- | ------- | -----------: | -----------------:
1 | 10 | 2025-01-05 | camera | 80.00 | 1
2 | 10 | 2025-01-20 | shoes | 30.00 | 2
3 | 11 | 2025-01-25 | laptop | 1200.00 | 1
4 | 12 | 2025-02-02 | clothes | 40.00 | 3
5 | 12 | 2025-02-10 | camera | 70.00 | 1
6 | 13 | 2025-02-15 | shoes | 20.00 | 2
7 | 13 | 2025-02-16 | shoes | 25.00 | 2
8 | 14 | 2025-03-01 | laptop | 999.00 | 1
9 | 10 | 2025-03-05 | clothes | 15.00 | 3
10 | 15 | 2025-03-08 | camera | 50.00 | 1
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.
Overview: This question evaluates proficiency in SQL and pandas data manipulation, covering data-quality validation, temporal sequence reasoning, deduplication, deterministic aggregation, and tie-breaking logic.
Subscriptions DQ: Unexpected statuses
You are given a subscriptions table with potentially messy data.
Return all rows whose status is NOT one of the allowed statuses: {'active','inactive'}. Output the violating rows (subscription_id, status, status_date).
Tables
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)
Hints
- Use a NOT IN filter on status.
- Return the raw violating rows, not just counts.
Subscriptions DQ: Duplicate (subscription_id, status_date)
Check whether (subscription_id, status_date) is unique.
Return all rows that belong to a duplicated (subscription_id, status_date) combination, along with the duplicate_count for that combination.
Tables
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)
Hints
- Use GROUP BY ... HAVING COUNT(*) > 1.
- Join back to subscriptions to return the concrete offending rows.
Subscriptions DQ: Non-increasing status_date per subscription
For each subscription_id, the status_date values should be strictly increasing when ordered by status_date (ties are not allowed).
Return any rows that violate strict increase. For each violating row, output subscription_id, status, status_date, and the previous_status_date based on ordering by (status_date, status). A row violates the rule if status_date <= previous_status_date.
Tables
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)
Hints
- Use LAG() partitioned by subscription_id.
- Strictly increasing means current date must be > previous date (no ties).
Subscriptions DQ: Same-day multiple statuses
For each subscription_id and calendar date (status_date), there should be at most one distinct status.
Return (subscription_id, status_date) where there are 2+ distinct statuses on the same date. Also return distinct_status_count and a comma-separated list of distinct statuses in alphabetical order.
Tables
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)
Hints
- COUNT(DISTINCT status) identifies multi-status days.
- STRING_AGG/LISTAGG can be used to display which statuses occurred.
Latest known subscription status as of a reference date
Using reference_date = DATE '2025-09-01', return one row per subscription_id with its latest known status on or before the reference_date, plus the status_date of that status.
If multiple rows tie on the same latest status_date for a subscription, break ties deterministically using this priority: active first, then inactive, then any other status (alphabetical within the same priority).
Tables
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)
Hints
- Filter to rows on/before the reference date, then use ROW_NUMBER().
- Implement a deterministic tie-breaker for same-day rows.
Subscription summary dates: first active and last inactive (as of reference date)
Using reference_date = DATE '2025-09-01', return a table with columns:
- subscription_id
- first_active_date: earliest status_date where status = 'active'
- last_inactive_date: most recent status_date where status = 'inactive' AND status_date <= reference_date
Ignore unexpected statuses (anything other than active/inactive). If a subscription never became active or never became inactive (on/before the reference date), return NULL for the corresponding date.
Tables
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)
Hints
- Conditional aggregation (MIN/MAX with CASE) naturally handles duplicates/ties.
- Return NULL if no qualifying rows exist for that condition.
Orders: Customers with low order count or low lifetime spend
From the orders table, return the list of cust_id who either:
(a) placed fewer than 2 total orders (across all time), OR
(b) have total lifetime order_amount < 100.00.
Return distinct cust_id values sorted ascending.
Tables
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)
Hints
- Aggregate by cust_id with COUNT and SUM.
- Use HAVING to filter on aggregated values.
Orders: Monthly top-5 leaderboards by order count and by spend
For each calendar month (format YYYY-MM based on order_date), compute two leaderboards:
1) Top 5 customers by order count.
2) Top 5 customers by total order_amount.
Tie-breaking:
- For the "by order count" leaderboard: higher order_count first, then higher total_amount, then lower cust_id.
- For the "by total amount" leaderboard: higher total_amount first, then lower cust_id.
Return a single result set with columns: order_month, leaderboard_type, rank_in_month, cust_id, order_count, total_amount.
If fewer than 5 customers exist in a month, return all available.
Tables
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)
Hints
- Aggregate to month+customer first, then rank with window functions.
- Use ROW_NUMBER for deterministic tie-breaking.
Orders: Pivot total spend per customer by product
Compute each customer's total spend per product type and return a wide table with columns exactly:
cust_id | camera | shoes | laptop | clothes
Missing combinations must be filled with 0.00. Return one row per cust_id sorted ascending.
Tables
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)
Hints
- Use conditional aggregation to create fixed columns per product.
- COALESCE the SUM results to 0.00 for missing products.