with borrows_data as(
    select
        book_id,
        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
    from(
        select  
            book_id,
            max(ifnull((case when dt = '2023-02' then borrows_cnt end), 0)) as feb_2023_borrows,
            max(ifnull((case when dt = '2024-02' then borrows_cnt end), 0)) as feb_2024_borrows,
            max(ifnull((case when dt = '2024-01' then borrows_cnt end), 0)) as jan_2024_borrows
        from (
            select
                book_id,
                date_format(borrow_date, '%Y-%m') as dt,
                count(*) as borrows_cnt
            from BorrowRecords
            where date_format(borrow_date, '%Y-%m') in ('2023-02', '2024-01', '2024-02')
            group by book_id, dt
        )as t1
        group by book_id
    )as t2
),
pct_data as (
    select
        t4.book_id,
        round(sum(case when region = '华北' then 1 else 0 end)*100/2023_cnt, 2) as north_pct_2023,
        round(sum(case when region = '华南' then 1 else 0 end)*100/2023_cnt, 2) as south_pct_2023,
        round(sum(case when region = '华东' then 1 else 0 end)*100/2023_cnt, 2) as east_pct_2023
    from (
        select
            book_id,
            region
        from BorrowRecords br 
        left join Branches b on br.branch_id=b.branch_id
        where year(borrow_date) = 2023
    )as t3
    right join (
        select
            bs.book_id,
            ifnull(count(*), 0) as 2023_cnt
        from BorrowRecords br 
        left join Branches b on br.branch_id=b.branch_id
        right join Books bs on br.book_id=bs.book_id
        where year(borrow_date) = 2023
        group by bs.book_id
    ) as t4 on t3.book_id =t4.book_id
    group by book_id
)
select
    bs.book_id,
    book_title,
    ifnull(feb_2023_borrows, 0) as feb_2023_borrows,
    ifnull(feb_2024_borrows, 0) as feb_2024_borrows,
    ifnull(jan_2024_borrows, 0) as jan_2024_borrows,
    ifnull(yoy_delta, 0) as yoy_delta,
    ifnull(mom_delta, 0) as mom_delta,
    round(ifnull(north_pct_2023, 0), 2) as north_pct_2023,
    round(ifnull(south_pct_2023, 0), 2) as south_pct_2023,
    round(ifnull(east_pct_2023, 0), 2) as east_pct_2023
from borrows_data bd 
left join pct_data pd on bd.book_id=pd.book_id
right join Books bs on bd.book_id=bs.book_id
order by bs.book_id asc, book_title asc