-- forked from LittlerData / marketplace new users @ https://flipsidecrypto.xyz/LittlerData/q/2023-05-17-04-44-pm-JBYnBh

with submissions as (

select

origin_from_address analyst

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

,ethereum.public.udf_hex_to_int(substr(topics[3], 3, 64))::string submission_id

,contract_address

,block_timestamp submission_date

,case

when contract_address = '0x280b3b748ccc42d5062ce59111fad08594f51d9f' then 'Metrics App Beta Launch'

when contract_address = '0x3e4f7978447e354e832d38363e927231e518e33a' then 'MetricsDAO Marketplace'

when contract_address = '0xadfd6e51b1a8a4f6cd5bb2e1c33b08cbaf47b1e8' then 'SkillForge'

when contract_address = '0xacbf13fd19f35c2032d3030619318f5b595cc7d8' then 'Pine Analytics Marketplace'

when contract_address = '0x3eeac7cf0526e4c26f9cd400fbea852715381709' then 'Flash Challenges Marketplace'

else contract_address

end as marketplace

from polygon.core.fact_event_logs

where origin_function_signature = '0x269dde89'

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

,'0xadfd6e51b1a8a4f6cd5bb2e1c33b08cbaf47b1e8', '0xacbf13fd19f35c2032d3030619318f5b595cc7d8'

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

),

submissions_id as (

select

analyst

,submission_date

,marketplace

,row_number() over (partition by analyst order by submission_date asc) rank

from submissions

group by 1, 2, 3