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,
| 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)
),
| 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 |