Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

01/10/23 23:57:19 (3 years ago)

Author:

201100

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL и складирани процедури)
 
+== Извештај за турнири, информации за кои решавачи учествувале на тие турнири, кои квизови ги решавале, колку поени освоиле, како и кој медали ги освоиле
+
+
+{{{
+select   us.userID, 
+         us.username, 
+         t.tournamentname, 
+         q.quizname, 
+         rt.points, 
+         a.attemptdate, 
+         m.medalname  
+
+from quizTaker_participates_in_tournament as qtou
+
+left join quiztaker as qt 
+on qtou.userID=qt.userID
+left join tournament t 
+on qtou.tournamentid = t.tournamentid 
+left join user_table  as us 
+on qt.userID=us.userID
+left join attempt a  
+on us.userid = a.userid 
+left join result_table rt 
+on rt.attemptid = a.attemptid
+left join quiz q 
+on a.quizid = q.quizid
+join quiz_is_part_of_tournament qipot 
+on q.quizid = qipot.quizid and t.tournamentid = qipot.tournamentid 
+left join medal_is_awarded_to_quiztaker miatq 
+on qt.userid = miatq.userid 
+left join medal m 
+on m.medalid = miatq.medalid and m.tournamentid = qtou.tournamentid 
+order by rt.points
+}}}
+
+== Вкупен број на организирани турнири, играчи и медали
+
+
+{{{
+select   tou.tournamentname as t_name,
+         count(distinct qtpt.userid) as total_players,
+         count(distinct qipot.quizid) as total_quizzes,
+         count(distinct m.medalid) as total_medals
+         
+from tournament tou
+
+left join quizTaker_participates_in_tournament qtpt 
+on tou.tournamentid=qtpt.tournamentid
+left join quiz_is_part_of_tournament qipot 
+on qipot.tournamentid = tou.tournamentid 
+left join attempt a 
+on a.quizid = qipot.quizid 
+left join result_table rt 
+on rt.attemptid = a.attemptid 
+left join medal m 
+on m.tournamentid = tou.tournamentid 
+group by 1
+order by t_name
+
+}}}
+
+
+
+