Changes between Version 4 and Version 5 of Напредни извештаи од базата (SQL и складирани процедури)

Timestamp:

01/17/25 07:26:47 (5 days ago)

Author:

201205

Comment:

--

Напредни извештаи од базата (SQL и складирани процедури)

@@ -1 +1 @@
 == Напредни извештаи од базата
+
+
+* Извештај за вкупниот број на корисници на апликацијата, просечниот рејтинг на корисниците, и просечниот број на размени на книги меѓу корисниците.
+
+{{{
+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;
+}}}
 
 * Извештај за името на книгата која била најмногу пати разменета во текот на дадена година