title: SQL Interview Test
body: <p>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.</p>
  
  <h2>Question</h2>
  
  <p>Table 'transactions' has the following structure:</p>
  
  <pre><code>create table transactions (
          sent_from varchar not null,
          receiver varchar not null,
          date date not null,
          usd_value integer not null);
  </code></pre>
  
  <p>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).</p>
  
  <h2>Example, given data:</h2>
  
  <pre><code>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
  </code></pre>
  
  <p>the query should return the following rowset:</p>
  
  <pre><code>  account_name
   --------------
    Bjorn
    Taylor
  </code></pre>
  
  <p>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.</p>
  
  <h2><strong>My solution:</strong></h2>
  
  <pre><code>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 &lt; 4
    GROUP BY receiver) AS reduced
  WHERE reduced.smount &gt;= 1024
  ORDER BY reduced.receiver ASC;
  </code></pre>
  
  <h2> Set up data (postgreSQL):</h2>
  
  <p>-- I have attempted to set up in www.sqlfiddle.com - <a href="http://sqlfiddle.com/#!15/13fc3/3" rel="nofollow noreferrer">http://sqlfiddle.com/#!15/13fc3/3</a></p>
  
  <pre><code>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 );
  </code></pre>
  
  <hr>
  
  <p>Any hints as to how I should have approached this question greatly appreciated.</p>