SQL Generation

For each dealership, rank employees by total sales amount in descending order. In the results I want to see dealership_name, employee_name , total_sales and sales_rank

SELECT d.name AS dealership_name, e.name AS employee_name, SUM(s.sale_price) AS total_sales, RANK() OVER (PARTITION BY d.branch_id ORDER BY SUM(s.sale_price) DESC) AS sales_rank FROM sales s JOIN employees e ON s.employee_id = e.employee_id JOIN dealerships d ON s.branch_id = d.branch_id GROUP BY d.name, e.name, d.branch_id;

WITH EmployeeSales AS (
    SELECT
        d.name AS dealership_name,
        e.name AS employee_name,
        e.branch_id,
        SUM(s.sale_price) AS total_sales
    FROM
        dealerships d
    JOIN
        employees e ON d.branch_id = e.branch_id
    JOIN
        sales s ON e.employee_id = s.employee_id
    GROUP BY
        d.name, e.name, e.branch_id
), RankedSales AS (
    SELECT
        dealership_name,
        employee_name,
        total_sales,
        RANK() OVER (PARTITION BY dealership_name ORDER BY total_sales DESC) AS sales_rank
    FROM
        EmployeeSales
)
SELECT
    dealership_name,
    employee_name,
    total_sales,
    sales_rank
FROM
    RankedSales
ORDER BY
    dealership_name, sales_rank;