user1269298 user1269298 - 1 year ago 3491
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:


  • 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 Source

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

FROM transfers AS t1
    FROM transfers t2
        t2.recipient = t1.recipient
        AND t2.amount >= t1.amount
) <= 3
GROUP BY t1.recipient
HAVING SUM(t1.amount) >= 1024
ORDER BY t1.recipient;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download