KalC - 4 months ago 13x
SQL Question

# SELECT SomeColumn where SUM(SomeOtherColumn) of any N rows or less is a certain value

I got totally stumped by this skill assessment question. The skill assessment is done. I am too old to use SO to cheat my way through... Just curious how to solve this.

You have a table with the following columns:

``````Sender | Recipient | Date | Amount
``````

How would you select all Recipients that each have a sum of ANY 3 or fewer amounts that is greater than or equal to X?

For example:

``````Sender  | Recipient |    Date    | Amount
--------+-----------+------------+-------
William | Jane      | 2016-05-27 |  \$1243
Sarah   | Josh      | 2016-05-12 |   \$500
Rohit   | Tammy     | 2016-05-24 |   \$200
Jacob   | Josh      | 2016-05-17 |   \$500
Abraham | Josh      | 2016-05-15 |    \$10
Marie   | Vivian    | 2016-05-16 |  \$1243
Alex    | Josh      | 2016-05-07 |   \$150
``````

If X = \$1024, you should get Jane, Vivian and Josh. Josh, because \$500 + \$500 + \$150 > \$1024.

One trick which will work is to generate a row number for each `Amount`, for each `recipient`. Then we can simply restrict to the greatest three amounts for each group using a simple `WHERE` condition.

``````SELECT recipient, SUM(Amount)
FROM yourTable t
WHERE (SELECT 1 + COUNT(*)
FROM yourTable
WHERE amount >= t.Amount AND recipient = t.recipient) <= 3
GROUP BY recipient
HAVING SUM(Amount) >= 1024
``````

This solution should be robust to the presence of ties. One nice thing about this solution is that it does not require the use of any user defined variables.