Users With Two Purchases Within Seven Days

Users With Two Purchases Within Seven Days - Problem

Database Medium

Imagine you're working as a data analyst for a major e-commerce platform, and the marketing team wants to identify frequent shoppers to target with special loyalty programs. Your task is to find users who show consistent purchasing behavior by making at least two purchases within a 7-day window.

Given a Purchases table with purchase records, you need to identify all users who made any two purchases at most 7 days apart. This could help the business understand customer buying patterns and reward loyal customers.

Table: Purchases

+---------------+------+
| Column Name   | Type |
+---------------+------+
| purchase_id   | int  |
| user_id       | int  |
| purchase_date | date |
+---------------+------+

Where purchase_id contains unique values, and each row represents a purchase made by a user on a specific date.

Goal: Return the user_ids of all users who made any two purchases with at most 7 days between them, ordered by user_id.

Input & Output

example_1.sql — Basic Case

$ Input: Purchases table:\n| purchase_id | user_id | purchase_date |\n|-------------|---------|---------------|\n| 4 | 2 | 2022-03-13 |\n| 1 | 1 | 2022-02-11 |\n| 2 | 1 | 2022-02-17 |\n| 6 | 3 | 2022-03-13 |\n| 5 | 3 | 2022-03-20 |\n| 3 | 1 | 2022-02-24 |

› Output: | user_id |\n|---------|\n| 1 |\n| 3 |

💡 Note: User 1 has purchases on 2022-02-11 and 2022-02-17 (6 days apart ≤ 7). User 3 has purchases on 2022-03-13 and 2022-03-20 (7 days apart ≤ 7). User 2 has only one purchase, so doesn't qualify.

example_2.sql — Edge Case: Exactly 7 Days

$ Input: Purchases table:\n| purchase_id | user_id | purchase_date |\n|-------------|---------|---------------|\n| 1 | 1 | 2023-01-01 |\n| 2 | 1 | 2023-01-08 |\n| 3 | 2 | 2023-01-01 |\n| 4 | 2 | 2023-01-09 |

› Output: | user_id |\n|---------|\n| 1 |

💡 Note: User 1 has purchases exactly 7 days apart (2023-01-01 to 2023-01-08), which qualifies. User 2 has purchases 8 days apart, which exceeds the 7-day limit.

example_3.sql — Multiple Valid Pairs

$ Input: Purchases table:\n| purchase_id | user_id | purchase_date |\n|-------------|---------|---------------|\n| 1 | 1 | 2023-01-01 |\n| 2 | 1 | 2023-01-03 |\n| 3 | 1 | 2023-01-05 |\n| 4 | 1 | 2023-01-20 |

› Output: | user_id |\n|---------|\n| 1 |

💡 Note: User 1 appears only once in the result despite having multiple valid pairs: (Jan 1, Jan 3) = 2 days, (Jan 3, Jan 5) = 2 days. We return distinct user_ids.

Constraints

1 ≤ Number of purchases ≤ 10⁵
1 ≤ user_id ≤ 10⁴
purchase_date is a valid date between 2020-01-01 and 2024-12-31
All purchase_id values are unique
A user can have multiple purchases on the same date

Visualization

Tap to expand

Understanding the Visualization

Organize Timeline

Sort all purchases by user and date to create chronological timelines

Look Back

For each purchase, use LAG to see the previous purchase date for that user

Measure Gap

Calculate the number of days between consecutive purchases

Filter Loyalists

Keep users where any consecutive purchases are ≤7 days apart

Key Takeaway

🎯 Key Insight: Instead of comparing every purchase with every other purchase (O(n²)), we sort chronologically and only check consecutive purchases using LAG window function, achieving O(n log n) efficiency.

Asked in

a Amazon 45 f Meta 38 G Google 32 ⊞ Microsoft 28

The optimal approach uses a window function with LAG to compare each purchase with the previous one for the same user. This eliminates the need for expensive self-joins and reduces complexity from O(n²) to O(n log n). The key insight is that we only need to check consecutive purchases chronologically - if any two consecutive purchases are within 7 days, the user qualifies.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Self Join with All Combinations)	O(n²)	O(n)	Compare every purchase with every other purchase for the same user
Window Function (Optimal)	O(n log n)	O(n)	Use LAG window function to compare each purchase with the previous one

Brute Force (Self Join with All Combinations) — Algorithm Steps

Self-join the Purchases table on user_id
Ensure we don't compare a purchase with itself
Calculate the absolute difference in days between purchase dates
Filter pairs where the difference is ≤ 7 days
Select distinct user_ids and order by user_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Self Join Setup

Join Purchases table with itself on user_id

Compare All Pairs

For each user, compare every purchase with every other purchase

Date Difference Check

Calculate days between purchases and filter ≤7 days

Collect Results

Gather distinct user_ids that meet criteria

Code -

solution.c — C

// SQL Solution
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
    "SELECT DISTINCT p1.user_id\n"
    "FROM Purchases p1\n"
    "JOIN Purchases p2 ON p1.user_id = p2.user_id\n"
    "WHERE p1.purchase_id != p2.purchase_id\n"
    "  AND ABS(DATEDIFF(p1.purchase_date, p2.purchase_date)) <= 7\n"
    "ORDER BY p1.user_id;";
    return query;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of the n purchases, we potentially compare with all other purchases of the same user

⚠ Quadratic Growth

Space Complexity

O(n)

Space for storing the join result and distinct user_ids

⚡ Linearithmic Space

28.4K Views

High Frequency

~15 min Avg. Time

1.2K 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 (Self Join with All Combinations) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler