select block_timestamp as fecha,

'Ethereum' as blockchain,

platform_name as marketplace,

tx_hash,

seller_address as seller,

buyer_address as buyer,

price_usd,

platform_fee_usd,

creator_fee_usd,

total_fees_usd,

tx_fee_usd

from ethereum.core.ez_nft_sales

where fecha > '2021-01-01' and fecha < '2022-09-01'

),

agg_eth as (

select fecha::date as date,

blockchain,

marketplace,

count (tx_hash) as txs,

count (distinct seller) as daily_seller,

count (distinct buyer) as daily_buyer,

sum (price_usd) as sales_volume,

sum (sales_volume) over (partition by marketplace order by date) as total_sales,

sum (daily_seller) over (partition by marketplace order by date) as total_sellers,

sum (daily_buyer) over (partition by marketplace order by date) as total_buyers

from raweth

group by 1,2

select * from agg_eth order by date desc