SELECT concat('0x', substr(data, 27, 40)) AS market

, CASE

WHEN tokenflow_eth.hextoint(substr(data, 1091, 64)) NOT ILIKE '%90%'

THEN CASE

WHEN tokenflow_eth.hextoint(substr(data, 1155, 64)) NOT ILIKE '%90%'

THEN tokenflow_eth.hextoint(substr(data, 1219, 64)) -- game name 3 rows

ELSE tokenflow_eth.hextoint(substr(data, 1155, 64)) END -- game name 2 rows

ELSE tokenflow_eth.hextoint(substr(data, 1091, 64)) -- game name 1 row

END AS tag

, CASE

WHEN tag ILIKE '%901%' THEN 'Soccer'

WHEN tag = '9002' THEN 'American Football'

WHEN tag = '9007' THEN 'MMA'

WHEN tag = '9003' THEN 'Baseball'

END AS sport

FROM optimism.core.fact_event_logs

WHERE contract_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

AND topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

),

bet_data AS (

SELECT concat('0x', substr(input_data, 35, 40)) AS market

, substr(input_data, 1, 10) AS methodid

, '' AS collateral -- union aux

, tokenflow_eth.hextoint(substr(input_data, 203, 64))*pow(10,-18) AS usd_sent

, from_address AS user

, block_timestamp::DATE AS date

FROM optimism.core.fact_transactions

WHERE to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

AND status = 'SUCCESS'

AND methodid IN ('0x8875eb84', '0x9f916c9f') --betamm, betammwithrefer

UNION ALL

SELECT concat('0x', substr(input_data, 35, 40)) AS market

, substr(input_data, 1, 10) AS methodid