KalC - 1 year ago 78

SQL Question

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 Source

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.