Blog

Display all months where sales exceeded the average monthly sales.

This question was asked in Interview at black rock. Read more

Company: BlackRock

CTC: 26LPA

SourceLinkedIn

SQL Interview Question

Q. Display all months where sales exceeded the average monthly sales

-- 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

Solution

Solution 1

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;

Solution 2

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
    );

Explanation

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:

MonthMonthlySales
January8000
February11000
March16000
April19000

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:

  • For January: 800 (January Sales) is not greater than 11666.67 (average), so it is excluded.
  • For February: 11000 (February Sales) is not greater than 11666.67 (average), so it is excluded.
  • For March: 16000 (March Sales) is greater than 11666.67 (average), so it remains in the result.
  • For April: 19000 (April Sales) is greater than 11666.67 (average), so it remains in the result

I hope this would have been helpful for you, consider sharing it with your friends. thank you.

Spread the love

Recent Posts

Mastering Pivot Table in Python: A Comprehensive Guide

Pivot tables are a powerful tool for summarizing and analyzing data, and Python’s Pandas library…

2 months ago

Data Science Interview Questions Section 3: SQL, Data Warehousing, and General Analytics Concepts

Welcome to Section 3 of our Data Science Interview Questions series! In this part, we…

2 months ago

Data Science Interview Questions Section 2: 25 Questions Designed To Deepen Your Understanding

Welcome back to our Data Science Interview Questions series! In the first section, we explored…

2 months ago

Data Science Questions Section 1: Data Visualization & BI Tools (Power BI, Tableau, etc.)

Data Science Questions in Section 1 focus on the essential concepts of Data Visualization and…

2 months ago

Optum Interview Questions: 30 Multiple Choice Questions (MCQs) with Answers

In this article, we’ve compiled 30 carefully selected multiple choice questions (MCQs) with answers to…

2 months ago

Day 15 of Learning Python for Data Science: Exploring Matplotlib Visualizations and EDA

Welcome to Day 15 of our Python for Data Science journey!On Day 15, we dived…

2 months ago