Query to get 1024 sum in maximum 3 transactions

Question

I want to display people’s names (recipient) who received at least 1024 in a maximum of 3 transactions.

Here the table "transfers":

   sender     | recipient  | date       | amount
  ------------+------------+------------+--------
   Smith      | Williams   | 2000-01-01 | 200
   Smith      | Taylor     | 2002-09-27 | 1024
   Smith      | Johnson    | 2005-06-26 | 512
   Williams   | Johnson    | 2010-12-17 | 100
   Williams   | Johnson    | 2004-03-22 | 10
   Brown      | Johnson    | 2013-03-20 | 500
   Johnson    | Williams   | 2007-06-02 | 400
   Johnson    | Williams   | 2005-06-26 | 400
   Johnson    | Williams   | 2005-06-26 | 200

Answer: Johnson and Taylor.

How to do this query?

Answer 1

One way to organize the problem is to start with the higher values. If the first three values do not meet the requested condition, surely lower values will also not meet.

Organizing the data

Dividing the problem into parts, we can get values and line numbering using Mysql variables:

SET @cur_rec = "", @cur_line = NULL;

SELECT
  @cur_line := IF(@cur_rec = recipient, @cur_line + 1, 1) AS line,
  @cur_rec := recipient AS recipient,
  amount
FROM
  transactions
ORDER BY
  recipient,
  amount DESC

And the result is this:

line   recipient      amount
   1   Johnson           512
   2   Johnson           500
   3   Johnson           100
   4   Johnson            10
   1   Taylor           1024
   1   Williams          400
   2   Williams          400
   3   Williams          200
   4   Williams          200

See working on SQL Fiddle.

Getting the answer

Having the data organized, and knowing that only meet the condition proposed sums whose value is >= 1024 considering line <= 3, we can use the previous SQL as subquery:

SET @cur_rec = "", @cur_line = NULL;

SELECT
  recipient,
  SUM( amount ) AS total
FROM (
    ---- Aqui é exatamente a query do passo anterior ----
    SELECT
      @cur_line := IF(@cur_rec = recipient, @cur_line + 1, 1) AS line,
      @cur_rec := recipient AS recipient,
      amount
    FROM
      transactions
    ORDER BY
      recipient,
      amount DESC
    -----------------------------------------------------
  ) AS org
WHERE
  line < 4
GROUP BY
  recipient
HAVING
  total >= 1024

Upshot:

   recipient       total
   Johnson          1112
   Taylor           1024

See working on SQL Fiddle.

Answer 2

I need all rows in the table to be uniquely numbered for this solution. Anything, it is trivial to generate a copy table of transfers with autogenerated id. I am only naming this numbered column after rowid.

CTE is a sql99 concept that, in my readings, works on Mysql 8 (in 2016 it was in development; see post on Mysql blog) and works on Mariadb 10.2.2

WITH RECURSIVE SOMA AS (
    SELECT
        RECIPIENT,
        ROWID as MAX_ROWID,
        1 as LEVEL, -- quantas transações diferentes já foram somadas, no caso aqui só a primeira de uma série de transações 
        ANOUNT as TOTAL
     FROM TRANSFERS
     UNION ALL -- daqui, vou para a parte recursiva
    SELECT
         S.RECIPIENT,
         T.ROWID as MAX_ROWID,
          LEVEL + 1 as LEVEL, -- acumulei mais uma transação na minha soma
          S.TOTAL + T.AMOUNT as TOTAL
     FROM
         SOMA S INNER JOIN TRANSFERS T
              ON (
                  T.RECIPIENT= S.RECIPIENT-- só faço a soma para o mesmo destinatário 
                   AND T.ROWID > S.MAX_ROWID -- garantia de que a soma não ocorrerá duas vezes na mesma linha
               )
    WHERE
          S.LEVEL <= 2 -- não precisamos somar mais do que 3 transações 
)
SELECT
    RECIPIENT,
    TOTAL,
     LEVEL
FROM SOMA
WHERE SOMA >= 1024
     AND LEVEL <= 3 -- checagem extra redundante, mas para deixar explícito que foram 3 transações ou menos

For earlier versions of Mysql, you can emulate a recursive CTE with procedures

Answer 3

As said by @Motta, use the HAVING COUNT(recipient) <= 3 AND SUM(amount) >= 1024, this will make it mandatory to have less than four transactions/lines and that the sum of all amount is greater than or equal to 1024.

For example:

SELECT recipient 
FROM   transfers 
GROUP  BY recipient 
HAVING COUNT(recipient) <= 3 
       AND SUM(amount) >= 1024 

---

Now, in the last comment, this query becomes useless. If you want to get the sum of a maximum of three elements that result in more than or equal to 1024, you can take the highest values of the three transactions and then check whether the sum of all major transactions is either not greater than or equal to 1024.

Soon:

SELECT recipient 
FROM transfers t 
GROUP BY t.recipient 
HAVING
(IFNULL(  
  (SELECT amount 
   FROM transfers s 
   WHERE s.recipient = t.recipient 
   ORDER BY amount DESC 
   LIMIT 1 OFFSET 0),
 0) + 
 IFNULL(  
  (SELECT amount 
   FROM transfers s 
   WHERE s.recipient = t.recipient 
   ORDER BY amount DESC 
   LIMIT 1 OFFSET 1),
 0) + 
 IFNULL(  
  (SELECT amount 
   FROM transfers s 
   WHERE s.recipient = t.recipient 
   ORDER BY amount DESC 
   LIMIT 1 OFFSET 2),
 0)) >= 1024

---

Another attempt, however frustrated, was to use:

group_concat(amount order by amount DESC)

Then use the SUBSTRING_INDEX(group_concat(...), '-', 3), thus obtaining the last three values.

But its result is, for example:

512,500,100

If there is any function that can break the , and adding them together would be a solution.