Blog

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_idproduct_idsales
1101500.00
2102300.00
3103200.00
4101400.00
5102600.00
6103800.00
71041000.00
8105700.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_idprod_salescumu_sales_%
101900.0020
102900.0020
1031000.0022
1041000.0022
105700.0016

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.

Spread the love

Recent Posts

Python Practice Questions & Solutions Day 5 of Learning Python for Data Science

Python Practice Questions & Solutions Day 5 of Learning Python for Data Science Welcome back…

5 days ago

Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing

Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing Understanding…

5 days ago

Python Practice Questions & Solutions Day 4 of Learning Python for Data Science

Python Practice Questions & Solutions Day 4 of Learning Python for Data Science Welcome back…

5 days ago

Day 4 of Learning Python for Data Science

Day 4 of Learning Python for Data Science Day 4 of Learning Python for Data…

5 days ago

Practice Questions and Answers for Day 3 of Learning Python for Data Science

Test your Python skills with these 20 practice questions and solutions from Day 3 of…

6 days ago

Day 3 of Learning Python for Data Science

Understanding Python’s conditional statements is essential for controlling the flow of a program. Today, we…

6 days ago