KowalskiDeFiOP Claim Pt 2 (Weird?)
Updated 2023-04-18
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
36
›
⌄
WITH
t as (
SELECT
DATE_TRUNC('hour', BLOCK_TIMESTAMP) as date,
from_address,
COUNT(distinct tx_hash)/COUNT(distinct from_address) as claims_per_address
FROM
optimism.core.fact_event_logs
INNER JOIN
optimism.core.fact_transactions
USING (TX_HASH)
WHERE
contract_address = '0x4200000000000000000000000000000000000042'
AND topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
AND topics[1] = '0x000000000000000000000000fedfaf1a10335448b7fa0268f56d2b44dbd357de'
GROUP BY
date,from_address
),
ovc as
(
SELECT
date,
from_address,
claims_per_address
FROM
t
WHERE
date > '2022-05-29'
AND claims_per_address >1
)
SELECT
DISTINCT from_address,
COUNT(DISTINCT TX_HASH) as num_txns,
SUM(ethereum.public.udf_hex_to_int(data)/POW(10,18)) as amount_claimed
FROM
optimism.core.fact_event_logs as logs
Run a query to Download Data