Total Revenue by Product Category You are a data analyst at Mercha , an online retail platform that sells products across multiple categories. The finance team needs a category-level revenue summary to determine which product categories are driving the most sales. You have access to two tables: Table: products Column Type Description product_id INT Unique product identifier product_name VARCHAR Name of the product category VARCHAR Category the product belongs to (e.g., Electronics, Clothing) Table: order_items Column Type Description item_id INT Unique line-item identifier product_id INT References the product sold quantity INT Number of units sold unit_price NUMERIC(10,2) Sale price per unit Your Task: Write a query that returns the total revenue per product category . Revenue for each line item is calculated as quantity × unit_price . Sort the results by total revenue in descending order . Expected Output Columns: category , total_items_sold , total_revenue

Table Setup (SQL Schema)

-- DDL: Create tables
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(150),
    category     VARCHAR(100)
);

CREATE TABLE order_items (
    item_id    INT PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    quantity   INT,
    unit_price NUMERIC(10, 2)
);

-- DML: Insert sample data
INSERT INTO products (product_id, product_name, category) VALUES
(1,  'Wireless Headphones',  'Electronics'),
(2,  'Mechanical Keyboard',  'Electronics'),
(3,  'USB-C Hub',            'Electronics'),
(4,  'Running Shoes',        'Footwear'),
(5,  'Leather Sandals',      'Footwear'),
(6,  'Yoga Mat',             'Sports'),
(7,  'Resistance Bands Set', 'Sports'),
(8,  'Cotton T-Shirt',       'Clothing'),
(9,  'Denim Jacket',         'Clothing'),
(10, 'Desk Lamp',            'Home & Office');

INSERT INTO order_items (item_id, product_id, quantity, unit_price) VALUES
(1,  1,  2,  89.99),
(2,  1,  1,  89.99),
(3,  2,  3,  120.00),
(4,  3,  5,  45.50),
(5,  3,  2,  45.50),
(6,  4,  2,  75.00),
(7,  4,  4,  75.00),
(8,  5,  3,  40.00),
(9,  6,  6,  35.00),
(10, 6,  2,  35.00),
(11, 7,  4,  25.00),
(12, 8,  10, 18.00),
(13, 8,  5,  18.00),
(14, 9,  3,  95.00),
(15, 10, 4,  55.00),
(16, 2,  1,  120.00),
(17, 5,  2,  40.00),
(18, 7,  3,  25.00);