select t.book_id,book_title
,sum(case when date_format(borrow_date ,'%Y-%m') = '2023-02' then 1 else 0 end) as feb_2023_borrows
,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end) as feb_2024_borrows
,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-01' then 1 else 0 end) as jan_2024_borrows
,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end)-sum(case when date_format(borrow_date ,'%Y-%m') = '2023-02' then 1 else 0 end) as yoy_delta
,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end)-sum(case when date_format(borrow_date ,'%Y-%m') = '2024-01' then 1 else 0 end) as mom_delta
,round(ifnull(sum(case when region='华北' and year(borrow_date) =2023 then 1 else 0 end)/sum(case when year(borrow_date) =2023 then 1 else 0 end)*100,0),2) as north_pct_2023
,round(ifnull(sum(case when region='华南' and year(borrow_date) =2023 then 1 else 0 end)/sum(case when year(borrow_date) =2023 then 1 else 0 end)*100,0),2) as south_pct_2023
,round(ifnull(sum(case when region='华东' and year(borrow_date) =2023 then 1 else 0 end)/sum(case when year(borrow_date) =2023 then 1 else 0 end)*100,0),2) as east_pct_2023
from Books t
left join BorrowRecords t1 on t.book_id=t1.book_id
left join Branches t2 on t1.branch_id=t2.branch_id
group by t.book_id,book_title
order by book_id,book_title