SQL Quest › SQL Interview Questions › Subqueries & CTEs

Three-Day Login Streaks

HardProQuerying BasicsSubqueries & CTEsWindow FunctionsDate FunctionsAggregation & GroupingJoins

Retention wants every streak of logins on 3 or more consecutive calendar days. logins has one row per login, with a timestamp in login_at — and some customers log in more than once a day.

Show exactly these 3 columns: name (from customers), streak_start (the streak's first day, YYYY-MM-DD) and streak_length (its number of days). A customer with two separate streaks appears twice. Order by streak_length descending, then name, then streak_start.

The trap is the second login on the same day: counted as its own row, it breaks a real streak in two. A streak may cross the end of a month.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

logins

login_idcustomer_idlogin_at
112024-03-01 08:12:00
212024-03-02 09:40:00
342024-03-01 18:05:00

customers

customer_idnameemailsignup_datemembershiptotal_orders
1John Smithjohn.smith@email.com2023-01-15Gold15
2Emma Wilsonemma.wilson@email.com2023-03-20Silver8
3Michael Brownmichael.brown@email.com2023-02-10Gold12

Expected output: 5 streaks — two of 5 days, one of 4, two of 3

Hint

This is a Pro challenge — the hint, the step-by-step tutor and the reference solution open in the app.

Concepts

SELECT CTE Window Functions ROW_NUMBER Date Functions DISTINCT GROUP BY HAVING JOIN Gaps and Islands

Practise the topic: SQL practice questions · CTE practice · Window function practice · Date function practice · GROUP BY exercises · JOIN practice · Ranking function practice · Advanced SQL interview questions

Read the concept: SQL CTE tutorial

In these company practice sets

Amazon

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Related questions

Top 10% Users by Transaction VolumeHard · ProMonth-over-Month Volume GrowthHard · ProTop 3 Spending Categories per CountryHard · ProThree Login Days in Any 7-Day WindowHard · ProA Subquery That Returns One ValueEasy · FreeA Subquery That Returns a Set (IN)Easy · Free

Where would this cost you points in an interview?

Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.

Take the readiness test