MadiUntitled Query
Updated 2022-10-13
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
›
⌄
-- 1.1 find those who claimed their VELO token from LP-ing
-- from optimism.velodrome.ez_claimed_rewards
-- where TOKEN_SYMBOL = 'VELO' AND REWARD_TYPE = 'lp_reward' : 10110
-- 1.2 how many sell their tokens immediately
-- 2.1 how many tokens out of their rewards were sold
-- Time frame of selling (i.e. within the hour of claiming)
-- Amount of sales (i.e. does the sell amount have to be lower or higher than the claimed amount?)
WITH tabl_tok as(
select
a.TOKEN_ADDRESS as TOKEN,
a.BLOCK_TIMESTAMP as TIMESTAMP_CLAIM,
b.BLOCK_TIMESTAMP as TIMESTAMP_SWAP,
a.ORIGIN_FROM_ADDRESS as ADDRESS,
TIMEDIFF(minute, TIMESTAMP_CLAIM, TIMESTAMP_SWAP) as TIME_DIFF
from optimism.velodrome.ez_claimed_rewards a
JOIN optimism.velodrome.ez_swaps b
ON a.TOKEN_ADDRESS = b.TOKEN_ADDRESS_IN and a.ORIGIN_FROM_ADDRESS = b.ORIGIN_FROM_ADDRESS
where a.TOKEN_SYMBOL = 'VELO' AND a.REWARD_TYPE = 'lp_reward' AND TIME_DIFF >= 1 AND TIME_DIFF <=60
)
SELECT
DATE_TRUNC('day', TIMESTAMP_CLAIM) AS time_claim,
COUNT (DISTINCT ADDRESS) as who_claimed
from tabl_tok
group by time_claim
Run a query to Download Data