Large orders with customer names
easyLarge orders with customer names
Amazon SQL Interview Question
Amazon Business account managers call customers personally after any very large order, so they need the customer's name next to each one.
List every order worth more than 3000, with the order_id, the name of the customer who placed it as customer_name, and the amount.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Data Engineer
- Data Scientist
- ML Engineer
- AI Engineer
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
customersTable10 rows
| Column Name | Type |
|---|---|
| customer_id | BIGINT |
| name | VARCHAR |
| country | VARCHAR |
| segment | VARCHAR |
| signup_date | DATE |
ordersExample Input
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | completed | 120.5 |
| 1002 | 2 | 2024-01-07 | completed | 2400 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1005 | 4 | 2024-01-18 | completed | 610.75 |
| 1006 | 2 | 2024-01-22 | completed | 1850.25 |
| 1007 | 5 | 2024-01-29 | completed | 3200 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1009 | 6 | 2024-02-06 | completed | 55.4 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
customersExample Input
| customer_id | name | country | segment | signup_date |
|---|---|---|---|---|
| 1 | Priya Sharma | India | consumer | 2023-01-14 |
| 2 | Marcus Webb | United States | enterprise | 2023-02-03 |
| 3 | Ana Sousa | Brazil | consumer | 2023-02-27 |
| 4 | Liam O'Connor | Ireland | small_business | 2023-03-15 |
| 5 | Yuki Tanaka | Japan | enterprise | 2023-04-02 |
| 6 | Fatima Al-Rashid | United Arab Emirates | consumer | 2023-05-21 |
| 7 | Daniel Okafor | Nigeria | small_business | 2023-06-08 |
Example Output
| order_id | customer_name | amount |
|---|---|---|
| 1007 | Yuki Tanaka | 3200 |
Explanation
Order 1007, worth 3,200, is the only order in the example above 3,000. It was placed by customer 5, Yuki Tanaka, so that name appears next to it.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
easy
Topic
joins
Your query
Starting the SQL engine...