P55SQLIntermediateDesign14 min
Design a Window-Function Query for Latest Subscription Status
Return each user's latest subscription state while retaining plan history.
#design#window-functions#subscriptions
Scenario
subscription_events has user_id, event_at, plan, and status. Users can upgrade, pause, and cancel over time.
Problem statement
Design a query that returns the latest row per user.
Your Task
- Rank events within user.
- Select only rank one for current status.
- Explain how history remains available.
Related Concepts
ROW_NUMBEREvent historyLatest record
Ready to count this problem?
Mark it complete when you have reasoned through the solution in your own words.