mysql – Query to get sum 1024 in maximum 3 transactions

Question:

I want to display the names of people ( recipient ) who received at least 1024 in maximum 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 make this query?

Answer:

One way to organize the problem is to start with the highest values. If the first three values ​​do not meet the requested condition, then certainly smaller values ​​will not meet the requirements.

organizing the data

Breaking the problem down into parts, we can get the values ​​and row 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 in SQL Fiddle .

getting the answer

Having the data organized, and knowing that only sums whose value is >= 1024 considering line <= 3 , we can use the previous SQL as a 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 .

Scroll to Top