Query editor
-- Dune query name: tokenized_stocks_xstocks_xlayer_aum_daily_v1-- Description: xStocks X Layer daily AUM in USD. Follows the ink AUM-- pattern (mint/burn cum-sum with issuer-wallet exclusion,-- daily calendar from per-contract anchor, historical stock-- price forward-fill). X Layer is NOT indexed in-- evms.erc20_transfers today, so this query reads raw-- xlayer.logs with the ERC-20 Transfer topic0 filter.-- Token universe is inlined here (single HTTP_GET against-- Backed /rest/tokens filtered to network='XLayer') instead-- of referencing a separate tokens MV.-- Materialize as dune.buccaneers.result_tokenized_stocks_xstocks_xlayer_aum_daily_v1-- Reference in tokenized_stocks_xstocks_chain_aum_daily_base_v1.WITH backed_tokens_raw AS (SELECT JSON_PARSE(HTTP_GET('https://api.backed.fi/rest/tokens')) AS js),backed_tokens_flat AS (SELECTLOWER(CAST(JSON_EXTRACT_SCALAR(deployment, '$.address') AS VARCHAR)) AS contract_address,CAST(JSON_EXTRACT_SCALAR(token, '$.symbol') AS VARCHAR) AS symbol,CAST(JSON_EXTRACT_SCALAR(token, '$.underlyingSymbol') AS VARCHAR) AS underlying_ticker,CAST(JSON_EXTRACT_SCALAR(deployment, '$.network') AS VARCHAR) AS networkFROM backed_tokens_rawCROSS JOIN UNNEST(CAST(JSON_EXTRACT(js, '$.nodes') AS ARRAY(JSON))) AS t(token)CROSS JOIN UNNEST(CAST(JSON_EXTRACT(token, '$.deployments') AS ARRAY(JSON))) AS d(deployment)),xstocks_xlayer_tokens AS (SELECT DISTINCTcontract_address,symbol,COALESCE(NULLIF(underlying_ticker, ''), REGEXP_REPLACE(symbol, 'x$', '')) AS underlying_tickerFROM backed_tokens_flatWHERE network = 'XLayer'),xstocks_xlayer_token_addrs AS (SELECT FROM_HEX(REPLACE(contract_address, '0x', '')) AS addr_binFROM xstocks_xlayer_tokens),xstocks_xlayer_meta AS (SELECTLOWER(CAST(contract_address AS VARCHAR)) AS contract_address,COALESCE(TRY_CAST(decimals AS INTEGER), 18) AS decimalsFROM tokens.erc20WHERE blockchain = 'xlayer'AND contract_address IN (SELECT addr_bin FROM xstocks_xlayer_token_addrs)),xstocks_xlayer_transfers AS (SELECTCAST(DATE_TRUNC('day', l.block_time) AS DATE) AS day,LOWER(CAST(l.contract_address AS VARCHAR)) AS contract_address,bytearray_substring(l.topic1, 13, 20) AS from_bin,bytearray_substring(l.topic2, 13, 20) AS to_bin,CAST(bytearray_to_uint256(l.data) AS DOUBLE)/ POWER(10, COALESCE(m.decimals, 18)) AS amountFROM xlayer.logs lLEFT JOIN xstocks_xlayer_meta mON LOWER(CAST(l.contract_address AS VARCHAR)) = m.contract_addressWHERE l.topic0 = 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef -- Transfer(address,address,uint256)AND l.block_date >= DATE '2025-06-01' -- predates first xstocks deploy on X Layer; adjust if backfill neededAND l.contract_address IN (SELECT addr_bin FROM xstocks_xlayer_token_addrs)