题解 | 统计借阅量

with borrows 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
        ,sum(case when year(borrow_date)=2023 and month(borrow_date)=2 then 1 else 0 
        end) as feb_2023_borrows
        ,sum(case when year(borrow_date)=2024 and month(borrow_date)=2 then 1 else 0 
        end) as feb_2024_borrows
        ,sum(case when year(borrow_date)=2024 and month(borrow_date)=1 then 1 else 0 
        end) as jan_2024_borrows
        from BorrowRecords
        where year(borrow_date) in (2023,2024)
        group by book_id
    )a
)

,reg_borrows as(
    select
        book_id
        ,coalesce(round(north_2023 / nullif(total, 0) * 100,2),0) as north_pct_2023
        ,coalesce(round(south_2023 / nullif(total, 0) * 100,2),0) as south_pct_2023
        ,coalesce(round(east_2023 / nullif(total, 0) * 100,2),0) as east_pct_2023
        from(
            select
            r.book_id
            ,count(r.record_id) as total
            ,sum(case when b.region = '华北' then 1 else 0
            end) as north_2023
            ,sum(case when b.region = '华南' then 1 else 0
            end) as south_2023
            ,sum(case when b.region = '华东' then 1 else 0
            end) as east_2023
        from BorrowRecords as r
        left join Branches as b
        using (branch_id)
        where year(r.borrow_date) = 2023
        group by book_id
    )a
)

select
    book_id
    ,bs.book_title
    ,coalesce(bo.feb_2023_borrows,0) as feb_2023_borrows
    ,coalesce(bo.feb_2024_borrows,0) as feb_2024_borrows
    ,coalesce(bo.jan_2024_borrows,0) as jan_2024_borrows
    ,coalesce(bo.yoy_delta,0) as yoy_delta
    ,coalesce(bo.mom_delta,0) as mom_delta
    ,coalesce(rb.north_pct_2023,0) as north_pct_2023
    ,coalesce(rb.south_pct_2023,0) as south_pct_2023
    ,coalesce(rb.east_pct_2023,0) as east_pct_2023
from Books as bs 
left join borrows as bo using(book_id)
left join reg_borrows as rb using(book_id)
order by book_id asc, book_title asc