Zomato: Blinkit Store Items Below Their Reorder Level

Easy
1029.

Zomato: Blinkit Store Items Below Their Reorder Level

A Blinkit dark store reorders an item when its stock falls below the item's reorder level. The store manager wants the morning reorder list.

For every active product (is_active = 1) whose units on hand are below its reorder_level, return product_name, on_hand and shortfall (reorder_level - on_hand). A product with no row in StoreStock has 0 units on hand. Order by shortfall descending, then product_name ascending.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Product

ColumnType
product_idint
product_namevarchar
reorder_levelint
is_activebool

Primary key: product_id.

The store's catalogue; reorder_level is always at least 1. Product names are unique.

Table: StoreStock

ColumnType
product_idint
on_handint

Primary key: product_id.

Units on the shelves at opening. A product that was never stocked has no row.

Example

The rows must come back in the order shown.

Product

product_idproduct_namereorder_levelis_active
1Toned Milk401
2Brown Bread301
3Farm Eggs201
4Bananas251
5Instant Noodles101
6Paneer Block120
7Cola Bottle241

StoreStock

product_idon_hand
125
230
35
460
750

Output

product_nameon_handshortfall
Farm Eggs515
Toned Milk2515
Instant Noodles010
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Zomato: Blinkit Store Items Below Their Reorder Level

A Blinkit dark store reorders an item when its stock falls below the item's reorder level. The store manager wants the morning reorder list.

  • Difficulty: Easy
  • Topics: Joins, Conditional Logic
  • Dialect: MySQL
  • Problem: #1029

Problem statement

A Blinkit dark store reorders an item when its stock falls below the item's reorder level. The store manager wants the morning reorder list.

For every active product (is_active = 1) whose units on hand are below its reorder_level, return product_name, on_hand and shortfall (reorder_level - on_hand). A product with no row in StoreStock has 0 units on hand. Order by shortfall descending, then product_name ascending.

A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.

Tables

Table: Product

ColumnType
product_idint
product_namevarchar
reorder_levelint
is_activebool

Primary key: product_id.

The store's catalogue; reorder_level is always at least 1. Product names are unique.

Table: StoreStock

ColumnType
product_idint
on_handint

Primary key: product_id.

Units on the shelves at opening. A product that was never stocked has no row.

Examples

Example 1

Product

product_idproduct_namereorder_levelis_active
1Toned Milk401
2Brown Bread301
3Farm Eggs201
4Bananas251
5Instant Noodles101
6Paneer Block120
7Cola Bottle241

StoreStock

product_idon_hand
125
230
35
460
750

Output

product_nameon_handshortfall
Farm Eggs515
Toned Milk2515
Instant Noodles010

How to solve Zomato: Blinkit Store Items Below Their Reorder Level

Products that were never stocked are the ones most in need of a reorder, yet they have no StoreStock row, so an inner join would silently drop them. A LEFT JOIN from Product keeps them with s.on_hand NULL, and COALESCE(s.on_hand, 0) turns that NULL into the 0 the statement asks for.

The filter then keeps active products whose (defaulted) stock is strictly below the reorder level. Writing the test on s.on_hand directly would be wrong: NULL < 40 is NULL, not true, and the never-stocked items would vanish again. The shortfall is the level minus the defaulted stock, and the order is by shortfall with the name as the tie-breaker.

The alternative fetches each product's stock with a scalar subquery wrapped in IFNULL, then filters in an outer query. Both make one pass over the catalogue with a key lookup per product.

Reference solution (MySQL)

SELECT p.product_name,
       COALESCE(s.on_hand, 0) AS on_hand,
       p.reorder_level - COALESCE(s.on_hand, 0) AS shortfall
FROM Product p
LEFT JOIN StoreStock s ON s.product_id = p.product_id
WHERE p.is_active = 1
  AND COALESCE(s.on_hand, 0) < p.reorder_level
ORDER BY shortfall DESC, p.product_name

Another way

SELECT product_name, on_hand, reorder_level - on_hand AS shortfall FROM (
  SELECT p.product_name, p.reorder_level, p.is_active,
         IFNULL((SELECT s.on_hand FROM StoreStock s WHERE s.product_id = p.product_id), 0) AS on_hand
  FROM Product p
) t
WHERE is_active = 1 AND on_hand < reorder_level
ORDER BY shortfall DESC, product_name ASC

← Zomato: Sold-Out Movie Shows on District · Zomato: Hyperpure Invoices Paid Late or Still Overdue →