CODEWITHSUNDEEP
mysql
KalC
KalC

Reputation: 1800

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.

SOLUTION All the three solutions worked. Thanks so much guys. Really appreciate it.

Chose @Tim Biegeleisen's answer for the simplicity factor.

Upvotes: 3

Views: 856

Answers (4)

Palzor Lama
Palzor Lama

Reputation: 1

;
WITH cte
AS (SELECT
  t.amount,
  t.recipient,
  1 AS cnt
FROM transfers t
UNION ALL
SELECT
  t1.amount + t2.amount,
  t2.recipient,
  cnt + 1
FROM cte t2
INNER JOIN transfers t1
  ON t1.recipient = t2.recipient
  AND t1.amount + t2.amount <= 1024
WHERE cnt <= 2)
SELECT
  *
FROM cte
WHERE amount >= 1024

Upvotes: -2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

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 test t
WHERE (SELECT 1 + COUNT(*)
     FROM test
     WHERE Amount >= t.Amount AND Recipient = t.Recipient AND
           date < t.date) <= 3
GROUP BY Recipient
HAVING SUM(Amount) >= 1024

This solution uses the transaction date to break ties (e.g. in the case of Josh receiving $500 in two different records). This solution is not robust to the use case of a recipient having two transactions for the same amount on the same day. One nice thing about this solution is that it does not require the use of any user defined variables.

SQLFiddle

Upvotes: 3

trincot
trincot

Reputation: 350300

You could use variables to number records per receiver in order of descending amount, and then filter for those records that have received a number not more than 3:

select   receiver,
         sum(amount)
from    (select   @num := if(@rec = receiver, @num + 1, 1) as rn,
                  @rec := receiver as receiver,
                  amount
         from     transaction
         order by receiver,
                  amount desc) as numbered
where    rn <= 3
group by receiver
having   sum(amount) >= 1024

SQL fiddle

The nice thing about this solution is that it uses variables to avoid the base table being queried twice, which on large data sets can have a huge performance impact.

Also, it guarantees that at most 3 records will be returned per receiver, even if in the third place there would be a tie: you'll never get 4.

Upvotes: 2

John Ruddell
John Ruddell

Reputation: 25842

What you can do is grab the top 3 of every recipient. and if their sum is greater than the set limit then you can return their names

SET @x = 1024;
SELECT t.Recipient
FROM (
    SELECT 
      Sender, 
      Recipient, 
      Amount, 
      SUM(Amount) as total_amount,
      @count := if(@rec = Recipient, @count + 1, 1) as counter,
      @rec := Recipient
  FROM test
  CROSS JOIN (SELECT @count := 1, @rec := '') t
  GROUP BY Recipient
  HAVING counter <= 3
  ORDER BY Recipient, Amount desc
) t
WHERE t.total_amount >= @x;

FIDDLE

Upvotes: 1

Related Questions