Напредни извештаи од базата

• Извештај за името на книгата која била најмногу пати разменета во текот на дадена година

create view most_borrowed_book as

with BookSwapCount as(
	select b.Title, extract(year from t.BorrowDate) as SwapYear, count(tb.BookId) as SwapCount
	from Transaction t join TransactionBook tb on t.TransactionId=tb.TransactionId
		join Book b on tb.BookId=b.BookId
	group by b.title, extract(year from t.borrowdate)
)
select SwapYear, Title, SwapCount
from BookSwapCount
where SwapCount = (
	select max(SwapCount)
	from BookSwapCount bsc
	where bsc.SwapYear = BookSwapCount.SwapYear
);

• Извештај за корисникот со најмал рејтинг за даден период

create view worst_rated_user as

with UserAverageRatings as (
	select au.Username, avg(r.Rating) as AverageRating
	from AppUser au join Review r on au.UserId=r.ReceiverId
	where r.Date between '2025-01-01' and '2025-06-30'
	group by au.Username
)	
select Username, AverageRating
from UserAverageRatings
where AverageRating = (
	select min(AverageRating)
	from UserAverageRatings
);

• Извештај за бројот на корисници кои се пријавени за вознемирување по градови

select au.city, count(r.reporteduserid) as HarassmentReports
from appuser au join report r on au.userid=r.reporteduserid 
where r.reporttype='Harassment'
group by au.city
order by harassmentreports desc;