SQLite 3.39

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


sqlite_version()


3.39.1


create table books (
book_key int not null,
book int not null
);
✓
create table purchases (
purchase_key int not null,
date_key int not null,
book_key int not null,  
cost real not null
);
✓
create table orders (
order_key int not null,
date_key int not null, 
book_key int not null,
price real not null
);
✓
create table dates (
date_key int not null,
period_key int not null
);
✓
insert into books (book_key, book) values 
(1, 'A'),
(2, 'B');
✓
select * from books;


book_key
book


1
A

2
B


insert into purchases (purchase_key, date_key, book_key, cost) values 
(1, 20231101, 1, 900),
(2, 20231201, 2, 750);
✓
select * from purchases;


purchase_key
date_key
book_key
cost


1
20231101
1
900

2
20231201
2
750


insert into orders (order_key, date_key, book_key, price) values 
(1, 20240101, 1, 1080.0),
(2, 20240101, 2, 900.0);
✓
select * from orders;


order_key
date_key
book_key
price


1
20240101
1
1080

2
20240101
2
900


insert into dates (date_key, period_key) values
(20231101, 202311),
(20231201, 202312),
(20240101, 202401)
;
✓
select * from dates;


date_key
period_key


20231101
202311

20231201
202312

20240101
202401


create view stocks as 
select
  t1.date_key,
  t1.book_key,
  pur.buys,
  ord.sells,
  sum(pur.buys) over (partition by t1.book_key order by t1.date_key, t1.book_key) as cum_buys,
  sum(ord.sells) over (partition by t1.book_key order by t1.date_key, t1.book_key) as cum_sells,
  ifnull(sum(pur.buys) over (partition by t1.book_key order by t1.date_key, t1.book_key), 0)
  - ifnull(sum(ord.sells) over (partition by t1.book_key order by t1.date_key, t1.book_key), 0) as delta 
from (
select
  d.date_key,
  b.book_key
from dates d
cross join books b
) t1
left join (select o.date_key, o.book_key, count(o.book_key) as sells 
  from orders o group by o.date_key, o.book_key) as ord
  on (t1.date_key=ord.date_key and t1.book_key=ord.book_key)
left join (select p.date_key, p.book_key, count(p.book_key) as buys 
  from purchases p group by p.date_key, p.book_key) as pur
  on (t1.date_key=pur.date_key and t1.book_key=pur.book_key)
;
✓
select 
  s.date_key,
  b.book,
  s.buys,
  s.sells,
  s.cum_buys,
  s.cum_sells,
  s.delta
from stocks s
  left join books b on (b.book_key=s.book_key)
;


date_key
book
buys
sells
cum_buys
cum_sells
delta


20231101
A
1
null
1
null
1

20231201
A
null
null
1
null
1

20240101
A
null
1
1
1
0

20231101
B
null
null
null
null
0

20231201
B
1
null
1
null
1

20240101
B
null
1
1
1
0


select
  t1.period_key,
  t1.book,
  max(t1.delta)
from (
select 
  s.date_key,
  d.period_key,
  b.book,
  s.buys,
  s.sells,
  s.cum_buys,
  s.cum_sells,
  s.delta
from stocks s
  left join dates d on (d.date_key=s.date_key)
  left join books b on (b.book_key=s.book_key)
) t1
group by
  t1.period_key,
  t1.book
;


period_key
book
max(t1.delta)


202311
A
1

202311
B
0

202312
A
1

202312
B
1

202401
A
0

202401
B
0
date_key	book	buys	sells	cum_buys	cum_sells	delta
20231101	A	1	null	1	null	1
20231201	A	null	null	1	null	1
20240101	A	null	1	1	1	0
20231101	B	null	null	null	null	0
20231201	B	1	null	1	null	1
20240101	B	null	1	1	1	0