KalC KalC - 6 months ago 20
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.

Answer

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.

SQLFiddle