| 99 | | select u.user_id, u.name, u.surname, count(d.discussion_id) as discussion_count, count (r.reply_id) as reply_count from users u |
| 100 | | left join discussions as d on d.user_id = u.user_id |
| 101 | | left join replies as r on r.user_id = u.user_id |
| 102 | | group by u.user_id, u.name, u.surname |
| 103 | | order by count(d.discussion_id) + count (r.reply_id) desc |
| | 99 | |
| | 100 | select distinct mov.title, table2.gnr as genre, mov.description, extract(year from mov.airing_date) as released_year, mov.imdb_rating |
| | 101 | from ( |
| | 102 | select u.user_id, |
| | 103 | g.genre_id, |
| | 104 | count(m), |
| | 105 | m2.movie_id as m_id, |
| | 106 | g.genre_type as gnr |
| | 107 | from users u |
| | 108 | join movie_likes ml on ml.user_id = u.user_id |
| | 109 | join movies m on m.movie_id = ml.movie_id |
| | 110 | join movie_genres mg on mg.movie_id = m.movie_id |
| | 111 | join genres g on g.genre_id = mg.genre_id |
| | 112 | join movie_genres mg2 on mg2.genre_id = mg.genre_id |
| | 113 | join movies m2 on m2.movie_id = mg2.movie_id |
| | 114 | where **u.user_id = 1** |
| | 115 | group by u.user_id, g.genre_id, m2.movie_id , g.genre_type |
| | 116 | order by count(m) desc |
| | 117 | limit 15 |
| | 118 | ) as table2 |
| | 119 | join movies mov on mov.movie_id = table2.m_id |
| | 120 | order by RANDOM() |
| | 121 | limit 5 |
