Longest stockout run
hardLongest stockout run
Blinkit SQL Interview Question
Blinkit judges a dark store on how long a product stays missing, not on how often it goes missing. One empty day is a delivery that slipped. Four empty days in a row is a customer who has already installed another app.
For every store and product, return the store_id, the sku, and the length in days of its longest unbroken run of days out of stock as longest_stockout_days. 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 | longest_stockout_days |
|---|---|---|
| 101 | ATTA-5KG | 3 |
| 101 | MILK-1L | 2 |
Explanation
ATTA-5KG was missing on three days in a row, came back for two days, then went missing for one more day, so its longest run is 3. MILK-1L was missing once on its own and later for two days together, so its longest run is 2.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Blinkit
Difficulty
hard
Topic
window functions
Language
SQL
Your query
Starting the SQL engine...