Amazon·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Jun 2026

Summary

Amazon Data Scientist interview with a subscription analytics problem that mixed SQL and pandas in a single question. The setup was deceptively simple but they clearly wanted to see how you think about data quality before jumping to the actual aggregation.

Questions Asked (1)

Given a subscriptions table with subscription_id, status, and status_date columns, write a SQL query to investigate the structure of the data (e.g., check uniqueness of id+date combinations, validate which status transitions are actually present). Then in pandas, build a DataFrame that shows for each subscription_id the first date it was ACTIVE and the last date it was INACTIVE.

Product Analytics & MetricsData Modeling

Author's notes

The SQL part tripped me up more than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by writing SQL queries to explore the data: check for duplicate (subscription_id, status_date) pairs, examine the distribution of statuses, and identify all observed status transitions. Then, in pandas, filter for ACTIVE and INACTIVE rows, group by subscription_id, and compute the minimum date for ACTIVE and maximum date for INACTIVE, handling cases where a subscription may lack one of the statuses.

Pro tip: When investigating status transitions, consider the order of events per subscription and look for unexpected transitions (e.g., ACTIVE to ACTIVE without an INACTIVE in between) to uncover data quality issues. Also, clarify with the interviewer whether 'first date ACTIVE' and 'last date INACTIVE' should be based on the entire history or only after the most recent status change.

1. Explore data structure with SQL

Write queries to check uniqueness of (subscription_id, status_date), count distinct statuses, and list all distinct status transitions by ordering events per subscription.

2. Validate status transitions

Identify which transitions are present (e.g., ACTIVE->INACTIVE, INACTIVE->ACTIVE) and flag any anomalies like missing intermediate states or duplicate dates.

3. Load and preprocess in pandas

Read the data into a DataFrame, convert status_date to datetime, and sort by subscription_id and status_date to ensure chronological order.

4. Compute first ACTIVE and last INACTIVE dates

Group by subscription_id and aggregate: for ACTIVE rows, take the minimum status_date; for INACTIVE rows, take the maximum status_date. Merge these results into a single DataFrame.

5. Handle missing statuses and finalize output

If a subscription has no ACTIVE or no INACTIVE records, decide whether to include with NaN or exclude, and ensure the final DataFrame has columns subscription_id, first_active_date, last_inactive_date.

Key Points to Mention

Uniqueness check on (subscription_id, status_date) to detect duplicates or multiple statuses on the same day.
Status transition analysis: use window functions like LAG to compare consecutive statuses per subscription.
Data quality issues: missing dates, out-of-order events, or illogical transitions (e.g., ACTIVE to ACTIVE).
Pandas groupby with min/max on filtered subsets, and merging results.
Handling subscriptions with only ACTIVE or only INACTIVE records (e.g., using outer join or fillna).
Time zone or date format considerations when converting status_date.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.