KalC - 4 months ago 13x

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

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.

Source (Stackoverflow)

Comments