Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Aggregate Window Function

Explore how to apply aggregate window functions to analyze sales data efficiently. Understand how to use SELECT queries, aliases, ORDER BY clauses, and window partitions. Learn alternative approaches like GROUP BY and CTEs to solve intermediate SQL interview problems.

We'll cover the following...

Solution

The solution is given below:

MySQL
/*Query to find the count of sales for each month*/
SELECT   DISTINCT Month,
         COUNT(*) OVER (PARTITION BY Month) AS 'Total Sales'
FROM     Sales
ORDER BY FIELD(Month, "January", "February", "March", "April", "May");

Code explanation

The explanation of the solution code is given below:

  • Lines 2–3: The SELECT statement selects the columns Month and Total sales. The Total sales column retrieves the count of the product in each category using the aggregate window function. We use AS to set an alias for the columns.

  • Line 4: The data is retrieved from the Sales table.

  • Line 5: The ORDER BY clause sorts the results by the Month column in custom order of months using the FIELD() function.

Recalling relevant concepts

We have covered the following concepts in this question:

  • Selective columns ...