题解 | 统计借阅量

with
    temp0 as (
        select
            br.book_id,
            count(*) as feb_2023_borrows
        from
            BorrowRecords br
            inner join Books b on br.book_id = b.book_id
        where
            year(borrow_date) = 2023
            and month(borrow_date) = 2
        group by
            br.book_id
    ),
    temp1 as (
        select
            br.book_id,
            count(*) as jan_2024_borrows
        from
            BorrowRecords br
            inner join Books b on br.book_id = b.book_id
        where
            year(borrow_date) = 2024
            and month(borrow_date) = 1
        group by
            br.book_id
    ),
    temp2 as (
        select
            br.book_id,
            count(*) as feb_2024_borrows
        from
            BorrowRecords br
            inner join Books b on br.book_id = b.book_id
        where
            year(borrow_date) = 2024
            and month(borrow_date) = 2
        group by
            br.book_id
    ),
    temp3 as (
        select
            br.book_id,
            ifnull(
                round(
                    100 * count(if(region = "华北", 1, null)) / count(*),
                    2
                ),
                0
            ) as north_pct_2023
        from
            BorrowRecords br
            inner join Books b on br.book_id = b.book_id
            inner join Branches bc on bc.branch_id = br.branch_id
        where
            year(borrow_date) = 2023
        group by
            br.book_id
    ),
    temp4 as (
        select
            br.book_id,
            ifnull(
                round(
                    100 * count(if(region = "华南", 1, null)) / count(*),
                    2
                ),
                0
            ) as south_pct_2023
        from
            BorrowRecords br
            inner join Books b on br.book_id = b.book_id
            inner join Branches bc on bc.branch_id = br.branch_id
        where
            year(borrow_date) = 2023
        group by
            br.book_id
    ),
    temp5 as (
        select
            br.book_id,
            ifnull(
                round(
                    100 * count(if(region = "华东", 1, null)) / count(*),
                    2
                ),
                0
            ) as east_pct_2023
        from
            BorrowRecords br
            inner join Books b on br.book_id = b.book_id
            inner join Branches bc on bc.branch_id = br.branch_id
        where
            year(borrow_date) = 2023
        group by
            br.book_id
    )
select
    b.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(feb_2024_borrows, 0) - ifnull(feb_2023_borrows, 0) as yoy_delta,
    ifnull(feb_2024_borrows, 0) - ifnull(jan_2024_borrows, 0) as mom_delta,
    ifnull(north_pct_2023, 0) as north_pct_2023,
    ifnull(south_pct_2023, 0) as south_pct_2023,
    ifnull(east_pct_2023, 0) as east_pct_2023
from
    Books b
    left join temp0 t0 on b.book_id = t0.book_id
    left join temp1 t1 on b.book_id = t1.book_id
    left join temp2 t2 on b.book_id = t2.book_id
    left join temp3 t3 on b.book_id = t3.book_id
    left join temp4 t4 on b.book_id = t4.book_id
    left join temp5 t5 on b.book_id = t5.book_id
order by
    b.book_id asc,
    book_title asc;