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.

⌘/Ctrl + Enter to grade

Write a query, then grade it.

Hints

Try it first. Reveal a hint only when you are stuck.