with region_temp as (  
select bk.book_id,book_title,coalesce(round(sum(case when region='华北' then  1 else 0 end )/count(record_id)*100,2),0)   as north_pct_2023,
coalesce(round(sum(case when region='华南' then  1 else 0 end )/count(record_id)*100,2),0)  as south_pct_2023,
coalesce(round(sum(case when region='华东' then  1 else 0 end )/count(record_id)*100,2),0)  as east_pct_2023
from   Books bk
left join  BorrowRecords br on br.book_id=bk.book_id and borrow_date between '2023-01-01' and '2023-12-31'
left join Branches bc on bc.branch_id=br.branch_id 
group by bk.book_id  
),
stime_temp  as (
select bk.book_id,bk.book_title,
sum(case when borrow_date between '2023-02-01' and '2023-02-28' then 1 else 0 end) as feb_2023_borrows,
sum(case when borrow_date between '2024-02-01' and '2024-02-29' then 1 else 0 end) as feb_2024_borrows,
sum(case when borrow_date between '2024-01-01' and '2024-01-31' then 1 else 0 end) as jan_2024_borrows
from   Books bk
left join BorrowRecords br  on br.book_id=bk.book_id
group by bk.book_id  
),
delta_temp as (
select bk.book_id, bk.book_title ,feb_2024_borrows-feb_2023_borrows as yoy_delta,
feb_2024_borrows-jan_2024_borrows as mom_delta,feb_2023_borrows,feb_2024_borrows,jan_2024_borrows
from  Books bk
left join   stime_temp st on st.book_id=bk.book_id
group by bk.book_id  
)
select  dt.book_id,dt.book_title,feb_2023_borrows,
feb_2024_borrows,jan_2024_borrows,yoy_delta,mom_delta,north_pct_2023,south_pct_2023,east_pct_2023
from delta_temp  dt
left join region_temp  rt on rt.book_id=dt.book_id
order by dt.book_id,book_title;