Group By and get top N in Simple SQL

Question

I have following table in SQLite BANK:

user-id sender-name receiver-name amount
----------------------------------------
1       A           B             200
2       A           C             250
3       A           B             400
4       A           B             520
4       A           D             120
4       A           D             130
4       A           B             110
4       A           B             300
4       A           B             190
4       A           C             230
4       A           B             110
4       A           C             40
4       A           C             80

I need to find out top 3 transaction from each receiver. There are multiple solutions provided for several other database which is not compatible with SQLite cause of the use of certain functions like PARTITION and RANK and even user-defined variables.

I need the solution in simple SQL queries to allow use with SQLite.

Expected result:

receiver-name amount
--------------------
B             560
C             1220
D             250

Answer 1

I managed to do it with using only simple function with self-join.

Now you can just update N with your preferred value, for my case top 3, it would be LIMIT 3.

SELECT receiver-name ,(
    SELECT SUM(amount) as sum_amount
    FROM (
        SELECT amount
        FROM bank as b2   
        WHERE b2.receiver-name = b.receiver-name
        ORDER BY b2.amount DESC
        LIMIT 3 
    )
) as sum_amount
FROM bank as b
GROUP BY receiver-name