Query editor
WITH robinhood_aum AS (SELECTCAST(day AS DATE) AS day,LOWER(CAST(provider AS VARCHAR)) AS provider,LOWER(CAST(blockchain AS VARCHAR)) AS blockchain,CAST(SUM(aum_usd) AS DOUBLE) AS aum_usd,CASEWHEN COUNT(DISTINCT pricing_source) = 1 THEN MAX(pricing_source)ELSE 'mixed_asset_pricing_sources'END AS aum_measurement_typeFROM dune.buccaneers.result_tokenized_stocks_asset_state_daily_v01WHERE LOWER(CAST(provider AS VARCHAR)) = 'robinhood'GROUP BY 1, 2, 3),robinhood_volume AS (SELECTCAST(day AS DATE) AS day,LOWER(CAST(provider AS VARCHAR)) AS provider,LOWER(CAST(blockchain AS VARCHAR)) AS blockchain,CAST(volume_usd AS DOUBLE) AS volume_usd,CAST(trade_count AS BIGINT) AS trade_count,CAST(active_traders AS BIGINT) AS active_traders,CAST(volume_measurement_type AS VARCHAR) AS volume_measurement_typeFROM query_6998448),provider_chain_days AS (SELECT DISTINCT day, provider, blockchain FROM robinhood_aumUNIONSELECT DISTINCT day, provider, blockchain FROM robinhood_volume)SELECTpcd.day,pcd.provider,pcd.blockchain,CAST(COALESCE(a.aum_usd, 0) AS DOUBLE) AS aum_usd,CAST(COALESCE(v.volume_usd, 0) AS DOUBLE) AS volume_usd,v.trade_count,v.active_traders,COALESCE(a.aum_measurement_type, 'not_available') AS aum_measurement_type,COALESCE(v.volume_measurement_type, 'not_available') AS volume_measurement_typeFROM provider_chain_days pcdLEFT JOIN robinhood_aum aON a.day = pcd.dayAND a.provider = pcd.providerAND a.blockchain = pcd.blockchainLEFT JOIN robinhood_volume vON v.day = pcd.dayAND v.provider = pcd.providerAND v.blockchain = pcd.blockchainORDER BY day, provider, blockchain