Coupon Eligibility
ASKED IN INTERVIEWZomato
Problem Statement
Zomato wants to boost user retention by offering a special "First-Time Coupon" to loyal customers who haven't discovered the benefits of discounts yet.
Write an SQL query to identify users who have placed 5 or more orders and have never used a coupon (coupon_id is NULL for all their orders).
Rules:
- A user must have total_orders >= 5.
- A user must have 0 orders where a coupon_id was applied.
- Output columns: user_id, total_orders.
- Sort the results by user_id in ascending order.
Table Schema:
- Orders: order_id, user_id, order_date, order_amount, coupon_id (VARCHAR - NULL if not used).
Tests your understanding of
Basic SQL, Aggregation, Filtering and Conditional Logic
Input Tables
Orders
| user_id(INTEGER) | coupon_id(VARCHAR) |
|---|---|
| 1 | null |
| 1 | null |
| 1 | null |
| 1 | null |
| 1 | null |
| 2 | SAVE10 |
| 2 | null |
| 2 | null |
| 2 | null |
| 2 | null |
| 3 | null |
| 4 | null |
| 4 | null |
| 4 | null |
| 4 | null |
| 4 | null |
Expected Output
| user_id(INTEGER) | total_orders(INTEGER) |
|---|---|
| 1 | 5 |
| 4 | 5 |
Tags
EasyASKED IN INTERVIEWBasic SQLAggregationFilteringConditional Logic
10-15 min
74%