ramishoowOvertime Markets - Profitable Traders
    Updated 2022-08-25
    --Overtime Markets - Profitable Traders
    --(Winning Bets)
    with overtime_markets as (select concat('0x',substr(data,27,40)) as market_address,
    case when len(data) = 1410 then right(tokenflow_eth.hextoint(substr(data,1091,64)),2) when len(data) = 1474 then right(tokenflow_eth.hextoint(substr(data,1155,64)),2)
    else right(tokenflow_eth.hextoint(substr(data,1219,64)),2) end as sport_id, case when sport_id = 1 then 'NCAA Men''s Football' when sport_id = 2 then 'NFL'
    when sport_id = 3 then 'MLB' when sport_id = 4 then 'NBA' when sport_id = 5 then 'NCAA Men''s Basketball' when sport_id = 6 then 'NHL'
    when sport_id = 7 then 'MMA' when sport_id = 8 then 'WNBA' when sport_id = 10 then 'MLS'
    when sport_id = 11 then 'EPL'when sport_id = 12 then 'Ligue 1'when sport_id = 13 then 'Bundesliga'
    when sport_id = 14 then 'La Liga' when sport_id = 15 then 'Serie A' when sport_id = 16 then 'UEFA Champions League'
    end as sport_league, case
    when sport_id in (1,2) then 'Football' when sport_id = 3 then 'Baseball' when sport_id in (4,5,8) then 'Basketball' when sport_id = 6 then 'Hockey' when sport_id = 7 then 'MMA'
    else 'Soccer' end as sport from optimism.core.fact_event_logs where contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'
    and topics[0]::string = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f' and tx_status = 'SUCCESS' ),
    buy_positions as ( select txs.from_address as user_address, sum(raw_amount / pow(10,decimals) * price * (-1)) as buy_usd
    from optimism.core.fact_transactions txs left join optimism.core.fact_token_transfers transfers on txs.tx_hash = transfers.tx_hash and txs.from_address = transfers.origin_from_address
    and transfers.origin_from_address = transfers.from_address left join optimism.core.fact_hourly_token_prices prices on transfers.contract_address = prices.token_address
    and date_trunc('hour',transfers.block_timestamp) = prices.hour inner join overtime_markets om on concat('0x',substr(txs.input_data,35,40)) = om.market_address
    where substr(input_data,0,10) in ('0x8875eb84','0x9f916c9f','0x6cc5a6ff') and status = 'SUCCESS' and txs.block_timestamp >= current_date() - 14 group by 1 ),
    sell_positions as ( select txs.from_address as user_address, sum(raw_amount / pow(10,decimals) * price) as sell_usd from optimism.core.fact_transactions txs
    left join optimism.core.fact_token_transfers transfers on txs.tx_hash = transfers.tx_hash and txs.from_address = transfers.origin_from_address
    and transfers.origin_from_address = transfers.to_address left join optimism.core.fact_hourly_token_prices prices on transfers.contract_address = prices.token_address
    and date_trunc('hour',transfers.block_timestamp) = prices.hour
    inner join overtime_markets om on concat('0x',substr(txs.input_data,35,40)) = om.market_address where substr(input_data,0,10) in ('0x3ce1108d')
    and status = 'SUCCESS' and txs.block_timestamp >= current_date() - 14 group by 1 ), claim_positions as ( select
    origin_from_address as user_address, sum(raw_amount / pow(10,decimals) * price) as claim_usd
    from optimism.core.fact_token_transfers transfers inner join overtime_markets om on transfers.from_address = om.market_address and transfers.origin_from_address = transfers.to_address
    left join optimism.core.fact_hourly_token_prices prices on transfers.contract_address = prices.token_address and date_trunc('hour',transfers.block_timestamp) = prices.hour
    where block_timestamp >= current_date() - 14 group by 1 ), top5 as (
    select user_address, ifnull(buy_usd,0) as buy, ifnull(sell_usd,0) as sell, ifnull(claim_usd,0) as claim,buy + sell + claim as profit
    from buy_positions left join sell_positions using(user_address) left join claim_positions using(user_address) order by profit desc
    limit 5 ),buy_actions as (select txs.from_address as user_address,sport, sport_league, market_address, sum(raw_amount / pow(10,decimals) * price * (-1)) as buy_usd,count(txs.tx_hash) as positions_played
    from optimism.core.fact_transactions txs left join optimism.core.fact_token_transfers transfers on txs.tx_hash = transfers.tx_hash
    and txs.from_address = transfers.origin_from_address and transfers.origin_from_address = transfers.from_address left join optimism.core.fact_hourly_token_prices prices
    on transfers.contract_address = prices.token_address and date_trunc('hour',transfers.block_timestamp) = prices.hour
    inner join overtime_markets om on concat('0x',substr(txs.input_data,35,40)) = om.market_address
    where substr(input_data,0,10) in ('0x8875eb84','0x9f916c9f','0x6cc5a6ff') and status = 'SUCCESS' and txs.block_timestamp >= current_date() - 14 group by 1,2,3,4),
    Run a query to Download Data