select a.block_timestamp,

a.tx_id,

a.receiver,

l.project_name as symbol,

a.amount/pow(10,a.decimal) as token_amount

from osmosis.core.fact_airdrop a

left join osmosis.core.dim_labels l

on a.currency = l.address

),

prices as (

select date_trunc('hour', recorded_at) as fecha,

symbol,

avg(price) as avg_price

from osmosis.core.dim_prices

--where symbol != 'IOV'

group by 1,2

),

airdrop_prices as (

select a.*,

a.token_amount*p.avg_price as usd_amount

from airdrop a

left join prices p

on a.symbol = p.symbol and date_trunc('hour', a.block_timestamp) = p.fecha

where a.symbol is not NULL -- removes ca. 10k txs for unlabelled token (pool tokens or double tokens i.e. ibc/XXX,8924uosmo)

and p.avg_price is not NULL -- removes TICK, CMDX, LUNC, USTC, ION, DVPN txs

),

OSMO_airdrop_receivers as (

select receiver,

sum(token_amount) as OSMO_airdropped,

sum(usd_amount) as OSMO_usd_airdropped

from airdrop_prices

where symbol = 'OSMO'