with tab1 as (

select BLOCK_TIMESTAMP::date as day

,TX_HASH

from ronin.nft.ez_nft_transfers

where ORIGIN_TO_ADDRESS='0x19f70ecd63f40f11716c3ce2b50a6d07491c12fe'

and ORIGIN_FUNCTION_SIGNATURE='0xd85d3d27'

and NAME='Lumiterra Game Item'

and BLOCK_TIMESTAMP::date>='2024-11-11'

),

tab2 as (

select BLOCK_TIMESTAMP::date as day

,TX_HASH

,TX_FEE

,GAS_USED

,GAS_PRICE/1e9 as gas_price

,CUMULATIVE_GAS_USED

from ronin.core.fact_transactions

select 'lum' as platform

,avg(TX_FEE) as network_fee

,avg((CUMULATIVE_GAS_USED-GAS_USED)*GAS_PRICE) as platform_fee

,avg(CUMULATIVE_GAS_USED*GAS_PRICE) as fee

from tab1

left join tab2

on tab1.TX_HASH=tab2.TX_HASH

and tab1.day=tab2.day

group by 1