| | 66 | == Предложување на 10те најдобро рангирани настани на даден корисник, врз база на корисниците со кои стапил во интеракција во последните 2 месеци и корисници кои имаат оставено број на реплики поголем или еднаков од вкупниот просечен на број на реплики по сите корисници |
| | 67 | |
| | 68 | {{{#!div style="font-size: 90%" |
| | 69 | {{{#!sql |
| | 70 | select title from( |
| | 71 | select m.title, sum(mv.stars_rated) as total |
| | 72 | from replies r |
| | 73 | join discussions d on r.discussion_id = d.discussion_id |
| | 74 | join replies r2 on r2.discussion_id = d.discussion_id and r2.user_id != 1 |
| | 75 | join users u on u.user_id = r2.user_id and |
| | 76 | |
| | 77 | ( |
| | 78 | select count(r3.reply_id) |
| | 79 | from replies r3 |
| | 80 | group by u.user_id |
| | 81 | ) >= |
| | 82 | ( |
| | 83 | |
| | 84 | select count(*)::float / (select count(*)::float from users) |
| | 85 | from replies |
| | 86 | ) |
| | 87 | |
| | 88 | join movie_rates mv on mv.user_id = u.user_id |
| | 89 | join movies m on m.movie_id = mv.movie_id |
| | 90 | where r.user_id = 1 and |
| | 91 | r.date between current_date - interval '2 months' and current_date |
| | 92 | group by m.title |
| | 93 | order by sum(mv.stars_rated) desc |
| | 94 | limit 15 |
| | 95 | ) as tabela; |
| | 96 | }}} |
| | 97 | }}} |
| 110 | | {{{#!div style="font-size: 90%" |
| 111 | | {{{#!sql |
| 112 | | select title from( |
| 113 | | select m.title, sum(mv.stars_rated) as total |
| 114 | | from replies r |
| 115 | | join discussions d on r.discussion_id = d.discussion_id |
| 116 | | join replies r2 on r2.discussion_id = d.discussion_id and r2.user_id != 1 |
| 117 | | join users u on u.user_id = r2.user_id and |
| 118 | | |
| 119 | | ( |
| 120 | | select count(r3.reply_id) |
| 121 | | from replies r3 |
| 122 | | group by u.user_id |
| 123 | | ) >= |
| 124 | | ( |
| 125 | | |
| 126 | | select count(*)::float / (select count(*)::float from users) |
| 127 | | from replies |
| 128 | | ) |
| 129 | | |
| 130 | | join movie_rates mv on mv.user_id = u.user_id |
| 131 | | join movies m on m.movie_id = mv.movie_id |
| 132 | | where r.user_id = 1 and |
| 133 | | r.date between current_date - interval '2 months' and current_date |
| 134 | | group by m.title |
| 135 | | order by sum(mv.stars_rated) desc |
| 136 | | limit 15 |
| 137 | | ) as tabela; |
| 138 | | }}} |
| 139 | | }}} |
