add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table books(id int,book_name varchar(20));
insert into books values(1,'Book 1'),(2,'Book 2'),(3,'Book 3');
create table users(id int,user_name varchar(20));
insert into users values(10,'User 10'),(20,'User 20'),(30,'User 40'),(30,'User 40');
create table book_user(user_id int,book_id int,start_page int,end_page int);
insert into book_user values
(10,1,10,30)
,(20,1, 2,25)
,(30,1, 2,26)
,(30,1, 10,31)
,(40,1, 33,40)
,(10,2,40,50)
,(30,2, 1,10)
,(40,2, 10,11)
,(20,2, 11,14)
,(10,3,1,10)
,(20,3,20,40)
;
select * from users;
select * from books;
select * from book_user;

Records: 3  Duplicates: 0  Warnings: 0

Records: 4  Duplicates: 0  Warnings: 0

Records: 11  Duplicates: 0  Warnings: 0
id user_name
10 User 10
20 User 20
30 User 40
30 User 40
id book_name
1 Book 1
2 Book 2
3 Book 3
user_id book_id start_page end_page
10 1 10 30
20 1 2 25
30 1 2 26
30 1 10 31
40 1 33 40
10 2 40 50
30 2 1 10
40 2 10 11
20 2 11 14
10 3 1 10
20 3 20 40
with recursive t as(
select book_id,start_page,max(end_page)end_page
,row_number()over(partition by book_id order by start_page) rn
from book_user
group by book_id,start_page
)
,r as(
select 0 lvl,bu.book_id,bu.start_page,bu.end_page,bu.rn
from t bu
where not exists(select 1 from t bu2
where bu2.book_id=bu.book_id and bu2.rn<bu.rn
and bu.start_page between bu2.start_page and bu2.end_page)
union all
select lvl+1,r.book_id,r.start_page,t.end_page,t.rn
from r inner join t on t.book_id=r.book_id and t.rn>r.rn
and r.end_page between t.start_page and r.end_page
)
select book_id,sum(end_page-start_page+1) total_pages
from (select book_id,start_page,max(end_page) end_page
from r
group by book_id,start_page
) gr
group by book_id
book_id total_pages
1 38
2 25
3 31
with recursive t as(
select book_id,start_page,max(end_page)end_page
,row_number()over(partition by book_id order by start_page) rn
,concat(start_page,':',group_concat(end_page),'') range1
from book_user
group by book_id,start_page
)
,r as(
select 0 lvl,bu.book_id,bu.start_page,bu.end_page,bu.rn
,range1 as path
from t bu
where not exists(select 1 from t bu2
where bu2.book_id=bu.book_id and bu2.rn<bu.rn
and bu.start_page between bu2.start_page and bu2.end_page)
union all
select lvl+1,r.book_id,r.start_page,t.end_page,t.rn
,concat(path,':',t.end_page) as path
from r inner join t on t.book_id=r.book_id and t.rn>r.rn
and r.end_page between t.start_page and r.end_page
)
-- select * from r
-- order by book_id,lvl,start_page;
select book_id,sum(end_page-start_page+1) total_pages
,group_concat(concat(start_page,'-',path) ) path
from (select book_id,start_page,max(end_page) end_page
,group_concat(path) path
from r
group by book_id,start_page
) gr
group by book_id
book_id total_pages path
1 38 2-2:25,26,2:25,26:31,33-33:40
2 25 1-1:10,1:10:11,1:10:11:14,40-40:50
3 31 1-1:10,20-20:40
select 'book_id',max(books.book_name) as book_name
,SUM(end_page - start_page) AS num_of_read_pages
FROM book_user
JOIN books ON books.id=book_user.book_id
GROUP BY book_id
ORDER BY num_of_read_pages DESC;
book_id book_name num_of_read_pages
book_id Book 1 95
book_id Book 3 29
book_id Book 2 23