题解 | 统计借阅量
统计借阅量
https://www.nowcoder.com/practice/280ed56ab3ee49a4b2a4595d38e1d565
select t.book_id,book_title ,sum(case when date_format(borrow_date ,'%Y-%m') = '2023-02' then 1 else 0 end) as feb_2023_borrows ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end) as feb_2024_borrows ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-01' then 1 else 0 end) as 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) as 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) as 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) as 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) as 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) as east_pct_2023 from Books t left join BorrowRecords t1 on t.book_id=t1.book_id left join Branches t2 on t1.branch_id=t2.branch_id group by t.book_id,book_title order by book_id,book_title
