Rearrange Products Table - Leetcode Solution

Problem:

Given a products table that contains three columns: product_id, category, and revenue.

You are asked to rearrange the table such that it has four columns: product_id, category, total_quantity, and total_revenue.

The total_quantity column represents the total number of products sold in each category, and the total_revenue column represents the total revenue generated by each category.

The table should be ordered in ascending order by category.

Solution:

To solve this problem, we will need to use GROUP BY and SUM functions to group the data by category and calculate the total quantity and revenue for each category.

The query will look like this:

SELECT product_id, category, SUM(revenue) AS total_revenue, COUNT(*) AS total_quantity
FROM products
GROUP BY category
ORDER BY category ASC;

Explanation:

• We use the SELECT statement to select the required columns from the products table, which are product_id, category, revenue, and quantity.

• We use the GROUP BY statement to group the data by category.

• We use the SUM function to calculate the total revenue for each category.

• We use the COUNT function to calculate the total quantity for each category.

• We order the result in ascending order by category using the ORDER BY statement.

• Finally, we execute the query and get the rearranged products table with four columns. The first column is product_id, the second column is category, the third column is total_revenue, and the fourth column is total_quantity.

This query will give us the desired output for the Rearrange Products Table problem on leetcode.

1