Capital One·Data Scientist·Online Assessment (OA)·Intermediate

Intermediate

Jun 2026

Summary

Capital One OA for a Data Scientist role, one SQL problem on transaction data. Nothing crazy, but the streak logic tripped me up more than I expected.

Questions Asked (1)

Given a transactions table with user IDs, amounts, dates, merchants, and statuses, find the longest streak of consecutive days with successful transactions for each user. Return user_id and longest_streak, ordered by streak length descending. Exclude failed or reversed transactions.

Algorithms & Data StructuresProduct Analytics & MetricsData Modeling

Author's notes

The filter part was obvious but the consecutive-days logic took me a minute.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, filter the transactions to include only successful ones, then deduplicate to get distinct user-date pairs. Use the 'gaps and islands' technique: for each user, assign a row number ordered by date and subtract it from the date to create a group identifier for consecutive days. Finally, count the size of each group and take the maximum per user, ordering by streak length descending.

Pro tip: Mention that you would clarify the definition of 'consecutive days' (e.g., calendar days vs. business days) and confirm that a streak can start on any day, not necessarily the first transaction. Also, note that if a user has no successful transactions, they should be excluded from the result.

1. Filter and deduplicate

Filter the transactions to include only successful ones (exclude failed/reversed). Then, extract distinct user_id and transaction_date pairs to ensure each day is counted once per user.

2. Identify consecutive groups

For each user, order the distinct dates and assign a row number. Subtract the row number from the date to create a group key; consecutive dates will have the same group key.

3. Calculate streak lengths

Group by user_id and the group key, then count the number of days in each group. This gives the length of each consecutive streak.

4. Find longest streak per user

For each user, take the maximum streak length from the previous step. This yields the longest consecutive days with successful transactions.

5. Order and return results

Return user_id and longest_streak, ordered by longest_streak descending. Optionally, handle ties by user_id or leave as is.

Key Points to Mention

Filtering out failed and reversed transactions before processing.
Deduplicating user-date pairs to avoid counting multiple transactions on the same day as separate days.
Using the 'gaps and islands' technique with row_number() and date subtraction to group consecutive days.
Handling edge cases: users with no successful transactions, single-day streaks, and ties in streak length.
Considering performance implications for large datasets (e.g., indexing, partitioning by user).
Clarifying business definitions: what constitutes a 'successful' transaction and whether weekends/holidays affect streaks.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.