Select max-discount product per category

A products table stores one row for every catalog item. Each row contains a category, a normal list_price, and an optional sale_price. For every category, select exactly one product: the item with the greatest absolute discount. If multiple items in a category share the same maximum discount, choose the item with the smallest product_id. The absolute discount is defined as: $$ discount\_amount = \max(\, list\_price - \text{COALESCE}(sale\_price,\ list\_price),\, 0\,) $$ So a…