tubaecciClaim and Dump
Updated 2024-11-11
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
›
⌄
-- forked from Distribution of Claimers by hold type @ https://flipsidecrypto.xyz/edit/queries/f8750ce5-bf48-4cce-9fa1-c264a7c9e7fd
WITH blast AS(
SELECT
block_timestamp,
tx_hash,
decoded_log:amount / POW(10, 18) AS amount,
decoded_log:delegatedRecipient AS claimer
FROM blast.core.ez_decoded_event_logs
WHERE contract_address = lower('0xF7bE503166828Fe8565C520D66645aC6A06BBdd7')
AND event_name = 'AirdropClaimed'
AND tx_status = 'SUCCESS'
),
claims AS(
SELECT
block_timestamp AS date,
claimer
FROM blast
),
transfers AS(
SELECT
from_address AS sender,
block_timestamp AS date
FROM blast.core.ez_token_transfers
WHERE contract_address = lower('0xb1a5700fA2358173Fe465e6eA4Ff52E36e88E2ad')
),
-- Checking for AVG hours between claim and transfer out
transfers_new AS(
SELECT
claimer,
a.date AS claim_date,
b.date AS transfer_date,
ROW_NUMBER() OVER(PARTITION BY claimer ORDER BY b.date) AS rank
FROM claims AS a
INNER JOIN transfers AS b ON a.claimer = b.sender and b.date >= a.date
GROUP by 1,2,3
QueryRunArchived: QueryRun has been archived