Question: Aggregate Window Function

Question

We have a database for a company that manages information about its employees, the product categories they handle, the products within those categories, and the sales made. The Employees table stores unique identifiers (EID), employee names, and ages. The ProductCategories table contains unique identifiers (CategoryID) for each category and assigns one employee to manage each. The Products table contains identifiers (PID) for each product, along with their respective categories, names, and prices. The Sales table keeps track of sales, recording a unique identifier (SalesID), the employee responsible, the product category, the sales amount, and the month of sale.

As a sales analyst, you need to quickly assess how active sales were during each month. Instead of manually counting transactions for each month, use an SQL window function to calculate the total number of sales for each month in the dataset. Here’s a task for you: Can you write an SQL query to retrieve a list of distinct months along with the total number of sales transactions that occurred in each month, using the columns: Month and Total Sales?

So, put on your database explorer hat and see if you can find the answer!

Expected output

The expected output is shown below:

Try it yourself

You can write a query in the following playground:

Hints

Below are some hints to help you understand these concepts better: