题解 | 统计借阅量
统计借阅量
https://www.nowcoder.com/practice/280ed56ab3ee49a4b2a4595d38e1d565
select book_id ,book_title ,sum(case when date_format(borrow_date ,'%Y-%m') = '2023-02' then 1 else 0 end) feb_2023_borrows ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end) feb_2024_borrows ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-01' then 1 else 0 end) jan_2024_borrows ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end) - sum(case when date_format(borrow_date ,'%Y-%m') = '2023-02' then 1 else 0 end) yoy_delta ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end) - sum(case when date_format(borrow_date ,'%Y-%m') = '2024-01' then 1 else 0 end) mom_delta ,round(ifnull(sum(case when region = '华北' and year(borrow_date) = '2023' then 1 else 0 end)/sum(case when year(borrow_date) = '2023' then 1 else 0 end)*100,0),2) north_pct_2023 ,round(ifnull(sum(case when region = '华南' and year(borrow_date) = '2023' then 1 else 0 end)/sum(case when year(borrow_date) = '2023' then 1 else 0 end)*100,0),2) south_pct_2023 ,round(ifnull(sum(case when region = '华东' and year(borrow_date) = '2023' then 1 else 0 end)/sum(case when year(borrow_date) = '2023' then 1 else 0 end)*100,0),2) east_pct_2023 from Books left join BorrowRecords BR using(book_id) left join Branches using(branch_id) group by 1,2 order by 1,2
