Katkat
Back

Calculate Longest Transaction Streak for Each User

Capital OneData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in data manipulation and time-series sequence analysis, specifically measuring the longest run of consecutive successful transactions per user from transactional logs.

transactions

+---------------+---------+--------+----------------+-----------+----------+ | transaction_id| user_id | amount | transaction_date| merchant | status | +---------------+---------+--------+----------------+-----------+----------+ | 1001 | 11 | 23.50 | 2023-05-01 | Amazon | success | | 1002 | 11 | 9.99 | 2023-05-02 | Spotify | success | | 1003 | 11 | 42.10 | 2023-05-04 | Walmart | success | | 2001 | 20 | 19.99 | 2023-05-02 | Netflix | failed | | 2002 | 20 | 15.75 | 2023-05-03 | Target | success | +---------------+---------+--------+----------------+-----------+----------+

Scenario

OA round: SQL challenge on customer transactions data; task is to derive meaningful user-level insights.

Question

For each user, calculate the longest streak (in days) of consecutive successful transactions and output user_id with longest_streak ordered desc; ignore failed or reversed transactions.

Hints

Gap-and-islands or window functions (LAG, DATE_DIFF) can identify consecutive-day groups.