Top Customers by Order Frequency | Dataford Interview Questions

Swiggy wants to identify its most frequent customers in a selected delivery region. Write a PostgreSQL query to return the top 10 customers by the number of delivered orders in Bengaluru.

Requirements

Join customers with orders using customer_id.
Include only customers whose region is Bengaluru and whose order status is delivered.
Count delivered orders for each customer and return the top 10.
Sort by order frequency descending, then customer_id ascending to make ties deterministic.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer's name
region	VARCHAR(50)	Customer's delivery region

orders

Column	Type	Description
order_idPK	INTEGER	Unique order identifier
customer_id	INTEGER	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Current order status

Your question is Top Customers by Order Frequency. 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