lagandispenserTransaction Volume By Destination
Updated 2022-07-31
99
1
2
3
4
5
6
7
8
9
10
11
›
⌄
WITH matic_bridge_txns AS (select block_timestamp,block_id,tx_id,to_address as wallet,symbol,amount_usd
from polygon.udm_events where amount is not null and symbol is not NULL and origin_address = '0x0000000000000000000000000000000000000000' and amount > 0 and block_timestamp > CURRENT_DATE - 30),
first_transfer as (
SELECT evt.block_timestamp,evt.tx_id,evt.symbol,evt.from_address as wallet,evt.amount_usd,evt.to_address,evt.to_address_name,evt.to_label,row_number() over(partition by evt.from_address order by evt.block_timestamp ) as rank
FROM polygon.udm_events AS evt WHERE EXISTS ( select 1 from matic_bridge_txns bridge where evt.block_timestamp > bridge.block_timestamp and bridge.wallet = evt.from_address )and evt.amount_usd > 1000 QUALIFY rank = 1),
joined as (SELECT bridge.block_timestamp as bridge_time,first_trnsf.block_timestamp as evt_time,bridge.symbol as bridge_symbol,first_trnsf.symbol as evt_symbol,
bridge.amount_usd as bridge_amount,first_trnsf.amount_usd as evt_amount,bridge.wallet,first_trnsf.to_address,first_trnsf.to_address_name,
case when first_trnsf.to_label is null then 'no label' else first_trnsf.to_label end as label_to,concat(bridge_symbol, '->',evt_symbol) as label_currency
FROM matic_bridge_txns bridge INNER JOIN first_transfer as first_trnsf ON bridge.wallet = first_trnsf.wallet)
SELECT date_trunc(day, bridge_time) as date,label_to,label_currency,to_address,count(*) as txns,sum(evt_amount) as amount
FROM joined GROUP By 1,2,3,4 order by date, amount asc
Run a query to Download Data