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
-- 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 |
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 |
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.
Hi, I am Vishal Jaiswal, I have about a decade of experience of working in MNCs like Genpact, Savista, Ingenious. Currently i am working in EXL as a senior quality analyst. Using my writing skills i want to share the experience i have gained and help as many as i can.
Welcome to Day 13 of Learning Python for Data Science! Today, we’re focusing on three…
Test your understanding of Python Data Structure, which we learned in our previous lesson of…
Welcome to Day 12 of Learning Python for Data Science. Today, we’ll dive into Pandas,…
NumPy Array in Python is a powerful library for numerical computing in Python. It provides…
Welcome to Day 9 of Learning Python for Data Science. Today we will explore comprehensions,…
Test your understanding of Python Data Structure, which we learned in our previous lesson of…