Cunning Questions (Make the Agent Sweat)
🧠 1) Data Quality / Consistency Traps
Which orders have total_amount that doesn’t match the sum of order_items?
Show orders where total_amount is 0 but items exist.
Find duplicate users (same phone/email OR same name + address).
List menu items that have price <= 0 (should never happen).
Show orders that have no order_items (empty orders).
Find any order_items where item_price is different from menu_items.price (price mismatch).
Which orders are assigned to OFFLINE agents? (should be suspicious)
Which restaurants have rating NULL or 0 but have many orders?
💰 2) Business Insights (Hard Mode)
Which restaurant made the most revenue (based on order_items, not total_amount)?
Which restaurant has the highest average order value?
Which user spent the most money overall?
Which user ordered the most times but spent the least? (cheap frequent customer)
Which item generates the most revenue (not the most quantity)?
Top 3 restaurants by revenue AND their best-selling item.
Which restaurant is rated high but has low orders? (overrated / hidden gem)
Which restaurant has many orders but low rating? (problem restaurant)
🚴 3) Delivery Agent Performance / Weird Assignments
Which agent delivered the most orders?
Which agent handled the highest total order value?
Which agents have never been assigned any order?
Which agents are marked AVAILABLE but still have active orders (PLACED/PREPARING/OUT_FOR_DELIVERY)?
Which agent has the most cancelled orders? (bad sign)
Which agent has the most “OUT_FOR_DELIVERY” orders stuck (not delivered)?
⏳ 4) Time-based / Trend Questions (Usually Hard for Agents)
Show the busiest hour of the day (most orders).
Show which day had the highest revenue.
What’s the average time gap between orders per user?
Which customers order mostly on weekends vs weekdays? (if enough data)
List restaurants with increasing order trend (last 7 days vs previous 7 days).
(Your current dummy data doesn’t include enough time variance, but the question is valid.)
🍔 5) “Customer Habits” / Behavior Mining
Which users are loyal to only 1 restaurant?
Which users order from 3+ different restaurants?
Which user has the highest average items per order?
Find users who always order expensive items (avg item_price > $12).
Which restaurant has the most repeat customers?
Which items are often ordered together (combo patterns)?
🕵️ 6) Suspicious / Fraud Style Questions
Find orders where quantity is unusually high (>=5 items in a single line).
Which customers cancel orders too frequently?
Which agent is linked to unusually high cancellations?
Restaurants with sudden spike in cancellations.
Users placing multiple orders back-to-back within 10 minutes. (needs timestamps)
🔀 7) Deep Join / Multi-table Questions (Harder SQL)
For each restaurant, show: total orders, delivered orders, cancelled orders, total revenue, avg rating.
For each user, show: total orders, total spent, favorite restaurant, favorite item.
Find the most ordered item per restaurant.
Show the “customer → restaurant → delivery agent” chain for each order.
Which restaurant has items but never got ordered?
Which users ordered from restaurants outside their city/state (based on address strings)?
🧨 8) Evil Edge-Case Questions (These break weak agents)
Show orders where order_status = PLACED but total_amount > 0 and agent assigned (suspicious flow).
Find menu items marked unavailable but still ordered in order_items.
Find restaurants with no menu items but have orders (impossible case).
Find users who exist but never ordered anything.
Find restaurants that have menu items but never got a single order.
🏆 “Boss Level” Questions (If you REALLY want pain)
✅ 1) “Revenue should come from order_items, not total_amount”
Recalculate each order’s total from order_items and compare with orders.total_amount.
Show mismatches + correct total.
✅ 2) Full leaderboard in one output
Give me a leaderboard of restaurants with: revenue, total orders, delivered %, avg order value, best-selling item.
✅ 3) One query that answers everything
Give a complete order report: Order ID, customer name, restaurant name, items list (item name + qty), total items, final bill, agent name, status.
