Calculate Longest Transaction Streak for Each User
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
You are given a `transactions` table that logs every transaction attempt. Each row has a `transaction_id`, the `user_id` who made it, the `amount`, the `transaction_date` (a calendar date), the `merchant`, and a `status` (e.g. `'success'`, `'failed'`).
For each user, find the **longest streak of consecutive calendar days** on which the user had **at least one successful transaction** (`status = 'success'`). A streak is a maximal run of back-to-back calendar dates (e.g. May 1, May 2, May 3 is a streak of length 3); any gap of one or more days breaks the streak.
Rules:
- Consider only rows with `status = 'success'`. Ignore `failed` (or any non-success) rows entirely.
- Multiple successful transactions on the **same day** count as a **single day** in the streak.
- A user with at least one successful transaction always has a streak of at least 1.
Return one row per user with two columns: `user_id` and `longest_streak` (the number of days in that user's longest consecutive-day streak). Order the result by `longest_streak` in **descending** order, breaking ties by `user_id` ascending.
Tables
transactions(transaction_id INTEGER, user_id INTEGER, amount DECIMAL(10,2), transaction_date DATE, merchant VARCHAR(100), status VARCHAR(20))
Hints
- Pre-aggregate to DISTINCT (user_id, transaction_date) for successful rows only, so multiple transactions on the same day don't inflate the streak length.
- In PostgreSQL, subtracting one DATE from another (a - b) gives an integer day count — use it with LAG to detect whether consecutive days are exactly 1 apart.