with t as (select book_id,book_title,branch_name,
date_format(borrow_date,'%Y-%m') as month,
year(borrow_date) as year
from Books bk
left join BorrowRecords br using(book_id)
left join Branches bc using(branch_id))

select book_id,book_title,
sum(if(month = '2023-02',1,0)) as feb_2023_borrows,
sum(if(month = '2024-02',1,0)) as feb_2024_borrows,
sum(if(month = '2024-01',1,0)) as jan_2024_borrows,
sum(if(month = '2024-02',1,0)) - sum(if(month = '2023-02',1,0)) as yoy_delta,
sum(if(month = '2024-02',1,0)) - sum(if(month = '2024-01',1,0)) as mom_delta,
ifnull(round(sum(if(branch_name = '北馆' and year = 2023,1,0))/ sum(if(year = 2023,1,0))*100,2),0) as north_pct_2023,
ifnull(round(sum(if(branch_name = '南馆' and year = 2023,1,0))/ sum(if(year = 2023,1,0))*100,2),0) as south_pct_2023,
ifnull(round(sum(if(branch_name = '东馆' and year = 2023,1,0))/ sum(if(year = 2023,1,0))*100,2),0) as east_pct_2023
from t
group by book_id,book_title
order by book_id,book_title