MySQL 8.0

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

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

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

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

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