Averages that skip NULLs
hardAverages that skip NULLs
Amazon SQL Interview Question
Two Amazon analysts reported different average discounts for the same catalog, and both were right. One averaged only the products that have a discount recorded. The other treated a product with no discount as 0% off.
Calculate both numbers in a single row: the first analyst's average as avg_listed_discount and the second analyst's average as avg_overall_discount. Do not round the results.
Asked of
- Data Analyst
- Data Scientist
- Analytics Engineer
- ML Engineer
- AI Engineer
productsTable15 rows
| Column Name | Type |
|---|---|
| product_id | BIGINT |
| product_name | VARCHAR |
| category | VARCHAR |
| brand | VARCHAR |
| price | DOUBLE |
| discount_pct | BIGINT |
| stock_quantity | BIGINT |
| launch_date | DATE |
| is_active | BOOLEAN |
productsExample Input
| product_id | product_name | category | brand | price | discount_pct | stock_quantity | launch_date | is_active |
|---|---|---|---|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | Logitech | 24.99 | 10 | 150 | 2023-02-14 | true |
| 2 | Mechanical Keyboard Pro | Electronics | Keychron | 119 | NULL | 40 | 2023-05-01 | true |
| 3 | Noise Canceling Headphones | Electronics | Sony | 299.99 | 15 | 0 | 2022-11-20 | true |
| 4 | Standing Desk | Furniture | Flexispot | 349 | NULL | 12 | 2023-08-09 | true |
| 5 | Ergonomic Chair Pro | Furniture | Steelcase | 1299 | 5 | 5 | 2021-06-30 | true |
| 6 | Desk Lamp | Furniture | NULL | 39.5 | NULL | 80 | 2024-01-15 | true |
| 7 | USB-C Hub | Electronics | Anker | 45 | 20 | 200 | 2023-03-22 | true |
| 8 | Notebook Set | Stationery | Moleskine | 18 | NULL | 0 | 2022-09-01 | false |
Example Output
| avg_listed_discount | avg_overall_discount |
|---|---|
| 12.5 | 6.25 |
Explanation
Four of the eight products have a discount: 10, 15, 5 and 20, which add up to 50. Averaging only those four gives 12.5. Counting the other four products as 0% off spreads the same 50 across all eight products, which gives 6.25.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Amazon
Difficulty
hard
Topic
null handling
Your query
Starting the SQL engine...