WITH CustomerSales AS (
SELECT
c.CustomerKey,
c.Name,
SUM(s.SalesAmount) AS TotalSales
FROM Customer c
JOIN Sales s ON c.CustomerKey = s.CustomerKey
GROUP BY c.CustomerKey, c.Name
),
TopCustomers AS (
SELECT
CustomerKey,
Name,
TotalSales,
RANK() OVER (ORDER BY TotalSales DESC) AS Ranking
FROM CustomerSales
)
SELECT *
FROM TopCustomers
ORDER BY Ranking
LIMIT 10;
EVALUATE
VAR TopCustomers =
TOPN (
10,
SUMMARIZE (
Customer,
Customer[CustomerKey],
Customer[Name],
"TotalSales", Sales[Sales Amount]
),
[TotalSales], DESC
)
RETURN
ADDCOLUMNS (
TopCustomers,
"Ranking", RANKX ( TopCustomers, [TotalSales],, DESC )
)
ORDER BY [Ranking]
import pandas as pd
# Assuming df_sales and df_customers are the Sales and Customer DataFrames
df_sales_customers = pd.merge(df_sales, df_customers, on='CustomerKey')
# Group by CustomerKey and Name, then calculate the total sales
df_totalsales = df_sales_customers.groupby(['CustomerKey', 'Name'])['SalesAmount'].sum().reset_index()
# Sort by TotalSales in descending order and assign ranking
df_totalsales['Ranking'] = df_totalsales['SalesAmount'].rank(ascending=False, method='dense')
# Sort by ranking and select the top 10 customers
df_top_customers = df_totalsales.sort_values(by='Ranking').head(10)
| 19099 | Doe, John | 298045.76 | 1 |
| 19088 | Jane, Smith | 295386.68 | 2 |
| 19087 | Mario, Xu | 268858.88 | 3 |
SUMMARIZE: Summarizes the total sales for each customer using SUM(Sales[Sales Amount]). TOPN: Filters out the top 10 customers based on their total sales. RANKX: Now ranks customers within the TopCustomers table, ensuring ranking is based on the filtered results. ADDCOLUMNS: Adds the ranking as a new column to the result set.
Leave a Comment