-- forked from Flipppppper / new name for query @ https://staging.flipsidecrypto.xyz/Flipppppper/q/C1XtuUkJZMm7/new-name-for-query

-- forked from LittlerData / 2023-05-24 01:27 PM @ https://flipsidecrypto.xyz/LittlerData/q/2023-05-24-01-27-pm-3uvprw

with challenge_funding as (

select

tx_hash

,block_timestamp as creation_date

,regexp_substr_all(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data

,concat('0x',substr(segmented_data[0]::string,25,40)) as prize_token_address

,ethereum.public.udf_hex_to_int((concat('0x',substr(segmented_data[1]::string,40)))) pool_prize

,case

when prize_token_address = '0x2791bca1f2de4661ed88a30c99a7a9449aa84174' then pool_prize/pow(10,6)

else null

end as prize_corrected

,ethereum.public.udf_hex_to_int(topics[2]::string)::string bounty_id

,concat(contract_address, '-', bounty_id)::string bounty_idd

,origin_from_address as sponsor

,contract_address --marketplace

from polygon.core.fact_event_logs

where block_timestamp > '2022-02-02'

and origin_function_signature = '0x5ea2be72'

and contract_address in ('0x280b3b748ccc42d5062ce59111fad08594f51d9f', '0x3e4f7978447e354e832d38363e927231e518e33a'

,'0xadfd6e51b1a8a4f6cd5bb2e1c33b08cbaf47b1e8', '0xacbf13fd19f35c2032d3030619318f5b595cc7d8'

,'0x3eeac7cf0526e4c26f9cd400fbea852715381709') --approved marketplaces, cant get to leave out the test marketplaces

),

challenge_ipfs as (

select

regexp_substr_all(SUBSTR(input_data, 11, len(input_data)), '.{64}') AS input_segmented_data

,left(concat('https://ipfs.io/ipfs/',try_hex_decode_string(substr(input_data,(64*7)+11,ethereum.public.udf_hex_to_int(input_segmented_data[6]::string)::int*2))),69) as ipfs_link

,bounty_idd

,prize_corrected as funding

,bounty_id