MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE revenue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchased_est DATE NOT NULL,
    net_comp DECIMAL(5, 2) NOT NULL DEFAULT 0
);
INSERT INTO revenue (purchased_est, net_comp) VALUES
    ('2024-05-06', 0.01), ('2024-05-06', 0.01), ('2024-05-06', 0.01), ('2024-05-06', 0.01), ('2024-05-06', 0.01),
    ('2024-05-07', 0.01), ('2024-05-07', 0.01), ('2024-05-07', 0.01), ('2024-05-07', 0.01), ('2024-05-07', 0.01),
    ('2024-05-08', 0.01), ('2024-05-08', 0.01), ('2024-05-08', 0.01), ('2024-05-08', 0.01), ('2024-05-08', 0.01),
    ('2024-05-09', 0.01), ('2024-05-09', 0.01), ('2024-05-09', 0.01), ('2024-05-09', 0.01), ('2024-05-09', 0.01),
    ('2024-05-10', 0.01), ('2024-05-10', 0.01), ('2024-05-10', 0.01), ('2024-05-10', 0.01), ('2024-05-10', 0.01),
    ('2024-05-11', 0.01), ('2024-05-11', 0.01), ('2024-05-11', 0.01), ('2024-05-11', 0.01), ('2024-05-11', 0.01),
    ('2024-05-12', 0.01), ('2024-05-12', 0.01), ('2024-05-12', 0.01), ('2024-05-12', 0.01), ('2024-05-12', 0.01),

    ('2024-05-13', 0.01), ('2024-05-13', 0.01), ('2024-05-13', 0.01),
    ('2024-05-14', 0.01), ('2024-05-14', 0.01), ('2024-05-14', 0.01),
    ('2024-05-15', 0.01), ('2024-05-15', 0.01), ('2024-05-15', 0.01),
    ('2024-05-16', 0.01), ('2024-05-16', 0.01), ('2024-05-16', 0.01),
    ('2024-05-17', 0.01), ('2024-05-17', 0.01), ('2024-05-17', 0.01),
    ('2024-05-18', 0.01), ('2024-05-18', 0.01), ('2024-05-18', 0.01),
    ('2024-05-19', 0.01), ('2024-05-19', 0.01), ('2024-05-19', 0.01),

    ('2024-05-20', 0.01), ('2024-05-20', 0.01), ('2024-05-20', 0.01), ('2024-05-20', 0.01),
    ('2024-05-21', 0.01), ('2024-05-21', 0.01), ('2024-05-21', 0.01), ('2024-05-21', 0.01),
    ('2024-05-22', 0.01), ('2024-05-22', 0.01), ('2024-05-22', 0.01), ('2024-05-22', 0.01),
    ('2024-05-23', 0.01), ('2024-05-23', 0.01), ('2024-05-23', 0.01), ('2024-05-23', 0.01);

Records: 72  Duplicates: 0  Warnings: 0


-- using @CURRENT_DATE instead of CURRENT_DATE so the queries return a result
-- with the test data in future
SET @CURRENT_DATE = '2024-05-23';
-- aggregation and window steps combined
WITH before_this_week_with_prev AS ( 
    SELECT  
        YEAR(purchased_est) AS year_sales,
        WEEK(purchased_est, 1) AS week_num,
        COUNT(*) AS total_orders,
        IFNULL(SUM(net_comp), 0) AS total_nc,
        LAG(COUNT(*)) OVER w AS prev_orders,
        LAG(IFNULL(SUM(net_comp), 0)) OVER w AS prev_nc
    FROM revenue
    WHERE purchased_est < @CURRENT_DATE - INTERVAL (WEEKDAY(@CURRENT_DATE)) DAY
    GROUP BY YEAR(purchased_est), WEEK(purchased_est, 1)
    WINDOW w AS (ORDER BY YEAR(purchased_est), WEEK(purchased_est, 1))
),
this_week_and_last_with_prev AS ( 
    SELECT  
        YEAR(purchased_est) AS year_sales,
        WEEK(purchased_est, 1) AS week_num, 
        SUM(WEEKDAY(purchased_est) < WEEKDAY(@CURRENT_DATE)) AS total_orders,
        IFNULL(SUM(IF(WEEKDAY(purchased_est) < WEEKDAY(@CURRENT_DATE), net_comp, 0)), 0) AS total_nc,
        LAG(SUM(WEEKDAY(purchased_est) < WEEKDAY(@CURRENT_DATE))) OVER w AS prev_orders,
        LAG(IFNULL(SUM(IF(WEEKDAY(purchased_est) < WEEKDAY(@CURRENT_DATE), net_comp, 0)), 0)) OVER w AS prev_nc
    FROM revenue
    WHERE purchased_est < @CURRENT_DATE
    AND purchased_est >= @CURRENT_DATE - INTERVAL (WEEKDAY(@CURRENT_DATE) + 7) DAY
    GROUP BY YEAR(purchased_est), WEEK(purchased_est, 1)
    WINDOW w AS (ORDER BY YEAR(purchased_est), WEEK(purchased_est, 1))
),
all_with_prev AS (
    SELECT * FROM before_this_week_with_prev
    UNION ALL
    SELECT * FROM this_week_and_last_with_prev WHERE week_num = WEEK(@CURRENT_DATE, 1)
)

SELECT
    week_num, 
    year_sales,
    total_orders,
    prev_orders,
    total_nc,
    prev_nc,
    CONCAT(ROUND(((total_orders / prev_orders) - 1) * 100, 2), '%') AS 'Orders difference',
    CONCAT(ROUND(((total_nc / prev_nc) - 1) * 100, 2), '%') AS 'NC difference'
FROM all_with_prev
ORDER BY year_sales DESC, week_num DESC;


week_num
year_sales
total_orders
prev_orders
total_nc
prev_nc
Orders difference
NC difference


21
2024
12
9
0.12
0.09
33.33%
33.33%

20
2024
21
35
0.21
0.35
-40.00%
-40.00%

19
2024
35
null
0.35
null
null
null


-- aggregation and window steps separated
WITH before_this_week AS ( 
    SELECT  
        YEAR(purchased_est) AS year_sales,
        WEEK(purchased_est, 1) AS week_num,
        COUNT(*) AS total_orders,
        IFNULL(SUM(net_comp), 0) AS total_nc
    FROM revenue
    WHERE purchased_est < @CURRENT_DATE - INTERVAL (WEEKDAY(@CURRENT_DATE)) DAY
    GROUP BY YEAR(purchased_est), WEEK(purchased_est, 1)
),
before_this_week_with_prev AS (
    SELECT *,
        LAG(total_orders) OVER w AS prev_orders,
        LAG(total_nc) OVER w AS prev_nc
    FROM before_this_week
    WINDOW w AS (ORDER BY year_sales, week_num)
),
this_week_and_last AS ( 
    SELECT  
        YEAR(purchased_est) AS year_sales,
        WEEK(purchased_est, 1) AS week_num, 
        SUM(WEEKDAY(purchased_est) < WEEKDAY(@CURRENT_DATE)) AS total_orders,
        IFNULL(SUM(IF(WEEKDAY(purchased_est) < WEEKDAY(@CURRENT_DATE), net_comp, 0)), 0) AS total_nc
    FROM revenue
    WHERE purchased_est < @CURRENT_DATE
    AND purchased_est >= @CURRENT_DATE - INTERVAL (WEEKDAY(@CURRENT_DATE) + 7) DAY
    GROUP BY YEAR(purchased_est), WEEK(purchased_est, 1)
),
this_week_and_last_with_prev AS (
    SELECT *,
        LAG(total_orders) OVER w AS prev_orders,
        LAG(total_nc) OVER w AS prev_nc
    FROM this_week_and_last
    WINDOW w AS (ORDER BY year_sales, week_num)
),
all_with_prev AS (
    SELECT * FROM before_this_week_with_prev
    UNION ALL
    SELECT * FROM this_week_and_last_with_prev WHERE week_num = WEEK(@CURRENT_DATE, 1)
)

SELECT
    week_num, 
    year_sales,
    total_orders,
    prev_orders,
    total_nc,
    prev_nc,
    CONCAT(ROUND(((total_orders / prev_orders) - 1) * 100, 2), '%') AS 'Orders difference',
    CONCAT(ROUND(((total_nc / prev_nc) - 1) * 100, 2), '%') AS 'NC difference'
FROM all_with_prev
ORDER BY year_sales DESC, week_num DESC;


week_num
year_sales
total_orders
prev_orders
total_nc
prev_nc
Orders difference
NC difference


21
2024
12
9
0.12
0.09
33.33%
33.33%

20
2024
21
35
0.21
0.35
-40.00%
-40.00%

19
2024
35
null
0.35
null
null
null
week_num	year_sales	total_orders	prev_orders	total_nc	prev_nc	Orders difference	NC difference
21	2024	12	9	0.12	0.09	33.33%	33.33%
20	2024	21	35	0.21	0.35	-40.00%	-40.00%
19	2024	35	null	0.35	null	null	null