Order Cancellation and Price Range Query | Dataford Interview Questions

Meesho's grocery analytics team wants to compare assortment pricing and order reliability across two fulfillment locations. Write a PostgreSQL query for calendar year 2024 using the two tables below.

Requirements

Join products to orders using product_id and restrict orders to 2024.
Return each location's minimum price, maximum price, and price range, calculated as maximum price minus minimum price.
Calculate the percentage of orders whose status is Cancelled or Not Delivered, using all 2024 orders as the denominator.
Include locations with products but no 2024 orders, using zero as their failed-order rate.
Rank locations from highest to lowest failed-order rate, using a deterministic alphabetical tie-breaker.

Schema

products

Column	Type	Description
product_idPK	INT	Unique grocery product identifier
product_name	VARCHAR(100)	Grocery product name
location	VARCHAR(50)	Fulfillment location
price	NUMERIC(10,2)	Listed product price in rupees

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
product_id	INT	Ordered product identifier referencing products.product_id
order_date	DATE	Date the order was placed
status	VARCHAR(30)	Delivery outcome such as Delivered, Cancelled, or Not Delivered

Your question is Order Cancellation and Price Range Query. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem