CTE and Windows Function to Fetch Top Payment Types in 2017 Sales [SQL Server]

Welcome to Excel Jet Consult blog post. In this blog post, we will explore how to use the CTE and Windows Function to Fetch Top Payment Types in 2017 Sales [SQL Server]. Let’s get started.

With a simple SELECT * FROM factSalesTable, we fetched all the records in the table as seen below

CTE and Windows Function to Fetch Top Payment Types in 2017 Sales

To fetch top payment type in 2017 sales using the Common Table Expression and window function, we are going to write and execute this query:

WITH RankedPaymentTypes AS (
    SELECT [Payment Type], SUM(Total) AS TotalSales,
           RANK() OVER (ORDER BY SUM(Total) DESC) AS PaymentRank
    FROM factSalesTable
    WHERE Year = 2017
    GROUP BY [Payment Type]
)
SELECT [Payment Type], TotalSales
FROM RankedPaymentTypes
WHERE PaymentRank <= 3;

Based on the above query, we define a common table expression (CTE) named “RankedPaymentTypes” to simplify our query.
Within the CTE, we select the “[Payment Type]” column, calculate the total sales for each payment type, and rank them in descending order based on total sales using the RANK() window function.
In the final SELECT statement, we retrieve the “[Payment Type]” and “TotalSales” columns from the “RankedPaymentTypes” CTE.
WHERE PaymentRank <= 3:

Finally, we filter the results to include only payment types with a rank of 3 or lower. This ensures that we focus on the top three payment types by sales in 2017.

From the screenshot below, we have the TotalSales by PaymentType ranked

The above query is suitable in identify the most popular payment types to streamline payment processing systems, ensuring efficient and secure transactions for businesses