Calculate Longest Transaction Streak for Each User

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.

Longest streak of consecutive successful transaction days per user

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.

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
transaction_date	DATE	NOT NULL
merchant	VARCHAR(100)	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

amount	merchant	status	transaction_date	transaction_id	user_id
23.5	Amazon	success	2023-05-01	1001	11
9.99	Spotify	success	2023-05-02	1002	11
7	Uber	success	2023-05-02	1003	11
42.1	Walmart	success	2023-05-04	1004	11
5	Lyft	failed	2023-05-05	1005	11
19.99	Netflix	failed	2023-05-02	2001	20
15.75	Target	success	2023-05-03	2002	20
30	Costco	success	2023-05-10	2003	30
12	BestBuy	success	2023-05-11	2004	30
8	Apple	success	2023-05-12	2005	30

Expected output

One row per user, with the length (in days) of that user's longest run of consecutive calendar days having a successful transaction, ordered by longest_streak descending then user_id ascending.

longest_streak	user_id
3	30
2	11
1	20

Hints

1. 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.
2. 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.
3. Use the gap-and-islands trick: flag each streak start, take a running SUM of that flag to assign a group id, then COUNT days per group and take MAX per user.