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.
Python Practice Questions & Solutions Day 5 of Learning Python for Data Science Welcome back…
Day 5 of Learning Python for Data Science: Data Types, Typecasting, Indexing, and Slicing Understanding…
Python Practice Questions & Solutions Day 4 of Learning Python for Data Science Welcome back…
Day 4 of Learning Python for Data Science Day 4 of Learning Python for Data…
Test your Python skills with these 20 practice questions and solutions from Day 3 of…
Understanding Python’s conditional statements is essential for controlling the flow of a program. Today, we…