sqlintermediate15 minutes

Create a Sales Performance Summary Report Using Window Functions

Build a SQL query that summarizes monthly sales performance per salesperson, including cumulative sales, ranking within each month, and percentage contribution to total sales. This intermediate mini-project involves data modeling concepts such as aggregation, window functions, and joining tables.

Challenge prompt

You are given two tables: `sales` and `salespeople`. `sales` table columns: - sale_id (INT): Unique sale identifier - salesperson_id (INT): ID of the salesperson who made the sale - sale_date (DATE): Date of the sale - amount (DECIMAL): Sale amount `salespeople` table columns: - salesperson_id (INT): Unique salesperson identifier - name (VARCHAR): Salesperson's full name Write a SQL query that returns a monthly summary report for each salesperson that includes: - Salesperson name - Year and Month (e.g., 2023-04) - Total sales amount for that month - Cumulative sales amount for that salesperson up to and including that month - Rank of the salesperson's total sales in that month compared to others (1 is highest) - Percentage contribution of the salesperson's monthly sales to the total sales made by all salespeople in that same month Sort the results by year-month ascending and by salesperson name ascending.

Guidance

• Use the DATE_FORMAT (MySQL) or TO_CHAR (PostgreSQL) function to extract year and month from sale_date.
• Use window functions such as RANK(), SUM() OVER(), and SUM() FILTER() or subqueries to compute rankings, cumulative sums, and total monthly sales.
• Join sales with salespeople on salesperson_id to get salesperson names.

Hints

• Consider using a common table expression (CTE) or subquery to group sales by salesperson and month before applying window functions.
• Remember that ranking functions reset by partition; partition by year-month for monthly rank and partition by salesperson_id ordered by year-month for cumulative sum.
• To calculate percentage contribution, divide the salesperson's monthly total sales by the grand total of sales in the same month.

Starter code

WITH monthly_sales AS (
  SELECT
    salesperson_id,
    TO_CHAR(sale_date, 'YYYY-MM') AS year_month,
    SUM(amount) AS monthly_total
  FROM sales
  GROUP BY salesperson_id, TO_CHAR(sale_date, 'YYYY-MM')
)
SELECT
  sp.name,
  ms.year_month,
  ms.monthly_total,
  -- cumulative sum, rank, and percentage calculation to be filled
FROM monthly_sales ms
JOIN salespeople sp ON ms.salesperson_id = sp.salesperson_id
ORDER BY ms.year_month, sp.name;

Expected output

name | year_month | monthly_total | cumulative_sales | rank | percentage_of_monthly_total ----------|------------|---------------|------------------|------|-------------------------- Alice | 2023-01 | 15000 | 15000 | 1 | 0.375 Bob | 2023-01 | 10000 | 10000 | 2 | 0.25 Alice | 2023-02 | 20000 | 35000 | 1 | 0.5 Bob | 2023-02 | 10000 | 20000 | 2 | 0.25 ...

Core concepts

SQL window functionsaggregation and groupingdata modelingjoins

Challenge a Friend

Send this duel to someone else and see if they can solve it.