SQL Generation

Between May and September 2025, how many Volvo vehicles were sold during the first week of each month, grouped by dealership?

WITH filtered_sales AS ( SELECT s.*, v.make, d.name AS dealership_name FROM sales s JOIN vehicles v ON s.vehicle_id = v.vehicle_id JOIN dealerships d ON s.branch_id = d.branch_id WHERE v.make = 'Volvo' AND s.sale_date >= DATE '2025-05-01' AND s.sale_date < DATE '2025-10-01' AND EXTRACT(DAY FROM s.sale_date) <= 7 ) SELECT dealership_name, DATE_TRUNC('month', sale_date) AS month, COUNT(*) AS volvo_sales FROM filtered_sales GROUP BY dealership_name, DATE_TRUNC('month', sale_date) ORDER BY month, dealership_name;

WITH first_week_sales AS (
    SELECT
        s.branch_id,
        d.name AS dealership_name,
        DATE_TRUNC('month', s.sale_date) AS month,
        COUNT(*) AS sales_count
    FROM
        sales s
    JOIN
        vehicles v ON s.vehicle_id = v.vehicle_id
    JOIN
        dealerships d ON s.branch_id = d.branch_id
    WHERE
        v.make = 'Volvo'
        AND s.sale_date BETWEEN '2025-05-01' AND '2025-09-30'
        AND EXTRACT(DAY FROM s.sale_date) <= 7
    GROUP BY
        s.branch_id, d.name, DATE_TRUNC('month', s.sale_date)
)

SELECT
    dealership_name,
    TO_CHAR(month, 'Month YYYY') AS month,
    SUM(sales_count) AS total_volvo_sales_first_week
FROM
    first_week_sales
GROUP BY
    dealership_name, month
ORDER BY
    month, dealership_name;