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


* Извештај за вкупниот број на корисници на апликацијата, просечниот рејтинг на корисниците, и просечниот број на размени на книги меѓу корисниците.

{{{
create view user_app_stats as 

select
    (select count(*) from AppUser) as TotalAppUsers,
    coalesce((
        select avg(averageUserRating)
        from(
            select r.ReceiverId, avg(r.Rating) as averageUserRating
            from Review r
            group by r.ReceiverId
        ) as ratings
    ), 0) as AverageUserRating,
    coalesce((
        select avg(transaction_count)
        from(
            select t.BorrowerId, count(*) as transaction_count
            from Transaction t
            group by t.BorrowerId
        ) as transactions
    ), 0) as AverageUserTransactions
}}}

* Извештај за секој член на апликацијата за последните три месеци, бројот на пријатели што ги има тој корисник, бројот на критики што ги примил, бројот на книги што ги има во библиотеката, и бројот на пријави од останатите корисници, подредени од корисникот со најголем број книги до корисникот со најмал број книги.

{{{
create view user_stats as 

select u.UserId, u.FirstName, u.LastName,
    coalesce((
        select count(*) 
        from FriendRequest fr
        where (fr.SenderId=u.UserId or fr.ReceiverId=u.UserId) 
        and fr.Status='Accepted'
    ), 0) as NumberOfFriends,
    coalesce((
        select count(*) 
        from Review r
        where r.GiverId= u.UserId
    ), 0) as NumberOfReviews,
    coalesce((
        select count(*) 
        from Library l join LibraryBook lb on l.InventoryId = lb.InventoryId
        where l.UserId=u.UserId
    ), 0) as NumberOfBooksOwned,
    coalesce((
        select count(*) 
        from Report r
        where r.ReportedUserId = u.UserId and r.ReportDate>=CURRENT_DATE-INTERVAL '3 months'
    ), 0) as NumberOfReportsReceived
from AppUser u
order by NumberOfBooksOwned desc;
}}}

* Извештај за најчесто позајмуваните книги по година и тримесечје

{{{
select
    extract(year from t.BorrowDate) as year,
    extract(quarter from t.BorrowDate) as quarter,
    b.Title, b.Author, count(*) as NumberOfTimesBorrowed
from Transaction t join TransactionBook tb on t.TransactionId=tb.TransactionId join Book b on tb.BookId = b.BookId
group by 
    extract(year from t.BorrowDate),
    extract(quarter from t.BorrowDate),
    b.BookId, b.Title, b.Author
order by year desc, quarter desc, NumberOfTimesBorrowed desc;
}}}

* Извештај за бројот на испратени, одобрени, одбиени и нерешени барања за книги за секој квартал од минатата година.

{{{
select
    extract(quarter from RequestDate) as Quarter,
    count(case when RequestStatus='Approved' then 1 end) as ApprovedBookRequests,
    count(case when RequestStatus='Pending' then 1 end) as PendingBookRequests,
    count(case when RequestStatus='Declined' then 1 end) as DeclinedBookRequests
from BookRequest
where extract(year from RequestDate) = extract(year from now()) -1
group by extract(quarter from RequestDate)
order by Quarter;
}}}

* Извештај за десетте најмногу додавани книги во листата на желби, по бројот додавања во листата

{{{
select b.Title, b.Author, count(*) as TimesAddedToWishlist
from WishlistBook wb join Book b on wb.BookId = b.BookId
group by b.BookId, b.Title, b.Author
order by TimesAddedToWishlist desc
limit 10;
}}}

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

{{{
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;
}}}