user1269298 user1269298 - 6 months ago 1118
SQL Question

a neat sql query for money transfer table

Here is my problem

Given a table transfers with the following structure:

create table transfers (
sender varchar not null,
recipient varchar not null,
date date not null,
amount integer not null
);


Write a query in SQL that returns a list of accounts that have received at least 1024 USD in at most three transfers. In other words, an account name is to be listed if there exist some three (or fewer) transfers, with the recipient equal to the account name, such that the sum of the transferred amounts is not less that 1024 USD. Note that there can be more than three transfers to that account, as long as some three or fewer transfers amount to at least 1024 USD. The table should be ordered by name (in ascending order).

The sender and recipient columns contain the names of the corresponding accounts, the date column contains the dates of the transfers, and the amount column contains the transferred amounts expressed in USD. You can assume that the sender value is different from the recipient value in each row.

For example, given the following data:

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


Your query should return the following rowset:

account_name
--------------
Johnson
Taylor



  • The Johnson account is listed because it has received 1112 USD in the following three transfers: 512 USD + 100 USD + 500 USD = 1112 USD.

  • The Taylor account has received 1024 USD in just one transfer.

  • The Williams account received 1200 USD in four transfers, but it is not listed because no three transfers to that account total at least 1024 USD.


Answer

First, get the top 3 transfers for each recipient based on amount and then check if the SUM >= 1024:

SELECT
    t1.recipient
FROM transfers AS t1
WHERE (
    SELECT COUNT(*)
    FROM transfers t2
    WHERE
        t2.recipient = t1.recipient
        AND t2.amount >= t1.amount
) <= 3
GROUP BY t1.recipient
HAVING SUM(t1.amount) >= 1024
ORDER BY t1.recipient;