Find Second Most Recent Subscription | Dataford Interview Questions

Adobe wants to review the second most recent subscription event for each account in Adobe Experience Cloud. Write a SQL query to return that event per user.

Requirements

Use a window function to rank subscription events per user_id by event_timestamp in descending order.
Return exactly one row per user for the second most recent event.
Include users with tied timestamps correctly, using a deterministic tie-breaker if needed.
Exclude users who do not have at least two subscription events.

Schema

subscription_events

Column	Type	Description
event_idPK	BIGINT	Primary key for the event
user_id	BIGINT	Adobe account identifier
subscription_id	BIGINT	Subscription record identifier
event_type	VARCHAR(50)	Event type, such as start, renewal, or cancel
event_timestamp	TIMESTAMP	When the subscription event occurred
channel	VARCHAR(50)	Source channel, such as web, mobile, or partner

Your question is Find Second Most Recent Subscription. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Adobe wants to review the second most recent subscription event for each account in Adobe Experience Cloud. Write a SQL query to return that event per user.

Requirements

Use a window function to rank subscription events per user_id by event_timestamp in descending order.
Return exactly one row per user for the second most recent event.
Include users with tied timestamps correctly, using a deterministic tie-breaker if needed.
Exclude users who do not have at least two subscription events.

Schema

subscription_events

Column	Type	Description
event_idPK	BIGINT	Primary key for the event
user_id	BIGINT	Adobe account identifier
subscription_id	BIGINT	Subscription record identifier
event_type	VARCHAR(50)	Event type, such as start, renewal, or cancel
event_timestamp	TIMESTAMP	When the subscription event occurred
channel	VARCHAR(50)	Source channel, such as web, mobile, or partner

Your question is Find Second Most Recent Subscription. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.