The final boss of SQL interviews

The gap between not knowing SQL and thinking you know SQL is not that big.

SQL’s simplicity is one of its strengths which made it withstand the test of time.

The learning curve is relatively flat, you don’t even have to learn a lot of syntax.

It may be the only programming language where one doesn’t have to look at syntax.

When it comes to SQL interviews, the last thing you do is worry about them.

If you use it on your job, you don’t need much preparation either.

Here’s the difficulty ladder of SQL questions in interviews:

• Simple SELECT

• Aggregations

• JOINs

• Window functions

• A combo of all of the above

If you’re good with these, you can handle 95% of the SQL interviews today.

However, if you’re aiming for a senior role or are just curious about what comes next?

This post is for you.

Because I’m here to tell you there’s one level left in this game.

I’m referring to a class of problems called the Islands & Gaps problem in SQL.

It takes many forms but let’s take a look at an example.

The data below shows user sessions with IDs, start, and end times.

The goal is to identify periods of continuous user activity, something like this:

The overlapping periods here are Islands separated by Gaps, hence the name.

This might be one of the most unintuitive problems in SQL.

Designing a solution without ever having seen it in a 30-60min interview is a tall order.

You want to group these rows that overlap and aggregate them to find the duration.

However, there’s not really anything to do the GROUP BY with.

Building a variable to group islands is the real challenge of the problem.

If you made it this far, I’d encourage you to give it a try.

Many resources go over the approach and I found this one to do the best job.

What I’m here to do is make you aware of its existence so you can get a head start.

There’s another variant with a partition variable, here’s an example of that.

While I don’t suggest memorizing solutions, it’s wise to be prepared.

Good luck!