Data Analyst · SQL · Senior · Coding

There's an events table with the columns user_id and event_time, and it has billions of rows. Write a SQL query that splits each user's events into sessions: a new session starts if more than thirty minutes have passed since the previous event. For each session, return the user, the session number, the start and end time, and the number of events.

~15 min by voice

How to approach it

This checks whether you know the gaps and islands technique with window functions: LAG, a gap flag, a running sum. For a senior something else matters too: whether you can recompute such a query incrementally on billions of rows, account for late events and the physical layout of the data.

Have you seen this question in a real interview?

Answers0

Avg. score—

Strong—

Practiced0

Topics

gaps and islands sessionization

Answer by voice

The interviewer will ask this question and listen. Think out loud and ask for a hint if you need one. When you're done, pause or tap “That's my answer”. In about a minute, your score and the points you covered show up here.

Best with headphones · ~15 min