Customers with Maximum Number of Transactions on Consecutive Days

Customers with Maximum Number of Transactions on Consecutive Days - Problem

Database Hard

You're analyzing customer transaction patterns to identify the most active customers. Given a Transactions table with customer transactions, your goal is to find customers who achieved the maximum number of consecutive transaction days.

Problem: Find all customers who made transactions for the highest number of consecutive days. For example, if one customer made transactions for 5 consecutive days and another for 3 consecutive days, you want the customer with 5 consecutive days (assuming that's the maximum).

Key Points:

Each row represents a unique (customer_id, transaction_date) pair
Consecutive days means no gaps between transaction dates
Return ALL customers tied for the maximum consecutive transaction days
Results should be ordered by customer_id ascending

This problem tests your ability to work with date sequences, window functions, and finding maximum consecutive sequences in SQL.

Input & Output

example_1.sql — Basic Case

$ Input: Transactions table: | transaction_id | customer_id | transaction_date | amount | |----------------|-------------|------------------|--------| | 1 | 1 | 2024-01-01 | 100 | | 2 | 1 | 2024-01-02 | 150 | | 3 | 1 | 2024-01-03 | 200 | | 4 | 2 | 2024-01-01 | 50 | | 5 | 2 | 2024-01-03 | 75 | | 6 | 3 | 2024-01-01 | 300 | | 7 | 3 | 2024-01-02 | 250 |

› Output: | customer_id | |-------------| | 1 |

💡 Note: Customer 1 made transactions on 3 consecutive days (2024-01-01, 2024-01-02, 2024-01-03). Customer 2 made transactions on 2 non-consecutive days with a gap. Customer 3 made transactions on 2 consecutive days. The maximum consecutive days is 3, achieved only by Customer 1.

example_2.sql — Multiple Winners

$ Input: Transactions table: | transaction_id | customer_id | transaction_date | amount | |----------------|-------------|------------------|--------| | 1 | 1 | 2024-01-01 | 100 | | 2 | 1 | 2024-01-02 | 150 | | 3 | 2 | 2024-01-05 | 200 | | 4 | 2 | 2024-01-06 | 250 | | 5 | 3 | 2024-01-10 | 300 |

› Output: | customer_id | |-------------| | 1 | | 2 |

💡 Note: Both Customer 1 and Customer 2 have 2 consecutive transaction days each, which is the maximum. Customer 3 only has 1 day. Therefore, both customers 1 and 2 are returned, sorted by customer_id.

example_3.sql — Single Transaction Days

› Output: | customer_id | |-------------| | 1 | | 2 | | 3 |

💡 Note: All customers have exactly 1 transaction day each, so the maximum consecutive days is 1. All customers are tied for the maximum, so all are returned in ascending order of customer_id.

Constraints

1 ≤ number of transactions ≤ 10⁵
1 ≤ customer_id ≤ 10⁴
transaction_date is in format 'YYYY-MM-DD'
1 ≤ amount ≤ 10⁶
Each (customer_id, transaction_date) pair is unique
At least one customer will have at least one transaction

Visualization

Tap to expand

Understanding the Visualization

Data Preparation

Extract unique customer-date pairs and sort them chronologically by customer

Window Function Magic

Apply ROW_NUMBER() within each customer group to create sequential numbering

Date Grouping

Calculate date_group = date - (row_number - 1) to group consecutive sequences

Count Sequences

Group by customer and date_group to count consecutive days in each sequence

Find Winners

Identify customers with the maximum consecutive transaction days

Key Takeaway

🎯 Key Insight: The ROW_NUMBER() window function technique transforms a complex consecutive sequence problem into simple grouping. Consecutive dates have the same `date - row_number` value, making detection O(n log n) instead of O(n³)!

Asked in

G Google 42 a Amazon 38 f Meta 29 ⊞ Microsoft 25

The optimal solution uses SQL window functions with the ROW_NUMBER() technique. The key insight is that consecutive dates will have a constant difference between the date and its row number, creating natural groups. By calculating date_group = transaction_date - (ROW_NUMBER() - 1), consecutive dates get the same group identifier. This allows efficient grouping and counting in O(n log n) time complexity, making it much faster than brute force approaches.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Nested Subqueries)	O(n³)	O(n)	Check each customer's transaction dates manually to find consecutive sequences
Window Functions with Row Numbers (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() window function to identify consecutive date groups efficiently

Brute Force (Nested Subqueries) — Algorithm Steps

Get all unique customer-date combinations
For each customer-date, count consecutive days starting from that date
Find the maximum consecutive count per customer
Find the overall maximum consecutive count
Return customers with the maximum count

Visualization

Tap to expand

Step-by-Step Walkthrough

Group by Customer

Collect all transaction dates for each customer

Sort Dates

Sort transaction dates chronologically for each customer

Check Consecutive

Manually check each date pair to see if they're consecutive

Track Maximum

Keep track of the longest consecutive sequence per customer

Find Global Max

Identify customers with the overall maximum consecutive days

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_CUSTOMERS 1000
#define MAX_DATES 365

typedef struct {
    int customer_id;
    char transaction_date[11];
} Transaction;

typedef struct {
    int customer_id;
    char dates[MAX_DATES][11];
    int date_count;
} CustomerDates;

int compareDates(const void *a, const void *b) {
    return strcmp((char*)a, (char*)b);
}

int compareCustomers(const void *a, const void *b) {
    return *(int*)a - *(int*)b;
}

int isConsecutive(const char *date1, const char *date2) {
    // Simplified consecutive check - implement proper date arithmetic
    return 1;
}

int* solution(Transaction* transactions, int n, int* resultSize) {
    CustomerDates customers[MAX_CUSTOMERS];
    int customerCount = 0;
    
    // Group dates by customer (brute force approach)
    for (int i = 0; i < n; i++) {
        int found = 0;
        for (int j = 0; j < customerCount; j++) {
            if (customers[j].customer_id == transactions[i].customer_id) {
                // Check if date already exists
                int dateExists = 0;
                for (int k = 0; k < customers[j].date_count; k++) {
                    if (strcmp(customers[j].dates[k], transactions[i].transaction_date) == 0) {
                        dateExists = 1;
                        break;
                    }
                }
                if (!dateExists) {
                    strcpy(customers[j].dates[customers[j].date_count], transactions[i].transaction_date);
                    customers[j].date_count++;
                }
                found = 1;
                break;
            }
        }
        if (!found) {
            customers[customerCount].customer_id = transactions[i].customer_id;
            strcpy(customers[customerCount].dates[0], transactions[i].transaction_date);
            customers[customerCount].date_count = 1;
            customerCount++;
        }
    }
    
    int maxConsecutive = 0;
    int customerMaxStreaks[MAX_CUSTOMERS];
    
    // Find max consecutive days for each customer
    for (int i = 0; i < customerCount; i++) {
        qsort(customers[i].dates, customers[i].date_count, sizeof(customers[i].dates[0]), compareDates);
        
        int maxStreak = 1;
        int currentStreak = 1;
        
        for (int j = 1; j < customers[i].date_count; j++) {
            if (isConsecutive(customers[i].dates[j-1], customers[i].dates[j])) {
                currentStreak++;
            } else {
                currentStreak = 1;
            }
            if (currentStreak > maxStreak) {
                maxStreak = currentStreak;
            }
        }
        
        customerMaxStreaks[i] = maxStreak;
        if (maxStreak > maxConsecutive) {
            maxConsecutive = maxStreak;
        }
    }
    
    // Find customers with maximum consecutive days
    int* result = malloc(customerCount * sizeof(int));
    *resultSize = 0;
    
    for (int i = 0; i < customerCount; i++) {
        if (customerMaxStreaks[i] == maxConsecutive) {
            result[*resultSize] = customers[i].customer_id;
            (*resultSize)++;
        }
    }
    
    qsort(result, *resultSize, sizeof(int), compareCustomers);
    return result;
}

int main() {
    // Parse input and call solution
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n³)

For each customer-date pair, we potentially scan all other dates for that customer, leading to cubic time complexity

⚠ Quadratic Growth

Space Complexity

O(n)

Space needed for intermediate results and temporary tables

⚡ Linearithmic Space

52.6K Views

High Frequency

~25 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Nested Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler