Display the cumulative percentage of total sales for each product.

This question was asked in Interview at black rock. Read more

Company: BlackRock

CTC: 26LPA

Source: LinkedIn

SQL Interview Question

Q. Display the cumulative percentage of total sales for each product.

-- Create the sales_table
CREATE TABLE sales_table (
    sale_id INT AUTO_INCREMENT PRIMARY KEY, -- Unique identifier for each sale
    product_id INT NOT NULL, -- ID of the product
    sales DECIMAL(10, 2) NOT NULL -- Sales amount for the product
);

-- Insert sample data into sales_table
INSERT INTO sales_table (product_id, sales)
VALUES
(101, 500.00),
(102, 300.00),
(103, 200.00),
(101, 400.00),
(102, 600.00),
(103, 800.00),
(104, 1000.00),
(105, 700.00);

sale_id	product_id	sales
1	101	500.00
2	102	300.00
3	103	200.00
4	101	400.00
5	102	600.00
6	103	800.00
7	104	1000.00
8	105	700.00

---

View on DB Fiddle

Solution

SELECT
    product_id,
    SUM(sales) AS prod_sales,
    round(SUM(sales)/SUM(SUM(sales)) over() *100) AS 'cumu_sales_%'
FROM sales_table
GROUP BY product_id;

product_id	prod_sales	cumu_sales_%
101	900.00	20
102	900.00	20
103	1000.00	22
104	1000.00	22
105	700.00	16

---

View on DB Fiddle

Explanation

In this scenario, we used window functions to calculate the cumulative sales percentage for each product.

---

I hope this would have been helpful for you, consider sharing it with your friends. thank you.

• Write a query to group employees by age ranges (e.g., 20–30, 31–40) and count the number of employees in each group.
• What is Pareto Principle
• Running Total for Different Genders: Write an SQL query to find the total score for each gender at each day
• SQL LAG function explained!!
• Page Recommendations: Write an SQL query to recommend pages to the user