with t as
(select 
t.book_id,
t.book_title,
sum(case when date_format(t1.borrow_date,'%Y-%m')='2023-02' then 1 else 0 end) feb_2023_borrows,
sum(case when date_format(t1.borrow_date,'%Y-%m')='2024-02' then 1 else 0 end) feb_2024_borrows,
sum(case when date_format(t1.borrow_date,'%Y-%m')='2024-01' then 1 else 0 end) jan_2024_borrows,
sum(case when date_format(t1.borrow_date,'%Y-%m')='2024-02' then 1 else 0 end)-sum(case when date_format(t1.borrow_date,'%Y-%m')='2023-02' then 1 else 0 end) as yoy_delta,
sum(case when date_format(t1.borrow_date,'%Y-%m')='2024-02' then 1 else 0 end)-sum(case when date_format(t1.borrow_date,'%Y-%m')='2024-01' then 1 else 0 end) as mom_delta,
sum(case when year(t1.borrow_date)='2023' and t2.region='华北' then 1 else 0 end)/sum(case when year(t1.borrow_date)='2023' then 1 else 0 end) as north_pct_2023,
sum(case when year(t1.borrow_date)='2023' and t2.region='华南' then 1 else 0 end)/sum(case when year(t1.borrow_date)='2023' then 1 else 0 end) as south_pct_2023,
sum(case when year(t1.borrow_date)='2023' and t2.region='华东' then 1 else 0 end)/sum(case when year(t1.borrow_date)='2023' then 1 else 0 end) 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,
t.book_title
)
select 
book_id,
book_title,
feb_2023_borrows,
feb_2024_borrows,
jan_2024_borrows,
yoy_delta,
mom_delta,
round(coalesce(north_pct_2023 * 100,0),2) north_pct_2023,
round(coalesce(south_pct_2023 * 100,0),2) south_pct_2023,
round(coalesce(east_pct_2023 * 100,0),2) east_pct_2023
from t
order by book_id,
book_title;