How to join a query result with a value, received from another query?-CodePudding

I want to calculate transaction costs in USD for a number of most recent transactions on the Rootstock blockchain. I have a PostgreSQL database table with token prices reports.token_prices from which I select the value of the latest available RBTC price in USD:

select tp.price_in_usd  
from reports.token_prices tp
where tp.chain_id = 30 
and tp.coingecko_token_id = 'rootstock'
order by tp.dt desc
limit 1

(note that tp.dt is a timestamp)

Result of the query:

16995.771

Then I have a table with all transactions, chain_rsk_mainnet.block_transactions, from which I select the gas fees for the 5 most recent ones:

select
bt.fees_paid
from chain_rsk_mainnet.block_transactions bt
order by bt.block_id desc, bt.tx_offset 
limit 5

(note that instead of using a timestamp, I'm using bt.block_id and bt.tx_offset for transaction order)

Result:

0
4469416300800
4469416300800
16450260000000
0

Now I want to multiply each of these numbers by the result of the first query. How can I do this in SQL?

CodePudding user response：

Without further information your simplest option would be just convert the first query into a CTE then Join that result in the second query.

with  price_cte(price_in_usd) as 
      (select tp.price_in_usd  
         from reports.token_prices tp
        where tp.chain_id = 30 
          and tp.coingecko_token_id = 'rootstock'
        order by tp.dt desc
        limit 1
      )
select bt.fees_paid * p.price_in_usd)  "Fees Paid in USD"
  from chain_rsk_mainnet.block_transactions bt
  cross join price_cte p
 order by bt.block_id desc, bt.tx_offset 
 limit 5;

NOTE: Not tested, no sample data nor results.