How often it ran out

window functions

How 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 NameType
check_idBIGINT
store_idBIGINT
skuVARCHAR
checked_onDATE
is_availableBOOLEAN

blinkit_stock_checksExample Input

check_idstore_idskuchecked_onis_available
1101ATTA-5KG2024-06-01true
2101ATTA-5KG2024-06-02true
3101ATTA-5KG2024-06-03false
4101ATTA-5KG2024-06-04false
5101ATTA-5KG2024-06-05false
6101ATTA-5KG2024-06-06true
7101ATTA-5KG2024-06-07true
8101ATTA-5KG2024-06-08false
11101MILK-1L2024-06-01true
12101MILK-1L2024-06-02false
13101MILK-1L2024-06-03true
14101MILK-1L2024-06-04true
15101MILK-1L2024-06-05true
16101MILK-1L2024-06-06true
17101MILK-1L2024-06-07false
18101MILK-1L2024-06-08false

Example Output

store_idskustockout_spells
101ATTA-5KG2
101MILK-1L2

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.