vilppuBachelor Thesis - Daily Volume By Game
Updated 2024-08-26
999
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
›
⌄
-- All fee payment transactions for v2 and limited.
-- Volume is derived from the fee (1% of total volume).
with yolo_bets as (
select distinct
block_timestamp,
tx_hash,
event_index as index,
case
when contract_address = lower('0x28EF3eaE1AbB6D6e22e9bFc7a0944f707E4726b3')
then 'YOLO Limited'
when contract_address = lower('0x0000000000E14E87e5c80A8A90817308fFF715d3')
then 'YOLO V2'
end as game_type,
cast(decoded_log['token'] as string) as token,
cast(decoded_log['amount'] as float) / power(10,18) as fee_amount,
cast(decoded_log['amount'] as float) / power(10,18) * 100 as bet_amount,
from
blast.core.ez_decoded_event_logs
where
contract_address in (
lower('0x0000000000E14E87e5c80A8A90817308fFF715d3'), lower('0x28EF3eaE1AbB6D6e22e9bFc7a0944f707E4726b3')
)
and event_name = 'ProtocolFeePayment'
and tx_status = 'SUCCESS'
),
-- Get MOD bets for ETHUSD AND BTCUSD for both directions (moon and doom).
mod_bets as (
select distinct
block_timestamp,
tx_hash,
event_index as index,
case
when contract_address = lower('0x693B37a9859Ce9465Fb2aAdeB03811a26A0c37C0')
then 'MOD ETHUSD'
when contract_address = lower('0xA56A95F41e64Bc76CDE7423aB2A2ee1763bD8Bcc')
QueryRunArchived: QueryRun has been archived