You can use CTE for that

with t as
(
    select  dvd_name, book_name  , count(*) nb
    from usr 
    inner join book on usr_book_id  = book_id 
    inner join dvd on dvd_id = usr_dvd_id
    group by dvd_name, book_name 
    having count(*) > 1
)

select  top 10 usr_smthg,  t.book_name,dvd_name
from t
inner join book b on b.book_name = t.book_name
inner join usr on usr_book_id  = book_id

In sql you can use a sub-query, like this:

select  top 10 usr.usr_smthg,  t.book_name, usr.dvd_name
from (
  select  dvd_name, book_name  , count(*) nb
  from usr 
  inner join book on usr_book_id  = book_id 
  inner join dvd on dvd_id = usr_dvd_id
  group by dvd_name, book_name 
  having count(*) > 1
) t
inner join book b on b.book_name = t.book_name
inner join usr on usr_book_id  = book_id 
-- guess
order by n.nb desc

You can use window function with subquery :

select top (10) t.usr_smthg, t.book_name, t.dvd_name
from (select usr_smthg, book_name, dvd_name, 
             count(*) over (partition by dvd_name, book_name) as cnt
      from usr inner join 
           book 
           on usr_book_id  = book_id inner join 
           dvd 
           on dvd_id = usr_dvd_id
     ) t
 where cnt > 1 
 order by ??;

?? indicates ordering column based on you want top (10) records.

Usually, there are better ways to get the proper result, than selecting from a SELECT result (such as JOIN , GROUP BY , and subselects). However, when using a window function such as ROW_NUMBER() it may become necessary to select from a result.

This can be accomplished by WITH ... AS .

WITH result
AS (SELECT ... FROM ...)
SELECT *
FROM result
WHERE ...

For details see https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/