Напредни извештаи од базата (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