with x as (SELECT hour::date as date, avg(price) as pr

from crosschain.price.ez_hourly_token_prices

where SYMBOL = 'WETH'

and BLOCKCHAIN = 'ethereum'

and hour::date >= '2023-08-16'

GROUP by 1),

mints as

(SELECT timestamp, tx_hash as hash, INPUTS[0]:value as token_id, caller

from external.tokenflow_starknet.decoded_traces

where FUNCTION = 'mint'

and timestamp::date >= '2023-09-21'

and CHAIN_ID = 'mainnet'

and CONTRACT = '0x042e7815d9e90b7ea53f4550f74dc12207ed6a0faaef57ba0dbf9a66f3762d82'),

fees as

(SELECT

INPUTS[0]:value as minter, (INPUTS[2]:value[0]:value)/1e18 as fee_eth, tx_hash

from external.tokenflow_starknet.decoded_traces

where FUNCTION = 'transferFrom'

and CHAIN_ID = 'mainnet'

and timestamp::date >= '2023-09-21'

and INPUTS[1]:value = '0xe340825f93764e4e0a0219324ecc5a0c76a258376c1deff75affe8397a4aa0'

and CONTRACT = '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7'),

final as

(SELECT timestamp, hash, caller as minter, fee_eth,fee_eth*pr as fee, token_id

from mints join fees on hash=tx_hash

join x on date=timestamp::date)