Practice advanced SQL question: Shortest Inter-Purchase Gap per Customer

Solve this interactive advanced question online in our browser coding environment.

Shortest Inter-Purchase Gap per Customer

advanced~15 min40 pts

Problem

For each customer, compute their shortest gap (in days) between any two consecutive purchases. This surfaces the most engaged buyers — repeat orders close together.

Schema: Customer

Column	Type
CustomerId	INTEGER (PK)
FirstName	TEXT NOT NULL
LastName	TEXT NOT NULL
Company	TEXT
Country	TEXT
Email	TEXT NOT NULL
SupportRepId	INTEGER (FK → Employee)

Schema: Invoice

Column	Type
InvoiceId	INTEGER (PK)
CustomerId	INTEGER (FK)
InvoiceDate	TIMESTAMP NOT NULL
BillingCountry	TEXT
Total	NUMERIC(10,2) NOT NULL

Requirements:

For each customer, derive every pair of consecutive invoices using LAG(InvoiceDate) partitioned by customer
Compute the gap in days (cast to integer) between current and previous invoice
Take the MIN gap per customer (ignore NULLs from the first invoice)
Return FullName, Country, ShortestGapDays, InvoiceCount
Only include customers with at least 2 invoices
Sort by ShortestGapDays ascending, then FullName ascending
Limit to 15 rows

CTE Window Functions LAG Date Arithmetic MIN Uber Swiggy

📊

📊 View Question Telemetry & Insights

Failure rate, silent bug autopsy & optimal query construction

Inspect →

Master SQL & Data Analytics

Ready to take your skills to the next level? Enroll in our comprehensive Data Analyst Career Track to master SQL, Python, Excel, and Power BI.

Explore the 12-Week Data Analyst Career Track