Prime vs. standard order value
mediumPrime vs. standard order value
Amazon SQL Interview Question
Amazon's membership team wants to know whether Prime members place bigger orders than other customers.
Label customers as "Prime" or "Standard" in a column called membership. For each label, find the number of delivered orders as delivered_orders and their average value as avg_order_value, rounded to 2 decimal places. Returned and canceled orders don't count. An order's value is the quantity times the unit price. Sort by membership.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
ordersTable30 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| category | VARCHAR |
| quantity | BIGINT |
| unit_price | DOUBLE |
| status | VARCHAR |
customersTable15 rows
| Column Name | Type |
|---|---|
| customer_id | BIGINT |
| customer_name | VARCHAR |
| state | VARCHAR |
| signup_date | DATE |
| prime_member | BOOLEAN |
ordersExample Input
| order_id | customer_id | order_date | category | quantity | unit_price | status |
|---|---|---|---|---|---|---|
| 10001 | 501 | 2024-05-01 | Books | 2 | 14.99 | delivered |
| 10002 | 502 | 2024-05-01 | Electronics | 1 | 249 | delivered |
| 10003 | 503 | 2024-05-02 | Home | 3 | 19.5 | returned |
| 10004 | 501 | 2024-05-02 | Toys | 1 | 34.99 | delivered |
| 10005 | 504 | 2024-05-03 | Grocery | 6 | 4.25 | delivered |
| 10006 | 505 | 2024-05-03 | Electronics | 2 | 89.99 | canceled |
| 10007 | 502 | 2024-05-04 | Books | 1 | 22 | delivered |
| 10008 | 506 | 2024-05-04 | Home | 1 | 129 | delivered |
| 10009 | 507 | 2024-05-05 | Grocery | 12 | 2.99 | delivered |
| 10010 | 503 | 2024-05-05 | Electronics | 1 | 59.99 | returned |
| 10011 | 508 | 2024-05-06 | Toys | 2 | 17.49 | delivered |
| 10012 | 504 | 2024-05-06 | Books | 3 | 9.99 | delivered |
customersExample Input
| customer_id | customer_name | state | signup_date | prime_member |
|---|---|---|---|---|
| 501 | Avery Johnson | CA | 2022-03-14 | true |
| 502 | Mateo Rivera | TX | 2021-11-02 | false |
| 503 | Hannah Brooks | NY | 2023-01-19 | true |
| 504 | Jamal Carter | WA | 2022-08-07 | true |
| 505 | Sofia Nguyen | CA | 2020-05-25 | true |
| 506 | Ethan Walsh | TX | 2023-06-30 | false |
| 507 | Leila Haddad | NY | 2021-02-11 | true |
| 508 | Ryan Patel | FL | 2022-12-03 | false |
| 509 | Chloe Martin | WA | 2023-09-15 | true |
| 510 | Diego Alvarez | FL | 2021-07-22 | true |
Example Output
| membership | delivered_orders | avg_order_value |
|---|---|---|
| Prime | 5 | 31.26 |
| Standard | 4 | 108.75 |
Explanation
In the example, Prime members' delivered orders are worth 29.98, 34.99, 25.50, 35.88 and 29.97, an average of 31.26. Hannah Brooks is a Prime member, but both of her orders were returned, so neither counts.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
medium
Topic
joins
Language
SQL
Your query
Starting the SQL engine...