Query to get sum 1024 in max 3 transactions

12

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

Here the transfers table:

   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?

    
asked by anonymous 29.03.2017 / 22:22

3 answers

10

One way to organize the problem is to start with larger values. If the first three values do not meet the requested condition, smaller values will certainly not be met either.


Organizing the Data

By dividing the problem into parts, we can get the values and numbering of lines 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 the sums whose value is >= 1024 considering line <= 3 are met, 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

Result:

   recipient       total
   Johnson          1112
   Taylor           1024

See working in SQL Fiddle .

    
31.03.2017 / 01:56
5

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

CTE is a sql99 concept that, in my readings, works in MySQL 8 (by 2016 it was under development; see MySQL blog post ) 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 CTE recursive with procedures

    
30.03.2017 / 06:51
1

As stated by @Motta , use HAVING COUNT(recipient) <= 3 AND SUM(amount) >= 1024 , this will cause it to have less than four transactions / rows 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 at most three elements that result in greater than or equal to 1024, you can take the highest values from the three transactions and then check if the sum of all the largest transactions is 1024 or greater.

Logo:

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 SUBSTRING_INDEX(group_concat(...), '-', 3) , thus obtaining the last three values.

But its result is, for example:

512,500,100

If there is a function that can break , and add them to it would be a solution.

    
30.03.2017 / 12:54