CryptoIcicleUntitled Query
Updated 2022-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
›
⌄
-- Overtime Markets - Profitable Traders
-- Payout 100 USDC
-- Grand Prize 300 USDC
-- Payout Network Ethereum
-- Level Intermediate
-- Difficulty Hard
-- Thales is a protocol that allows for the creation of peer-to-peer parimutuel markets that anyone can join.
-- A Parimutuel Market is a market where the collateral is locked in a pool and the final payout to participants
-- is not determined until after the market-settling event finalizes.
-- There are several types of markets, including Up and Down markets, ranged markets, exotic markets, and Overtime Markets.
-- Overtime markets is a fully on-chain sports AMM with Chainlink sport feeds, allowing users to bet on Soccer,
-- Football, MMA, and Baseball.
-- For this bounty we will be focused on the Overtime volume on Optimism.
-- Single tx example: https://optimistic.etherscan.io/tx/0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865
-- For more information on Thales and Overtime Markets, see https://thalesmarket.io/ & https://overtimemarkets.xyz/#/markets
-- Identify the 5 most profitable traders on Overtime Markets in the last two weeks.
-- How much has each wallet won by sport?
-- SQL Credit: https://app.flipsidecrypto.com/velocity/queries/01fffd66-6f99-4d22-8b8a-b2ffb248afa6
with txns as (
select
game_contract,
Tags,
concat(home_team, away_team) as game
from
(
SELECT
concat('0x', substr(data, 27, 40)) as game_contract,
HEX_DECODE_STRING(substr(data, 3 + 13 * 64, 64)) as home_team,
HEX_DECODE_STRING(substr(data, 3 + 15 * 64, 64)) as away_team,
regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,
ethereum.public.udf_hex_to_int(concat('0x', SUBSTR(segmented_data [17], 25, 40))) AS tags
from
optimism.core.fact_event_logs
where
Run a query to Download Data