SELECT

cc."Collector",

cc."Submissions",

DENSE_RANK() OVER (

ORDER BY

cc."Submissions" DESC

) AS "Rank"

FROM

(

SELECT

event_data:owner as "Collector",

COUNT(*) as "Submissions"

FROM

flow.core.fact_events

WHERE

event_contract = 'A.4da127056dc9ba3f.Escrow'

AND event_data:leaderboardName ILIKE 'legends_of_the_turf%'

AND event_type = 'EntryDeposited'

GROUP BY

event_data:owner

) cc

CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', fe.block_timestamp) AS "Timestamp",

cc."Collector",

metadata."Player",

metadata."Team",

metadata."Series",

metadata."Tier",

metadata."Set Name",

metadata."Serial #",

TO_NUMBER('1') AS "TX",

cc."Submissions" as "Collectors Submissions",

COUNT(DISTINCT cc."Collector") OVER () AS "Collectors Participating",

COUNT(*) OVER (PARTITION BY metadata."Player", metadata."Team", metadata."Series", metadata."Tier", metadata."Set Name") AS "Moment Submissions",