https://www.nowcoder.com/practice/280ed56ab3ee49a4

select 
book_id
,book_title
,feb_2023_borrows
,feb_2024_borrows
,jan_2024_borrows
,feb_2024_borrows-feb_2023_borrows as yoy_delta
,feb_2024_borrows-jan_2024_borrows as mom_delta
,round(coalesce(nort_2023*100/ifnull(pct_2023,0),0),2) as north_pct_2023
,round(coalesce(south_2023*100/ifnull(pct_2023,0),0),2) as south_pct_2023
,round(coalesce(east_2023*100/ifnull(pct_2023,0),0),2) as east_pct_2023
from (
select distinct t1.book_id
,book_title
,count(case when substr(borrow_date,1,7)='2023-02' then t2.record_id else null end) over(partition by t1.book_id) as feb_2023_borrows
,count(case when substr(borrow_date,1,7)='2024-02' then t2.record_id else null end) over(partition by t1.book_id) as feb_2024_borrows
,count(case when substr(borrow_date,1,7)='2024-01' then t2.record_id else null end) over(partition by t1.book_id) as jan_2024_borrows
,count(case when substr(borrow_date,1,4)='2023'and region='华北' then t2.record_id else null end) over(partition by t1.book_id) as nort_2023
,count(case when substr(borrow_date,1,4)='2023' then t2.record_id else null end) over(partition by t1.book_id) as pct_2023
,count(case when substr(borrow_date,1,4)='2023'and region='华南' then t2.record_id else null end) over(partition by t1.book_id) south_2023
,count(case when substr(borrow_date,1,4)='2023'and region='华东' then t2.record_id else null end) over(partition by t1.book_id) east_2023
from Books t1 
left join BorrowRecords t2 on t1.book_id =t2.book_id
left join Branches t3 on t2.branch_id =t3.branch_id 
) aa