amelia-leeUntitled Query
Updated 2022-08-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
›
⌄
with table1 as (with tab1 as (select origin_from_address as address, block_timestamp::date as bridge_date, CASE
when event_name='LockedEther' THEN 'ETH'
when event_name='LockedERC20' THEN 'ERC20'
end as asset
from ethereum.core.fact_event_logs
where ORIGIN_TO_ADDRESS='0xa0c68c638235ee32657e8f720a23cec1bfc77c77' and
(event_name='LockedEther' or event_name='LockedERC20')),
tab2 as (select origin_from_address, min(block_timestamp::date) as first_tx_date, event_name,
contract_address, contract_name, tx_hash
from polygon.core.fact_event_logs
group by 1,3,4,5,6)
select address, event_name, contract_address, tx_hash
from tab1 left join tab2 on tab1.address=tab2.origin_from_address and tab1.bridge_date<=tab2.first_tx_date
)
select contract_address, count(distinct tx_hash) as transactions_count, case
when contract_address='0x0000000000000000000000000000000000001010' then 'Polygon: MATIC Token'
when contract_address='0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' then 'Wrapped Ether'
when contract_address='0x2791bca1f2de4661ed88a30c99a7a9449aa84174' then 'USD Coin'
when contract_address='0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' then 'Polygon: WMATIC Token'
when contract_address='0x403e967b044d4be25170310157cb1a4bf10bdd0f' then 'Aavegotchi FUD'
when contract_address='0x44a6e0be76e1d9620a7f76588e4509fe4fa8e8c8' then 'Aavegotchi FOMO'
when contract_address='0x6a3e7c3c6ef65ee26975b12293ca1aad7e1daed2' then 'Aavegotchi ALPHA'
when contract_address='0x42e5e06ef5b90fe15f853f59299fc96259209c5c' then ' Aavegotchi KEK'
when contract_address='0x1d0360bac7299c86ec8e99d0c1c9a95fefaf2a11' then 'Gotchiverse REALM Parcel'
when contract_address='0xd50d167dd35d256e19e2fb76d6b9bf9f4c571a3e' then 'Pegaxy|Pega'
else contract_address
end as contract
from table1
group by 1,3
order by 2 DESC
limit 10
--Emanoel91
Run a query to Download Data