WITH price_updates AS (

SELECT

from_address AS contract_address,

COUNT(*) AS update_count

FROM aurora.core.fact_traces

WHERE to_address = LOWER('0xF89C7b475821EC3fDC2dC8099032c05c6c0c9AB9')

AND block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '6 months'

--AND SUBSTRING(input, 1, 10) IN (

-- '0xef9e5e28', '0xb9256d28', '0x785bdda0', '0xf4347f83',

-- '0x4716e9c5', '0xaccca7f9', '0x0d4702e1', '0x631a316c'

--)

--AND type = 'CALL'

GROUP BY 1

),

user_counts AS (

SELECT

to_address AS contract_address,

COUNT(DISTINCT from_address) AS unique_users

FROM aurora.core.fact_transactions

WHERE block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '6 months'

AND to_address IN (

SELECT contract_address

FROM price_updates

)

AND status = 'SUCCESS'

GROUP BY 1

SELECT

pu.contract_address,

pu.update_count AS price_updates,

COALESCE(uc.unique_users, 0) AS number_of_users,

COALESCE(dl.PROJECT_NAME, 'Unknown') AS project_name,

--RANK() OVER (ORDER BY pu.update_count DESC) AS rank

FROM price_updates pu