cswork / libraryOpen practice workspace

Coding questions · Amazon

Verify subscriptions and analyze orders with SQL/Python

This question evaluates proficiency in SQL and pandas data manipulation, covering data-quality validation, temporal sequence reasoning, deduplication, deterministic aggregation, and tie-breaking logic.

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_idstatusstatus_date
1001inactive2025-01-10
1001active2025-02-01
1001paused2025-03-15
1001inactive2025-04-01
1002active2025-01-05
1002active2025-01-05
1003active2025-02-10
1003inactive2025-02-08

orders

order_idcust_idorder_dateproductorder_amountshipping_option_id
1102025-01-05camera80.001
2102025-01-20shoes30.002
3112025-01-25laptop1200.001
4122025-02-02clothes40.003
5122025-02-10camera70.001
6132025-02-15shoes20.002
7132025-02-16shoes25.002
8142025-03-01laptop999.001
9102025-03-05clothes15.003
10152025-03-08camera50.001

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.

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).

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

Violating subscription rows with unexpected statuses.

statusstatus_datesubscription_id
paused2025-03-151001

Hints

  1. Use a NOT IN filter on status.
  2. 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.

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

All subscription rows that are part of a duplicated (subscription_id, status_date) pair, plus the count of rows in that duplicated group.

duplicate_countstatusstatus_datesubscription_id
2active2025-01-051002
2active2025-01-051002
2active2025-02-101003
2inactive2025-02-101003

Hints

  1. Use GROUP BY ... HAVING COUNT(*) > 1.
  2. 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.

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

Rows where status_date is not strictly increasing within a subscription when ordered by (status_date, status).

previous_status_datestatusstatus_datesubscription_id
2025-01-05active2025-01-051002
2025-02-10inactive2025-02-101003

Hints

  1. Use LAG() partitioned by subscription_id.
  2. 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.

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

Subscription/date pairs that have multiple distinct statuses recorded the same day.

distinct_status_countstatus_datestatusessubscription_id
22025-02-10active,inactive1003

Hints

  1. COUNT(DISTINCT status) identifies multi-status days.
  2. 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).

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

One row per subscription_id showing its latest status on or before 2025-09-01 and the status_date of that latest status.

latest_statuslatest_status_datesubscription_id
inactive2025-04-011001
active2025-01-051002
active2025-02-101003

Hints

  1. Filter to rows on/before the reference date, then use ROW_NUMBER().
  2. 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.

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

One row per subscription_id with first_active_date and last_inactive_date (as of 2025-09-01).

first_active_datelast_inactive_datesubscription_id
2025-02-012025-04-011001
2025-01-05NULL1002
2025-02-102025-02-101003

Hints

  1. Conditional aggregation (MIN/MAX with CASE) naturally handles duplicates/ties.
  2. 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.

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

List of customers with <2 orders OR lifetime spend < 100.00.

cust_id
11
13
14
15

Hints

  1. Aggregate by cust_id with COUNT and SUM.
  2. 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.

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

Two monthly leaderboards (by order_count and by total_amount) with deterministic ranking and up to top 5 per month.

cust_idleaderboard_typeorder_countorder_monthrank_in_monthtotal_amount
10by_order_count22025-011110
11by_order_count12025-0121200
11by_total_amount12025-0111200
10by_total_amount22025-012110
12by_order_count22025-021110
13by_order_count22025-02245
12by_total_amount22025-021110
13by_total_amount22025-02245
14by_order_count12025-031999
15by_order_count12025-03250
10by_order_count12025-03315
14by_total_amount12025-031999
15by_total_amount12025-03250
10by_total_amount12025-03315

Hints

  1. Aggregate to month+customer first, then rank with window functions.
  2. 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.

Table subscriptions

columntypeconstraints
subscription_idINTNOT NULL
statusVARCHAR(20)NOT NULL
status_dateDATENOT NULL

Sample data:

statusstatus_datesubscription_id
inactive2025-01-101001
active2025-02-011001
paused2025-03-151001
inactive2025-04-011001
active2025-01-051002
active2025-01-051002
active2025-02-101003
inactive2025-02-081003
inactive2025-02-101003

Table orders

columntypeconstraints
order_idINTPRIMARY KEY, NOT NULL
cust_idINTNOT NULL
order_dateDATENOT NULL
productVARCHAR(30)NOT NULL
order_amountDECIMAL(10,2)NOT NULL
shipping_option_idINTNOT NULL

Sample data:

cust_idorder_amountorder_dateorder_idproductshipping_option_id
10802025-01-051camera1
10302025-01-202shoes2
1112002025-01-253laptop1
12402025-02-024clothes3
12702025-02-105camera1
13202025-02-156shoes2
13252025-02-167shoes2
149992025-03-018laptop1
10152025-03-059clothes3
15502025-03-0810camera1

Expected output

One row per customer with total spend split into fixed product columns; missing product totals shown as 0.00.

cameraclothescust_idlaptopshoes
801510030
001112000
70401200
0013045
00149990
5001500

Hints

  1. Use conditional aggregation to create fixed columns per product.
  2. COALESCE the SUM results to 0.00 for missing products.