| 2 | |
| 3 | == Прикажување на најдискутираните филмови за секоја година |
| 4 | {{{ |
| 5 | select year,title, (num_discussions + num_replies) as total from ( |
| 6 | |
| 7 | select godini.year,m.title , count(d1.discussion_id) as num_discussions, count(r.reply_id) as num_replies |
| 8 | from movies m |
| 9 | left join discussions d1 on d1.movie_id = m.movie_id |
| 10 | left join replies r on r.discussion_id = d1.discussion_id |
| 11 | join( |
| 12 | |
| 13 | |
| 14 | select distinct extract(year from d.date) as year |
| 15 | from discussions d |
| 16 | ) as godini on godini.year = extract(year from d1.date) or |
| 17 | extract(year from r.date) = godini.year |
| 18 | |
| 19 | group by m.title, godini.year |
| 20 | |
| 21 | ) as sum_by_year |
| 22 | where (num_discussions + num_replies) = ( |
| 23 | |
| 24 | select max(num_discussions + num_replies) from ( |
| 25 | select godini.year1,m.title as title1 , count(d1.discussion_id) as num_discussions, count(r.reply_id) as |
| 26 | num_replies |
| 27 | |
| 28 | from movies m |
| 29 | left join discussions d1 on d1.movie_id = m.movie_id |
| 30 | left join replies r on r.discussion_id = d1.discussion_id |
| 31 | join( |
| 32 | |
| 33 | |
| 34 | select distinct extract(year from d.date) as year1 |
| 35 | from discussions d |
| 36 | ) as godini on godini.year1 = extract(year from d1.date) or |
| 37 | extract(year from r.date) = godini.year1 |
| 38 | |
| 39 | group by m.title, godini.year1 |
| 40 | |
| 41 | ) as diskusii_repliki |
| 42 | where diskusii_repliki.year1 = year |
| 43 | group by diskusii_repliki.year1 |
| 44 | ); |
| 45 | |
| 46 | |
| 47 | }}} |
| 48 | == Предложување на филмови на корисник врз база на корисниците со кои стапил во интеракција |
| 49 | {{{ |
| 50 | |
| 51 | select distinct m.title |
| 52 | from replies r |
| 53 | join discussions d on r.discussion_id = d.discussion_id |
| 54 | join replies r2 on r2.discussion_id = d.discussion_id and r2.user_id != 1 |
| 55 | join users u on u.user_id = r2.user_id |
| 56 | join movie_likes ml on ml.user_id = u.user_id |
| 57 | join movies m on m.movie_id = ml.movie_id |
| 58 | where r.user_id = 1; |
| 59 | }}} |
| 60 | |
| 61 | * при реализација на прашалникот, наместо 1, ќе биде искористено соодветниот идентификатор на најавениот корисник |