SQLite: Leave only n rows for each field
WITH 
cte AS ( SELECT author, 
                name, 
                rating, 
                ROW_NUMBER() OVER ( PARTITION BY author 
                                    ORDER BY rating DESC ) rn
         FROM ratings )
DELETE
FROM ratings
WHERE EXISTS ( SELECT 1
               FROM cte
               WHERE ratings.author = cte.author
                 AND ratings.name = cte.name
                 AND cte.rn &gt; 2 );


fiddle