SELECT

CONCAT('0x', RIGHT(topics [1] :: STRING, 40)) AS created_contracts

FROM

polygon.core.fact_event_logs

WHERE

block_timestamp :: DATE > '2022-10-16' :: DATE

AND contract_Address = LOWER('0x218b3c623ffb9c5e4dbb9142e6ca6f6559f1c2d6')

AND origin_function_signature = '0x7b366213'

),

base_table AS (

SELECT

block_timestamp::date as day,

tx_hash,

contract_address AS org,

CONCAT('0x', SUBSTR(topics [2] :: STRING, 27, 40)) AS from_address,

CONCAT('0x', SUBSTR(topics [3] :: STRING, 27, 40)) AS to_address,

CONCAT('0x', SUBSTR(topics [1] :: STRING, 27, 40)) AS OPERATOR,

regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,

ethereum.public.udf_hex_to_int(segmented_data [0] :: STRING) AS id,

ethereum.public.udf_hex_to_int(segmented_data [1] :: STRING) AS value_erc1155

FROM

polygon.core.fact_event_logs

WHERE block_timestamp :: DATE > '2022-10-16' :: DATE

AND contract_address IN (SELECT DISTINCT created_contracts FROM orgs)

AND topics [0] :: STRING = '0xc3d58168c5ae7397731d063d5bbf3d657854427343f4c083240f7aacaa2d0f62'

),

mints AS (

SELECT

day, tx_hash, org, operator, to_address AS target, id, value_erc1155, CONCAT(org,'-',target,'-',id) AS unique_id

FROM

base_table

WHERE from_address = '0x0000000000000000000000000000000000000000'

),

burns AS (

SELECT