title: Validating an SQL query
body: <p>I am a beginner with SQL and looking to write a query to identify users whose <em>first</em> transaction was a <em>successful card payment</em> over an equivalent value of <em>10 USD</em> (amounts are in different currencies). 
  This is a theoretical exercise whereby I have the databases in excel but currently cannot access any SQL servers to validate this query.</p>
  
  <p><strong>Firstly</strong> I have defined the result set as follows:</p>
  
  <pre><code>   SELECT t.user_id, min(t.created_date), (t.amount / fx.rate / Power (10, cd.exponent) AS amount) 
       FROM transactions AS t
  </code></pre>
  
  <p>This should yield the user ID, earliest date of transaction and the transaction amount in USD (original transaction converted into USD and converted into a cash amount from an integer amount). </p>
  
  <p>Fairly comfortable with the last formula, just want to make sure the <strong>referencing below</strong> brings the fx.rate and cd.exponent correctly so it can actually run:</p>
  
  <pre><code>   JOIN fx_rates AS fx 
       ON ( fx.ccy = t.currency 
            AND fx.base_ccy = 'USD' ) 
     JOIN currency_details AS cd 
       ON cd.currency = t.currency 
  </code></pre>
  
  <p>The above should ensure the 'amount' column has all the references necessary to be calculated.</p>
  
  <p><strong>Finally</strong> I am looking to apply a set of restrictions so data includes only <em>completed card payments over 10 USD</em>:</p>
  
  <pre><code>  WHERE t.type='card_payment'
      AND t.state='completed'
      AND amount&gt;=10
  </code></pre>
  
  <p>This is the tricky bit as I read that you can't reference an alias ('amount') as it isn't really in the result set but not sure if that applies here.</p>
  
  <p>I have two questions:</p>
  
  <p>1) Would this query produce a list of first transactions which were over 10USD? I don't want it to find when/if the transaction reached that threshold. I am only interested in the first transaction for each user. If the answer is no, would I be better of creating a table with first transactions and filtering on that instead? I honestly thought that's what I'm doing here.</p>
  
  <p>2) Is referencing the alias 'amount' allowed within the query? If not, is another SELECT required here?</p>
  
  <p><strong>Full query</strong></p>
  
  <pre><code>      SELECT t.user_id, min(t.created_date), (t.amount / fx.rate / Power (10, cd.exponent) AS amount) 
        FROM transactions AS t
          JOIN fx_rates AS fx 
           ON ( fx.ccy = t.currency 
            AND fx.base_ccy = 'USD' ) 
          JOIN currency_details AS cd 
           ON cd.currency = t.currency
        WHERE t.type='card_payment'
          AND t.state='completed'
          AND amount&gt;=10
  </code></pre>
  
  <p>-------<strong>UPDATE 1</strong>-------</p>
  
  <p>Following numerous comments and answers the updated query is as follows:</p>
  
  <pre><code>SELECT t.user_id, t.created_date, 
    (t.amount / fx.rate / Power(10, cd.exponent)) AS amount
  FROM ( 
      SELECT *, Row_Number () OVER
    (PARTITION BY t.user_id ORDER BY t.created_date) AS RowNum 
      FROM transactions AS t)
    JOIN fx_rates fx 
      ON ( fx.ccy = t.currency
        AND fx.base_ccy = ‘USD') 
    JOIN currency_details cd 
      ON cd.currency = t.currency
  WHERE RowNum = 1 
    AND t.type = ‘card_payment‘
    AND t.state = ‘completed‘
    AND (t.amount / fx.rate / Power(10, cd.exponent)) &gt;= 10
  GROUP  BY t.user_id;
  </code></pre>