SQL Interview Test

Having been given the following question as part of an interview I am keen to know the optimum solution. The question has been edited so that it is not identifiable.

Question

Table ‘transactions’ has the following structure:

create table transactions (
        sent_from varchar not null,
        receiver varchar not null,
        date date not null,
        usd_value integer not null);

Write a query that returns a list of receivers that have received at least 1024 USD in at most 3 transactions. There can be more than 3 transters to that account, as long as some 3 or fewer transactions usd_value total to at least 1024 USD. The table should be ordered by name (in ascending order).

Example, given data:

sent_from   | receiver     | date       | usd_value 
------------+--------------+------------+--------
Jonas       | Willhelm     | 2000-01-01 | 200
Jonas       | Timpson      | 2002-09-27 | 1024
Jonas       | Bjorn        | 2001-03-16 | 512
Willhelm    | Bjorn        | 2010-12-17 | 100
Willhelm    | Bjorn        | 2004-03-22 | 10
Brown       | Bjorn        | 2013_03_20 | 500
Bjorn       | Willhelm     | 2007-06-02 | 400
Bjorn       | Willhelm     | 2001-03-16 | 400
Bjorn       | Willhelm     | 2001-03-16 | 200

the query should return the following rowset:

  account_name
 --------------
  Bjorn
  Taylor

The Bjorn account is listed because it has received 1112 USD in the following three transactions 512 USD + 100 USD + 500 USD = 1112 USD. The Timpson account has received 1024 USD in just one transfer. The Willhelm account received 1200 USD in four transactions, but is it not listed because no three transactions to that account total at least 1024 USD.

My solution:

WITH ordered_transactions AS (
  SELECT
    receiver, usd_value,
    ROW_NUMBER()
    OVER (PARTITION BY
      receiver
      ORDER BY usd_value DESC) AS Row_ID
  FROM public.transactions
)

SELECT receiver FROM
  (SELECT receiver, sum(usd_value) as smount
  FROM ordered_transactions
  WHERE Row_ID < 4
  GROUP BY receiver) AS reduced
WHERE reduced.smount >= 1024
ORDER BY reduced.receiver ASC;

Set up data (postgreSQL):

— I have attempted to set up in www.sqlfiddle.com – http://sqlfiddle.com/#!15/13fc3/3

create table transactions (
    sent_from    VARCHAR NOT NULL,
    receiver VARCHAR NOT NULL,
    date      DATE    NOT NULL,
    usd_value    INTEGER NOT NULL);


insert into transactions VALUES ('Jonas', 'Willhelm', to_date('2000-01-01', 'YYYY-MM-DD'), 200 );
insert into transactions VALUES ('Jonas', 'Taylor', to_date('2002-09-27', 'YYYY-MM-DD'), 1024 );
insert into transactions VALUES ('Jonas', 'Bjorn', to_date('2001-03-16', 'YYYY-MM-DD'), 512 );
insert into transactions VALUES ('Willhelm', 'Bjorn', to_date('2010-12-17', 'YYYY-MM-DD'), 100 );
insert into transactions VALUES ('Willhelm', 'Bjorn', to_date('2004-03-22', 'YYYY-MM-DD'), 10 );
insert into transactions VALUES ('Brown', 'Bjorn', to_date('2013-03-20', 'YYYY-MM-DD'), 500 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2007-06-02', 'YYYY-MM-DD'), 400 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2001-03-16', 'YYYY-MM-DD'), 400 );
insert into transactions VALUES ('Bjorn', 'Willhelm', to_date('2001-03-16', 'YYYY-MM-DD'), 200 );

---

Any hints as to how I should have approached this question greatly appreciated.

Answer

You solution seems fine, but you could simplify the query by using the having clause:

SELECT receiver
FROM ordered_transactions
WHERE Row_ID < 4
GROUP BY receiver
HAVING SUM(usd_value) >= 1024
ORDER BY receiver ASC;