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

, substr(input_data, 138, 1) AS position

, substr(input_data, 1, 10) AS methodid

, '' AS collateral -- union aux

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

, 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

AND block_timestamp >= CURRENT_DATE - 14

UNION ALL

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

, substr(input_data, 138, 1) AS position

, substr(input_data, 1, 10) AS methodid

, concat('0x', substr(input_data, 355, 40)) AS collateral

, tokenflow_eth.hextoint(substr(input_data, 203, 64))*

CASE

WHEN collateral IN ('0x7f5c764cbc14f9669b88837ca1490cca17c31607','0x94b008aa00579c1307b0ef2c499ad98a8ce58e58') THEN pow(10,-6) --usdc, udst

WHEN collateral = lower('0xDA10009cBd5D07dd0CeCc66161FC93D7c9000da1') THEN pow(10,-18) --dai

END AS usd_bet

, from_address AS user

, block_timestamp::DATE AS date

FROM optimism.core.fact_transactions

WHERE to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

AND status = 'SUCCESS'

AND methodid IN ('0xec933f83', '0x6cc5a6ff')

AND block_timestamp >= CURRENT_DATE - 14

UNION ALL

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

, substr(input_data, 138, 1) AS position