with a as (
    select record_id,book_id,branch_id,date_format(borrow_date,'%Y%m') d_f,year(borrow_date) d_y  
    from BorrowRecords)


select  Books.book_id,
        book_title,   
        ifnull(sum(if(d_f=202302,1,0)),0) feb_2023_borrows,
        ifnull(sum(if(d_f=202402,1,0)),0) feb_2024_borrows,
        ifnull(sum(if(d_f=202401,1,0)),0) jan_2024_borrows,
        ifnull(sum(if(d_f=202402,1,0))-sum(if(d_f=202302,1,0)),0) yoy_delta,
        ifnull(sum(if(d_f=202402,1,0))-sum(if(d_f=202401,1,0)),0) mom_delta,
        ifnull(round(sum(if(d_y=2023 and region='华北',1,0))/sum(if(d_y=2023,1,0))*100,2),0) north_pct_2023,
        ifnull(round(sum(if(d_y=2023 and region='华南',1,0))/sum(if(d_y=2023,1,0))*100,2),0) south_pct_2023,
        ifnull(round(sum(if(d_y=2023 and region='华东',1,0))/sum(if(d_y=2023,1,0))*100,2),0) east_pct_2023
from a 
right join Books 
on a.book_id = Books.book_id
left join Branches 
on a.branch_id = Branches.branch_id
group by Books.book_id,book_title
order by Books.book_id