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

Најиграни квизови

select   q.quizname, 
         count(a.attemptid)
from quiz q
join attempt a on q.quizid=a.quizid
where a.attemptdate between now() - interval '2 years' and now()
group by 1
order by count(a.attemptid)

Просечни поени по квиз

select qt.userid, q.quizname, avg(rt.points) from quiztaker qt
left join attempt a on qt.userid= a.userid
left join quiz q on q.quizid=a.quizid
left join result_table rt  on a.attemptid=rt.attemptid
group by 1, 2;

Вкупен број решени квизови во последните 3 месеци

select count(qu.quizid) as broj_resheni_kvizovi
from quiz qu 
join attempt at on qu.quizid = at.quizid 
join quizTaker qt on at.userid  = qt.userid
and at.attemptdate between now() - interval '3 months' and now()

Прашања рангирани според бројот на точни одговори

select   q.questiontext, 
         count(s.choiceid) as counter  

from question q

join selectionquestion sq 
on q.questionid = sq.questionid 
left join choice c 
on c.questionid = q.questionid
left join selectedchoice s 
on s.choiceid = c.choiceid
where s.choiceid=c.choiceid and c.iscorrect
group by 1
order by counter

Рангирани корисници според бројот на медали кои ги освоиле

select u.username, count(miatq.medalid) as medal_count from quiztaker qt
left join medal_is_awarded_to_quiztaker miatq on qt.userid = miatq.userid 
left join user_table u on qt.userid = u.userid 
group by 1
order by medal_count

Рангирани корисници според бројот на обиди во последната година

select   u.username, 
         count(a.attemptid) as attempted_quizzes 

from quiztaker q 

left join user_table u 
on q.userid = u.userid 
left join attempt a 
on a.userid = q.userid 
where a.attemptdate between now() - interval '1 year' and now()
group by 1
order by attempted_quizzes