Write SQL to find top net-revenue products

Using the sample schema and data below, write a single SQL query that returns, for the last 7 days relative to today (use today = 2025-09-01, so the window is 2025-08-26 to 2025-09-01 inclusive), the top product by net revenue within each category. Output columns: category, product_id, net_units, net_revenue_usd. Definitions: • Consider only orders whose order_date is in the 7-day window. • Net units = sum(qty from order_items for those orders) − sum(qty_returned from returns with return_date also in the 7-day window and matching the same order_id and product_id). • Net revenue = net units × products.price_usd. • If there's a tie on net revenue, break ties by higher net units, then by lexicographically smaller product_id. Use analytic functions (e.g., row_number) to pick winners per category.

Sample tables (ASCII): users(user_id, city) 1, SF 2, NY 3, SF 4, LA

products(product_id, name, category, price_usd) P1, Desk, Furniture, 200 P2, Chair, Furniture, 120 P3, Monitor, Electronics, 300 P4, Keyboard, Electronics, 80

orders(order_id, user_id, order_date) O1, 1, 2025-08-26 O2, 2, 2025-08-27 O3, 1, 2025-08-30 O4, 3, 2025-08-31 O5, 4, 2025-09-01

order_items(order_id, product_id, qty) O1, P1, 1 O1, P2, 2 O2, P3, 1 O3, P3, 1 O3, P4, 3 O4, P2, 1 O5, P1, 1

returns(return_id, order_id, product_id, qty_returned, return_date) R1, O1, P2, 1, 2025-08-31 R2, O3, P4, 1, 2025-09-01