SQLite: Leave only n rows for each field I have a table

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Window        | 5         |
|  Zinaida Serebriakova       | Florence      | 1         |
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Bath          | 4         |
|  Thomas And William Daniell | Turbine Makers| 2         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Galleons      | 1         |
|  Robert Motherwell          | Ulysses       | 2         |
+-----------------------------+---------------+-----------+


I need to get 2 paintings for each painter with the highest rate.

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Window        | 5         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Ulysses       | 2         |
|  Robert Motherwell          | Galleons      | 1         |
+-----------------------------+---------------+-----------+


And delete everything else!
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