princefarzamThe daily amount of bets in USD which partitioned by sports in the last two weeks
Updated 2022-08-22
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 txns as (SELECT
regexp_substr_all( SUBSTR(DATA, 3, len(DATA)), '.{64}' ) AS segmented_data,
CASE
WHEN concat('0x',SUBSTR(segmented_data[2], 25, 40))='0x0000000000000000000000000000000000000000' THEN 'HOME'
WHEN concat('0x',SUBSTR(segmented_data[2], 25, 40))='0x0000000000000000000000000000000000000001' THEN 'AWAY'
WHEN concat('0x',SUBSTR(segmented_data[2], 25, 40))='0x0000000000000000000000000000000000000002' THEN 'DRAW'
END AS Position,
concat('0x',SUBSTR(segmented_data[1], 25, 40)) AS market,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[3], 25, 40)))/pow(10,18) AS Token_Amount,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[4], 25, 40)))/pow(10,18) AS Expected_Payout,
concat('0x',SUBSTR(segmented_data[5], 25, 40)) AS Stable_coin,
concat('0x',SUBSTR(segmented_data[6], 25, 40)) AS Ticket,
*
FROM optimism.core.fact_event_logs
WHERE CONTRACT_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
AND ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
AND BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - 14
AND TX_STATUS='SUCCESS'
AND EVENT_NAME IS NULL),
Market_Tags AS (
SELECT
regexp_substr_all( SUBSTR(DATA, 3, len(DATA)), '.{64}' ) AS segmented_data,
concat('0x',SUBSTR(segmented_data[0], 25, 40)) AS market,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[17], 25, 40))) AS Tags1,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[18], 25, 40))) AS Tags2,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[19], 25, 40))) AS Tags3,
CASE
WHEN Tags1 >= 9000 and Tags1 <=9020 THEN Tags1
WHEN Tags2 >= 9000 and Tags2 <=9020 THEN Tags2
WHEN Tags3 >= 9000 and Tags3 <=9020 THEN Tags3
END AS TAG,
*
FROM optimism.core.fact_event_logs
WHERE ORIGIN_FROM_ADDRESS='0x646f0733614cfcaffc72f6147228c72bc782d452'
AND ORIGIN_TO_ADDRESS='0x2b91c14ce9aa828ed124d12541452a017d8a2148'