Consecutive Low-Sales Islands per Product
SQL coding challenge · Difficulty: hard · Topic: Gap and Island Problems · +200 XP
A retail analytics team wants to find runs of consecutive low-sales days for each product. A day is *low* when quantity_sold is strictly less than 10. An *island* is a maximal run of consecutive calendar days for one product where every day has a row and every day is low. A day with no row for the product, or a day with 10 or more units, ends the island. A single low day on its own is an island of 1 day.
For each island return product_id, start_date, end_date and num_days, ordered by product_id, then start_date. Products with no low days do not appear. Each product has at most one row per day.
Example Input
`
sales table:
sale_id | sale_date | product_id | quantity_sold
1 | 2024-01-01 | 1 | 5
2 | 2024-01-02 | 1 | 8
3 | 2024-01-03 | 1 | 12
4 | 2024-01-04 | 1 | 3
5 | 2024-01-05 | 1 | 9
6 | 2024-01-06 | 1 | 7
7 | 2024-01-08 | 1 | 4
8 | 2024-01-09 | 1 | 15
9 | 2024-01-01 | 2 | 20
10 | 2024-01-02 | 2 | 9
11 | 2024-01-03 | 2 | 9
12 | 2024-01-04 | 2 | 10
13 | 2024-01-05 | 2 | 2
14 | 2024-01-01 | 3 | 11
15 | 2024-01-02 | 3 | 30
`
Expected Output
`
product_id | start_date | end_date | num_days
1 | 2024-01-01 | 2024-01-02 | 2
1 | 2024-01-04 | 2024-01-06 | 3
1 | 2024-01-08 | 2024-01-08 | 1
2 | 2024-01-02 | 2024-01-03 | 2
2 | 2024-01-05 | 2024-01-05 | 1
`
Product 1 has no row for 2024-01-07, so 2024-01-08 starts a new island. Product 2 sold exactly 10 on 2024-01-04, which is not low. Product 3 never has a low day.
What this SQL challenge teaches you
“Consecutive Low-Sales Islands per Product” is a hard-level SQL challenge focused on Gap and Island Problems. Working through it gives you hands-on practice with gaps and islands, ROW_NUMBER, DATE_SUB, window functions, CTE — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.
Concepts covered
- gaps and islands
- ROW_NUMBER
- DATE_SUB
- window functions
- CTE
How to approach it
If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:
- Keep only the low days first: WHERE quantity_sold < 10.
- Number the low days of each product in date order with ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date).
- For consecutive dates, sale_date minus that row number (DATE_SUB(sale_date, INTERVAL rn DAY)) is the same value -- it identifies the island.
- A missing date makes the difference jump, so it starts a new island automatically -- comparing with the previous row alone would not notice the gap.
- GROUP BY product_id and that island key, then take MIN/MAX of the date and COUNT(*).
How to practise it on PySpark.in
Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.
Related SQL challenges
- Top 3 Products per Category
- Running Total Revenue
- Find Duplicate Emails
- Median Salary per Department
- Second Highest Salary
- Latest Order Per Customer
Helpful resources
Frequently asked questions
Do I need to install Spark or a database to solve this?
No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.
Is this challenge free?
Yes - the problem, the sample dataset, the hints and unlimited test runs are free.
What level is it?
It is rated hard and covers Gap and Island Problems.