Calculate Longest Transaction Streak for Each User

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Capital One.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Capital One during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Calculate Longest Transaction Streak for Each User