MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.6.9-MariaDB-1:10.6.9+maria~deb11


create table units (
  id int not null primary key,
  product_id int not null,
  price int not null,
  acquired_at datetime not null,
  released_at datetime
);

insert into units values
(1, 1, 12, 20230101, null),
(2, 1, 35, 20230101, 20230601),
(3, 1, 47, 20230101, 20230201),
(4, 1, 56, 20240101, 20240201),
(5, 1, 78, 20240101, 20240228),
(6, 1, 89, 20240101, null);

create table products (
  id int not null primary key,
  turnover decimal(12,3)
);

insert into products (id) values (1);

Records: 6  Duplicates: 0  Warnings: 0


select * from products;


id
turnover


1
null


select * from units;


id
product_id
price
acquired_at
released_at


1
1
12
2023-01-01 00:00:00
null

2
1
35
2023-01-01 00:00:00
2023-06-01 00:00:00

3
1
47
2023-01-01 00:00:00
2023-02-01 00:00:00

4
1
56
2024-01-01 00:00:00
2024-02-01 00:00:00

5
1
78
2024-01-01 00:00:00
2024-02-28 00:00:00

6
1
89
2024-01-01 00:00:00
null


select id, acquired_at, released_at,
  product_id,
  case when date(released_at) > curdate() - interval 1 year and date(released_at) <= curdate() then price end sales,
  greatest(curdate() - interval 1 year, date(acquired_at)) after_date,
  least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)) before_date,
  greatest(0, datediff(
    least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
    greatest(curdate() - interval 1 year, date(acquired_at))
  ) - 1) inventory_days,
  price,
  greatest(0, datediff(
    least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
    greatest(curdate() - interval 1 year, date(acquired_at))
  ) - 1) * price / datediff(curdate(), curdate() - interval 1 year) avg_inventory
from units


id
acquired_at
released_at
product_id
sales
after_date
before_date
inventory_days
price
avg_inventory


1
2023-01-01 00:00:00
null
1
null
2023-02-21
2024-02-22
365
12
12.0000

2
2023-01-01 00:00:00
2023-06-01 00:00:00
1
35
2023-02-21
2023-06-01
99
35
9.4932

3
2023-01-01 00:00:00
2023-02-01 00:00:00
1
null
2023-02-21
2023-02-01
0
47
0.0000

4
2024-01-01 00:00:00
2024-02-01 00:00:00
1
56
2024-01-01
2024-02-01
30
56
4.6027

5
2024-01-01 00:00:00
2024-02-28 00:00:00
1
null
2024-01-01
2024-02-22
51
78
10.8986

6
2024-01-01 00:00:00
null
1
null
2024-01-01
2024-02-22
51
89
12.4356


  select
    product_id,
    sum(case when date(released_at) > curdate() - interval 1 year and date(released_at) <= curdate() then price end) sales,
    sum(greatest(0, datediff(
      least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
      greatest(curdate() - interval 1 year, date(acquired_at))
    ) - 1) * price) / datediff(curdate(), curdate() - interval 1 year) avg_inventory
  from units
  group by product_id


product_id
sales
avg_inventory


1
91
49.4301


update products
join (
  select
    product_id,
    sum(case when date(released_at) > curdate() - interval 1 year and date(released_at) <= curdate() then price end) sales,
    sum(greatest(0, datediff(
      least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
      greatest(curdate() - interval 1 year, date(acquired_at))
    ) - 1) * price) / datediff(curdate(), curdate() - interval 1 year) avg_inventory
  from units
  group by product_id
  having avg_inventory
) summary on id = product_id
set turnover = sales / avg_inventory

Rows matched: 1  Changed: 1  Warnings: 2


select * from products


id
turnover


1
1.841
id	product_id	price	acquired_at	released_at
1	1	12	2023-01-01 00:00:00	null
2	1	35	2023-01-01 00:00:00	2023-06-01 00:00:00
3	1	47	2023-01-01 00:00:00	2023-02-01 00:00:00
4	1	56	2024-01-01 00:00:00	2024-02-01 00:00:00
5	1	78	2024-01-01 00:00:00	2024-02-28 00:00:00
6	1	89	2024-01-01 00:00:00	null