Select max-discount product per category

You have a catalog of products. For each category, return exactly one product: the one with the largest absolute discount; if multiple products in the same category have the same discount, return the one with the smallest product_id. Define absolute discount as max(list_price - COALESCE(sale_price, list_price), 0). Return: category, product_id, list_price, sale_price, discount_amount. Write a single SQL query that works if sale_price can be NULL and never below 0, using window functions; also show a version without window functions. Schema and sample data:

Tables

• products(product_id INT PRIMARY KEY, category VARCHAR(50) NOT NULL, list_price DECIMAL(10,2) NOT NULL, sale_price DECIMAL(10,2) NULL)

Sample rows (products) product_id | category | list_price | sale_price 101 | Phones | 799.00 | 699.00 102 | Phones | 999.00 | 749.00 103 | Phones | 999.00 | 749.00 201 | Laptops | 1299.00 | 1099.00 202 | Laptops | 1399.00 | 1199.00 301 | TVs | 899.00 | NULL

Expected result rows category | product_id | list_price | sale_price | discount_amount Phones | 102 | 999.00 | 749.00 | 250.00 Laptops | 201 | 1299.00 | 1099.00 | 200.00 TVs | 301 | 899.00 | NULL | 0.00

Sub-questions:

• Provide the window-function solution (ROW_NUMBER partitioned by category ordered by discount_amount DESC, product_id ASC).
• Provide a without-window solution (e.g., join to a per-category max-discount subquery with tie-break on product_id).
• Explain how your query avoids floating point precision issues and handles NULL sale_price.