Explore Subscription Patterns and Status Transitions with SQL/Pandas

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.

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.

Table subscriptions

column	type	constraints
subscription_id	INTEGER
status	VARCHAR(10)
status_date	DATE

Sample data:

status	status_date	subscription_id
active	2023-01-05	101
inactive	2023-03-10	101
inactive	2023-02-12	102
active	2023-04-01	102

Expected output

Return any (subscription_id, status_date) pairs that appear more than once, with their occurrence count. If none, the result is empty.

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.

Table subscriptions

column	type	constraints
subscription_id	INTEGER
status	VARCHAR(10)
status_date	DATE

Sample data:

status	status_date	subscription_id
active	2023-01-05	101
inactive	2023-03-10	101
inactive	2023-02-12	102
active	2023-04-01	102

Expected output

For each observed transition between consecutive rows per subscription (ordered by status_date), count how many times that transition occurs across all subscriptions.

from_status	to_status	transition_count
active	inactive	1
inactive	active	1

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)
3. Group by prev_status and current status

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.

Table subscriptions

column	type	constraints
subscription_id	INTEGER
status	VARCHAR(10)
status_date	DATE

Sample data:

status	status_date	subscription_id
active	2023-01-05	101
inactive	2023-03-10	101
inactive	2023-02-12	102
active	2023-04-01	102

Expected output

For each subscription_id, return first_active_date (MIN date where status = 'active') and last_inactive_date (MAX date where status = 'inactive').

first_active_date	last_inactive_date	subscription_id
2023-01-05	2023-03-10	101
2023-04-01	2023-02-12	102

Hints

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