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

Day 13 of Learning Python for Data Science: Mastering Pivot, Apply and RegEx

Welcome to Day 13 of Learning Python for Data Science! Today, we’re focusing on three…

6 days ago

Practice day 12 of Learning Python for Data Science

Test your understanding of Python Data Structure, which we learned in our previous lesson of…

2 weeks ago

Day 12 of Learning Python for Data Science – Pandas

Welcome to Day 12 of Learning Python for Data Science. Today, we’ll dive into Pandas,…

2 weeks ago

Day 10 Of Learning Python for Data Science – NumPy Array In Python

NumPy Array in Python is a powerful library for numerical computing in Python. It provides…

2 weeks ago

Day 9 of Learning Python for Data Science – Queries Related To Functions In Python

Welcome to Day 9 of Learning Python for Data Science. Today we will explore comprehensions,…

2 weeks ago

Practice day 8 of Learning Python for Data Science

Test your understanding of Python Data Structure, which we learned in our previous lesson of…

2 weeks ago