sum of the three largest values in a column, per group

Question

I ran in to this problem on a CS question site, and I can't figure out how to do this.

Problem

My first solution ran in to a dead end with this code

SELECT 
 recipient
FROM 
 transfers
GROUP BY
 recipient
HAVING
 sum(amount) >= 1024
ORDER BY
 recipient ASC;

But there is no way to put a limit on the sum to sum the largest 3 values.

Another idea was to create a subquery and join the two tables.

like so

SELECT
recipient,
(   SELECT SUM(amount)
    FROM transfer t1
    WHERE t1.recipient = t.recipient ORDER BY amount DESC LIMIT 3) as summedValue
FROM
transfer t

However, that summed all the values and not just the top three also.

Answer 1

For one specific recipient, the following query gets the (at most) three largest transfers:

SELECT *
FROM transfers
WHERE recipient = ?
ORDER BY amount DESC
LIMIT 3;

To get the entire amount of these transfers, wrap this into a subquery (otherwise, the LIMIT would be executed after the aggregation):

SELECT sum(amount)
FROM (SELECT *
      FROM transfers
      WHERE recipient = ?
      ORDER BY amount DESC
      LIMIT 3);

Then use that to filter the accounts (the DISTINCT subquery ensures that each account is checked only once):

SELECT recipient
FROM (SELECT DISTINCT recipient
      FROM transfers) AS r
WHERE (SELECT sum(amount)
       FROM (SELECT *
             FROM transfers
             WHERE recipient = r.recipient
             ORDER BY amount DESC
             LIMIT 3)
      ) >= 1024;