How to integrate MIN(date) function into SELECT*

Question

Breakdown of the problem that I'm trying to solve. I need to:

1. Fetch earliest created_date for every user_id. I know that this can be done with the following query:

   SELECT user_id, MIN(created_date)
   FROM new_schema.transactions
   GROUP BY user_id
   

2. Integrate the above query into the below query. The below works fine on its own.

   SELECT *, t.amount / POWER(10, cd.exponent) * fx.rate AS usd_equivalent
   FROM new_schema.transactions t
   JOIN new_schema.fx_rates fx ON t.currency = fx.ccy
   JOIN new_schema.currency_details cd ON t.currency = cd.currency
   WHERE fx.base_ccy = "USD" 
       AND t.state = "COMPLETED" 
       AND t.type = "CARD_PAYMENT"
   HAVING usd_equivalent > 10;
   

Basically, in addition to the manipulations I'm performing in my second query I also need to fetch the records with earliest created_date for every user_id after applying the said manipulations. How can I enrich my current query to facilitate this?

Answer 1

You can use a window function:

SELECT *, t.amount / POWER(10, cd.exponent) * fx.rate AS usd_equivalent
FROM (SELECT t.*,
              MIN(created_date) OVER (PARTITION BY user_id) as first_created_date
      FROM new_schema.transactions t
     ) t JOIN
     new_schema.fx_rates fx
     ON t.currency = fx.ccy JOIN
     new_schema.currency_details cd
     ON t.currency = cd.currency
WHERE fx.base_ccy = 'USD' AND
      t.state = 'COMPLETED' AND
      t.type = 'CARD_PAYMENT'
HAVING usd_equivalent > 10;