mysql - Predicting rate of inventory depletion based on previous sales -

mysql - Predicting rate of inventory depletion based on previous sales -

suppose have order_items table tracks sales of items:

create table `items` ( `id` int(10) unsigned not null auto_increment, `stock` int(11) not null, `price` decimal(8,2) not null, `created_at` timestamp not null default '0000-00-00 00:00:00', primary key (`id`) ) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_unicode_ci; create table `order_items` ( `id` int(10) unsigned not null auto_increment, `order_id` int(10) unsigned not null, `item_id` int(10) unsigned default null, `quantity` int(11) not null, `price` decimal(8,2) not null, `created_at` timestamp not null default '0000-00-00 00:00:00', primary key (`id`), key `order_items_order_id_index` (`order_id`), key `order_items_item_id_index` (`item_id`) ) engine=innodb auto_increment=1 default charset=utf8 collate=utf8_unicode_ci;

how create query can homecoming estimated rate of inventory quantity depletion based on past sales particular time period?

for instance, if item_id 1 sells half stock in 2 weeks (based on created_at timestamp), how set query tell me in 2 weeks there should no quantity remaining?

would possible grouping depletion rates time spans in single query?

here fiddle this: http://sqlfiddle.com/#!2/c568f

here's less basic query gets items, current stock, how much they've sold in lastly 2 weeks , how much stock there'll after 2 more weeks if keeps up:

select i.id, i.stock current_stock, coalesce(sum(o.quantity), 0) orders_last_2_weeks, i.stock - coalesce(sum(o.quantity), 0) stock_after_2_weeks items left outer bring together order_items o on o.item_id = i.id , o.created_at > now() - interval 2 week grouping i.id

http://sqlfiddle.com/#!2/08754/6

i had tweak dates on fiddle none of orders ending in lastly 2 weeks.

mysql sql prediction