Query Challenges

Introduction

Harder query challenges that test creative SQL — recursive CTEs, gaps & islands, sessionization, percentiles. These show senior fluency in interviews.

Understanding the topic

Core concepts to understand:

• Gaps & islands: find continuous streaks.
• Sessionization: group events into user sessions.
• Percentiles: percentile_cont(0.95).
• Median per group.
• Recursive: shortest path / org-chart depth.

Syntax reference

Visual workflow / architecture:

Streaks of paid days per user
events ─▶ rownum + date - rn ─▶ groups ─▶ MIN/MAX per group

Real-world use

Every senior data-engineering / backend interview hits at least one of these.

Best practices

• Master gaps & islands — appears constantly.
• Use windows liberally.
• Practice 5–10 times until automatic.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Find user streaks of N consecutive days.
• Q2. Median order value per user.
• Q3. Sessionize events with 30-min idle.
• Q4. 95th percentile latency per service.
• Q5. Shortest path in a graph table.