Practice · hard · dataset: ecom
Price deviation ranking
A pricing analyst wants to know which products sit furthest above their category’s average price. For
each category, rank products by their deviation from the category average, where deviation is
unit_price minus the category’s average unit_price.
Return category, product_id, name, unit_price, category_avg_price, deviation, and
price_rank. Round the three money values to two decimals. Use rank() for price_rank, so
products with the same deviation share a rank. Return every product whose rank is three or better.
Sort by category ascending, then price_rank ascending, then product_id ascending.
Tables available
| Table | Columns |
|---|---|
products |
product_id, name, category, unit_price |
The average must be computed per category over the whole table, not over the products already filtered to the top three.
Write a query, then grade it.
Hints
Try it first. Reveal a hint only when you are stuck.