Quick Overview

This question evaluates proficiency in time-series and event-sequence data manipulation, temporal aggregation, and data quality validation using SQL and pandas, focusing on identifying status transitions and date-based uniqueness.

Explore Subscription Patterns and Status Transitions with SQL/Pandas

Company: Amazon

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

subscriptions +-----------------+---------+-------------+ | subscription_id | status | status_date | +-----------------+---------+-------------+ | 101 | active | 2023-01-05 | | 101 | inactive| 2023-03-10 | | 102 | inactive| 2023-02-12 | | 102 | active | 2023-04-01 | +-----------------+---------+-------------+ ##### Scenario Subscription analytics – product team wants to understand when customers become active or churn. ##### Question Write an SQL query that explores column values and row patterns to confirm or deny assumptions about the structure of SUBSCRIPTIONS (e.g., uniqueness of subscription_id+status_date, allowed status transitions). In Python (pandas), build a DataFrame that returns, for every subscription_id, the first date it was ACTIVE and the last date it was INACTIVE. ##### Hints Think window functions for SQL; in pandas use groupby with idxmin / idxmax or boolean masks.

Overview: This question evaluates proficiency in time-series and event-sequence data manipulation, temporal aggregation, and data quality validation using SQL and pandas, focusing on identifying status transitions and date-based uniqueness.

Check pair uniqueness

Using the subscriptions table, identify any duplicate (subscription_id, status_date) pairs and return their counts. Only include pairs occurring more than once.

Tables

subscriptions(subscription_id INTEGER, status VARCHAR(10), status_date DATE)

Hints

  1. Group by subscription_id and status_date
  2. Use HAVING COUNT(*) > 1 to filter duplicates

Status transition frequencies

Determine the frequencies of observed transitions between consecutive statuses per subscription (ordered by status_date). Return from_status, to_status, and transition_count aggregated across all subscriptions.

Tables

subscriptions(subscription_id INTEGER, status VARCHAR(10), status_date DATE)

Hints

  1. Use LAG over PARTITION BY subscription_id ORDER BY status_date
  2. Filter out the first row per subscription (prev_status IS NOT NULL)

First active, last inactive

For each subscription_id, return the earliest date it was 'active' and the latest date it was 'inactive'. If a subscription never had a given status, return NULL for that date.

Tables

subscriptions(subscription_id INTEGER, status VARCHAR(10), status_date DATE)

Hints

  1. Use conditional aggregation with CASE inside MIN/MAX
  2. LOWER(status) can make the comparison case-insensitive

Loading coding console...