
How often it ran out
mediumHow often it ran out
Blinkit SQL Interview Question
Two different problems look identical in a daily report. A product missing for one long stretch is a supply failure at the warehouse; one that keeps blinking out and coming back is a reorder point set too low at the store. Telling them apart needs a count of stretches, not of empty days.
For every store and product, return the store_id, the sku, and how many separate runs of out-of-stock days it had as stockout_spells. Leave out any store and product that was never out of stock.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
blinkit_stock_checksTable30 rows
| Column Name | Type |
|---|---|
| check_id | BIGINT |
| store_id | BIGINT |
| sku | VARCHAR |
| checked_on | DATE |
| is_available | BOOLEAN |
blinkit_stock_checksExample Input
| check_id | store_id | sku | checked_on | is_available |
|---|---|---|---|---|
| 1 | 101 | ATTA-5KG | 2024-06-01 | true |
| 2 | 101 | ATTA-5KG | 2024-06-02 | true |
| 3 | 101 | ATTA-5KG | 2024-06-03 | false |
| 4 | 101 | ATTA-5KG | 2024-06-04 | false |
| 5 | 101 | ATTA-5KG | 2024-06-05 | false |
| 6 | 101 | ATTA-5KG | 2024-06-06 | true |
| 7 | 101 | ATTA-5KG | 2024-06-07 | true |
| 8 | 101 | ATTA-5KG | 2024-06-08 | false |
| 11 | 101 | MILK-1L | 2024-06-01 | true |
| 12 | 101 | MILK-1L | 2024-06-02 | false |
| 13 | 101 | MILK-1L | 2024-06-03 | true |
| 14 | 101 | MILK-1L | 2024-06-04 | true |
| 15 | 101 | MILK-1L | 2024-06-05 | true |
| 16 | 101 | MILK-1L | 2024-06-06 | true |
| 17 | 101 | MILK-1L | 2024-06-07 | false |
| 18 | 101 | MILK-1L | 2024-06-08 | false |
Example Output
| store_id | sku | stockout_spells |
|---|---|---|
| 101 | ATTA-5KG | 2 |
| 101 | MILK-1L | 2 |
Explanation
ATTA-5KG was missing on three days in a row and then on one more day later, which is two separate runs rather than four missing days. MILK-1L was also missing twice: once on its own, and once for two days together.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Blinkit
Difficulty
medium
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...