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

Вкупен број на организирани турнири, играчи и медали

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

Најиграни квизови во последните 6 месеци

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

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

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

Вкупен број решени квизови во последните 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()
group by at.attemptdate

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

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
left join question_is_part_of_quiz qipoq
on q.questionid=qipoq.questionid
left join attempt a 
on qipoq.quizid=a.quizid
where s.choiceid=c.choiceid and c.iscorrect
and a.attemptdate between now() - interval '1 year' and now()
group by 1, a.attemptdate
order by counter desc

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

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 desc

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

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, a.attemptdate
order by attempted_quizzes desc

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

select   q.quizname, 
         count(qipoq.questionid)  as counter 

from quiz q

left join question_is_part_of_quiz qipoq 
on qipoq.quizid = q.quizid 
join question que 
on que.questionid = qipoq.questionid 
join selectionquestion sq 
on que.questionid = sq.questionid 
left join choice c 
on c.questionid = que.questionid
left join selectedchoice s 
on s.choiceid = c.choiceid
left join attempt a
on q.quizid=a.quizid
where s.choiceid=c.choiceid and c.iscorrect
and a.attemptdate between now() - interval '1 month' and now()
group by 1, a.attemptdate
order by counter desc

Топ 5 решавачи на квизови рангирани според бројот на поени во последните 3 месеци

select u.username, res.points, q.quizname 
from result_table res
join attempt a on res.attemptid=a.attemptid
join quiztaker qt on a.userid=qt.userid
join user_table u on qt.userid=u.userid
join quiz q on a.quizId=q.quizId
where a.attemptdate between now() - interval '3 months' and now()
order by res.points desc
group by a.attemptdate
limit 5;