Big orders or a loyal customer
mediumBig orders or a loyal customer
Best Buy SQL Interview Question
Best Buy's loyalty team is choosing orders to feature in a thank-you campaign. An order qualifies if it was completed and it was either worth more than 3000 or placed by customer 1, the longest-standing customer in the data.
List the order_id, customer_id and amount of every qualifying order.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- 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 |
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 |
Example Output
| order_id | customer_id | amount |
|---|---|---|
| 1001 | 1 | 120.5 |
| 1003 | 1 | 89.99 |
| 1007 | 5 | 3200 |
Explanation
Orders 1001 and 1003 qualify because they were completed and placed by customer 1. Order 1007 qualifies because it was completed and is worth 3,200. Order 1008 also belongs to customer 1, but it was refunded, so it does not qualify.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Best Buy
Difficulty
medium
Topic
logical operators
Your query
Starting the SQL engine...