superflyUntitled Query
Updated 2022-08-26
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 transfers AS ( SELECT block_timestamp::DATE date, tx_hash, origin_from_address trader, contract_address, raw_amount/1e18 amountFROM optimism.core.fact_token_transfersWHERE DATEDIFF('d', block_timestamp::DATE, CURRENT_DATE())<=14 AND
contract_address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' AND
to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
),
games AS (
SELECT DISTINCT tx_hash, contract_address AS game_address
FROM optimism.core.fact_event_logs
WHERE DATEDIFF('d', block_timestamp::DATE, CURRENT_DATE())<=14 AND
origin_to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'
),
sports AS ( SELECT CONCAT ('0x',SUBSTR (data,3+24,40)) game_address, ethereum.public.udf_hex_to_int(REGEXP_SUBSTR (data, '0{60}23[2-3].')) tags,
CASE
when tags IN (9001,9002) then 'American Football'
when tags = 9003 then 'Baseball'
when tags IN (9004,9005,9008) then 'Basketball'
when tags = 9006 then 'Hockey'
when tags = 9007 then 'MMA'
when tags BETWEEN 9010 AND 9016 then 'Soccer'
END AS sport
FROM optimism.core.fact_event_logs
WHERE topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'
AND sport = 'MMA'
),
bets AS (
SELECT game_address, sport, trader, SUM (amount) AS bets_amount, COUNT (*) number_of_bets
FROM transfers JOIN games USING (tx_hash) JOIN sports USING (game_address)
GROUP BY 1,2,3
ORDER BY 4 DESC
),
claims as (
select
event_inputs :account :: string as trader,
event_inputs :value / 1e18 as claim_amount,
tx_hash as claim_tx_hash,
origin_to_address as game_address
from optimism.core.fact_event_logs
Run a query to Download Data