You are working as a data analyst for ShopNest , a growing e-commerce platform. The operations team wants to understand how well each product is selling by looking at the total number of units sold across all customer orders. This helps them make decisions about restocking, promotions, and discontinuing slow-moving items. The database contains two tables: products — which stores product details including product_id , product_name , and category — and order_items — which records each line item in a customer order, including order_item_id , product_id , quantity , and unit_price . Each row in order_items represents a product included in a specific order, and one order can have multiple items. Your task is to write a SQL query that returns the name of each product along with the total quantity sold (i.e., the sum of all quantity values in order_items for that product). The results should be sorted in descending order by total quantity sold so the best-selling products appear at the top. Join the products table with the order_items table on product_id . Use SUM(quantity) to calculate the total units sold per product. Group results by product_id and product_name . Return columns: product_name and total_quantity_sold . Sort the output by total_quantity_sold in descending order. Hint: Make sure to include the product_name in your GROUP BY clause since you are selecting it alongside an aggregate function. A common pitfall is forgetting to group by all non-aggregated columns in the SELECT list, which will cause a PostgreSQL error.

Table Setup (SQL Schema)

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(50) NOT NULL
);

CREATE TABLE order_items (
  order_item_id SERIAL PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL REFERENCES products(product_id),
  quantity INT NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL
);

INSERT INTO products (product_name, category) VALUES
  ('Wireless Mouse', 'Electronics'),
  ('USB-C Hub', 'Electronics'),
  ('Notebook A5', 'Stationery'),
  ('Ballpoint Pen Set', 'Stationery'),
  ('Desk Lamp', 'Home Office'),
  ('Mechanical Keyboard', 'Electronics'),
  ('Sticky Notes Pack', 'Stationery'),
  ('Monitor Stand', 'Home Office');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 2, 25.99),
  (1, 3, 5, 4.49),
  (2, 2, 1, 45.00),
  (2, 6, 1, 89.99),
  (3, 1, 3, 25.99),
  (3, 5, 2, 34.99),
  (4, 4, 10, 3.99),
  (4, 7, 4, 2.99),
  (5, 3, 6, 4.49),
  (5, 1, 1, 25.99),
  (6, 8, 2, 29.99),
  (6, 2, 3, 45.00),
  (7, 6, 2, 89.99),
  (7, 4, 5, 3.99),
  (8, 7, 8, 2.99),
  (8, 5, 1, 34.99),
  (9, 3, 3, 4.49),
  (9, 1, 2, 25.99),
  (10, 8, 1, 29.99),
  (10, 2, 2, 45.00);