This question was asked in Interview at black rock. Read more
Company: BlackRock
CTC: 26LPA
Source: LinkedIn
-- Create the Sales table
CREATE TABLE Sales (
SaleID INT PRIMARY KEY,
Month VARCHAR(7),
Sales DECIMAL(10, 2)
);
-- Insert sample data into Sales table
INSERT INTO Sales (SaleID, Month, Sales) VALUES
(1, '2024-01', 5000.00),
(2, '2024-01', 3000.00),
(3, '2024-02', 4000.00),
(4, '2024-02', 7000.00),
(5, '2024-03', 8000.00),
(6, '2024-03', 2000.00),
(7, '2024-03', 6000.00),
(8, '2024-04', 9000.00),
(9, '2024-04', 10000.00),
(10, '2024-05', 3000.00);
see this code on db-fiddle
WITH MonthlySales AS (
SELECT
Month,
SUM(Sales) AS MonthlySales
FROM
Sales
GROUP BY
Month
),
AverageSales AS (
SELECT
AVG(MonthlySales) AS AvgMonthlySales
FROM
MonthlySales
)
SELECT
ms.Month,
ms.MonthlySales
FROM
MonthlySales ms
JOIN
AverageSales a
ON
ms.MonthlySales > a.AvgMonthlySales;
SELECT
Month,
SUM(Sales) AS MonthlySales
FROM
Sales
GROUP BY
Month
HAVING
SUM(Sales) > (
SELECT AVG(MonthlySales)
FROM (
SELECT
SUM(Sales) AS MonthlySales
FROM
Sales
GROUP BY
Month
) AS MonthlyAverages
);
Although there are multiple ways to solve this problem, using the CTE method stands out as the most efficient, clear, and cost-effective approach.
We started by calculating the monthly sales, which we did by using the SUM()
function on the sales column and grouping the data by Month
. The result for this query looks like this:
Month | MonthlySales |
---|---|
January | 8000 |
February | 11000 |
March | 16000 |
April | 19000 |
Next, we calculated the average monthly sales using the AVG()
function on MonthlySales
. This gave us a single value representing the average monthly sales:
AvgMonthlySales |
---|
11666.67 |
Finally, we joined the AverageSales with the MonthlySales table, applying the condition that we should only keep the rows where MonthlySales > AvgMonthlySales
. This resulted in:
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 15 of our Python for Data Science journey!On Day 15, we dived…
Welcome to Day 14 of our Python for Data Science journey! Today, we explored Seaborn,…
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…