Lesson 4 of 10 · 45 min
Advanced interview challenges
By the end of this lesson you can solve the problems senior analyst and analytics-engineering interviews end with (gaps, islands of consecutive days, recursive hierarchies, cumulative shares, cohort repeat rates and rolling windows over complete calendars) by breaking each into named CTE steps.
Advanced questions look intimidating because the final query is long. In practice each one is three or four small steps, and the interviewer wants to see those steps. The rule for this round: one CTE per idea, each with a name that says what it holds, and run each CTE on its own before stacking the next.
The data in this lesson is mostly Mizan Cloud (saas): subscription rows with start and end dates, and a stream of product events. You will also visit Masar Holding's org chart, Wadi Market's orders and the Wadi Market analytics mart.
Gaps
A gap is time between two rows of the same entity where nothing was happening: a customer who cancelled and came back months later, a sensor that stopped reporting, an invoice number that was skipped. The shape is always the same: sort each entity's rows, use LAG to put the previous row's end next to the current row's start, and keep the rows where the start is later than the previous end.
This lesson, its 6 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons