adambalaUntitled Query
Updated 2022-08-02
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
›
⌄
with a as
(
select ORIGIN_FROM_ADDRESS ,TX_HASH bridge_tx,BLOCK_TIMESTAMP bridge_date,AMOUNT_USD volume ,AMOUNT volume_eth
from ethereum.core.ez_eth_transfers
where
ORIGIN_TO_ADDRESS ='0xa0c68c638235ee32657e8f720a23cec1bfc77c77' and
ORIGIN_FUNCTION_SIGNATURE ='0x4faa8a26' and
ETH_FROM_ADDRESS='0xa0c68c638235ee32657e8f720a23cec1bfc77c77' and
ETH_TO_ADDRESS ='0x8484ef722627bf18ca5ae6bcf031c23e6e922b30' and BLOCK_TIMESTAMP ::date >CURRENT_DATE -60
),
b as (select distinct ORIGIN_FROM_ADDRESS,tx_id,AMOUNT_USD,BLOCK_TIMESTAMP ,TO_LABEL ,TO_LABEL_SUBTYPE ,TO_LABEL_TYPE
from
(select
distinct ORIGIN_FROM_ADDRESS,tx_id,bridge_tx,AMOUNT_USD,BLOCK_TIMESTAMP ,bridge_date,(datediff('day',bridge_date,BLOCK_TIMESTAMP)) as diff ,TO_LABEL ,TO_LABEL_TYPE ,TO_LABEL_SUBTYPE
from flipside_prod_db.polygon.udm_events inner join a on ORIGIN_FROM_ADDRESS=ORIGIN_ADDRESS
where bridge_date < BLOCK_TIMESTAMP and diff< 7 and TO_LABEL is not null and TO_LABEL_SUBTYPE is not null and TO_LABEL_TYPE is not null
order by 3 desc)
)
SELECT count(distinct ORIGIN_FROM_ADDRESS ) as users ,COUNT(DISTINCT tx_id) AS tx_ids ,sum(AMOUNT_USD) as volume , BLOCK_TIMESTAMP::DATE AS DATE ,TO_LABEL ,TO_LABEL_SUBTYPE ,TO_LABEL_TYPE
FROM
b where BLOCK_TIMESTAMP::date > '2022-03-01'
GROUP BY 4,5,6,7
Run a query to Download Data