FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

create table orders (id int auto_increment primary key, created_at datetime);
create table orders_products (order_id int, final_price numeric);
insert into orders (created_at)
with recursive
y as (select 2019 y union all select y + 1 from y where y < 2025),
d as
(
    select cast(concat(y, '-12-31') as date) d from y
    union all
    select case when dayofyear(d) = 358 then cast(concat(year(d), '-01-07') as date) else d - interval 1 day end
    from d
    where dayofyear(d) > 1
)
select d from d;
insert into orders_products
    select id, 1.0 from orders;

SELECT
    YEAR (orders.created_at) AS year,
    (DAYOFYEAR (orders.created_at) - 1) DIV 7 + 1 AS week,
    min(orders.created_at) as created_date_min,
    max(orders.created_at) as created_date_max,
    sum(
        orders_products.final_price
    ) AS total
FROM
    `orders_products`
INNER JOIN `orders` ON `orders_products`.`order_id` = `orders`.`id`
GROUP BY
    `year`,
    `week`
ORDER BY
    `year` ASC,
    `week` ASC;

SELECT
    YEAR (orders.created_at) AS year,
    LEAST(52, (DAYOFYEAR (orders.created_at) - 1) DIV 7 + 1) AS week,
    min(orders.created_at) as created_date_min,
    max(orders.created_at) as created_date_max,
    sum(
        orders_products.final_price
    ) AS total
FROM
    `orders_products`
INNER JOIN `orders` ON `orders_products`.`order_id` = `orders`.`id`
GROUP BY
    `year`,
    `week`
ORDER BY
    `year` ASC,
    `week` ASC;

year	week	created_date_min	created_date_max	total
2019	1	2019-01-01 00:00:00	2019-01-07 00:00:00	7
2019	52	2019-12-24 00:00:00	2019-12-30 00:00:00	7
2019	53	2019-12-31 00:00:00	2019-12-31 00:00:00	1
2020	1	2020-01-01 00:00:00	2020-01-07 00:00:00	7
2020	52	2020-12-23 00:00:00	2020-12-29 00:00:00	7
2020	53	2020-12-30 00:00:00	2020-12-31 00:00:00	2
2021	1	2021-01-01 00:00:00	2021-01-07 00:00:00	7
2021	52	2021-12-24 00:00:00	2021-12-30 00:00:00	7
2021	53	2021-12-31 00:00:00	2021-12-31 00:00:00	1
2022	1	2022-01-01 00:00:00	2022-01-07 00:00:00	7
2022	52	2022-12-24 00:00:00	2022-12-30 00:00:00	7
2022	53	2022-12-31 00:00:00	2022-12-31 00:00:00	1
2023	1	2023-01-01 00:00:00	2023-01-07 00:00:00	7
2023	52	2023-12-24 00:00:00	2023-12-30 00:00:00	7
2023	53	2023-12-31 00:00:00	2023-12-31 00:00:00	1
2024	1	2024-01-01 00:00:00	2024-01-07 00:00:00	7
2024	52	2024-12-23 00:00:00	2024-12-29 00:00:00	7
2024	53	2024-12-30 00:00:00	2024-12-31 00:00:00	2
2025	1	2025-01-01 00:00:00	2025-01-07 00:00:00	7
2025	52	2025-12-24 00:00:00	2025-12-30 00:00:00	7
2025	53	2025-12-31 00:00:00	2025-12-31 00:00:00	1

year	week	created_date_min	created_date_max	total
2019	1	2019-01-01 00:00:00	2019-01-07 00:00:00	7
2019	52	2019-12-24 00:00:00	2019-12-31 00:00:00	8
2020	1	2020-01-01 00:00:00	2020-01-07 00:00:00	7
2020	52	2020-12-23 00:00:00	2020-12-31 00:00:00	9
2021	1	2021-01-01 00:00:00	2021-01-07 00:00:00	7
2021	52	2021-12-24 00:00:00	2021-12-31 00:00:00	8
2022	1	2022-01-01 00:00:00	2022-01-07 00:00:00	7
2022	52	2022-12-24 00:00:00	2022-12-31 00:00:00	8
2023	1	2023-01-01 00:00:00	2023-01-07 00:00:00	7
2023	52	2023-12-24 00:00:00	2023-12-31 00:00:00	8
2024	1	2024-01-01 00:00:00	2024-01-07 00:00:00	7
2024	52	2024-12-23 00:00:00	2024-12-31 00:00:00	9
2025	1	2025-01-01 00:00:00	2025-01-07 00:00:00	7
2025	52	2025-12-24 00:00:00	2025-12-31 00:00:00	8