Query editor
with base_data as (selectcount(distinct (addr_from, source_chain)) as unique_addresses,count(distinct a.primary_key) as messages,sum(coalesce(b.usd_value, 0)) as usd_volume,count(distinct project) as projects_used,count(distinct casewhen type_transfer = 'oft' then symbol || '-' || projectelse nullend) as ofts_sentfrom layerzero.messages aleft join layerzero.transfers b on a.primary_key = b.messages_foreign_keywhere casewhen cast('{{Stats Time Period}}' as varchar) = '1-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '1' daywhen cast('{{Stats Time Period}}' as varchar) = '7-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '7' daywhen cast('{{Stats Time Period}}' as varchar) = '30-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '30' dayelse trueend),used_contracts as (select count(distinct contract) as used_contractsfrom (select distinct source_chain || '-' || source_contract as contractfrom layerzero.messageswhere casewhen cast('{{Stats Time Period}}' as varchar) = '1-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '1' daywhen cast('{{Stats Time Period}}' as varchar) = '7-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '7' daywhen cast('{{Stats Time Period}}' as varchar) = '30-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '30' dayelse trueendunionselect distinct destination_chain || '-' || destination_contract as contractfrom layerzero.messageswhere casewhen cast('{{Stats Time Period}}' as varchar) = '1-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '1' daywhen cast('{{Stats Time Period}}' as varchar) = '7-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '7' daywhen cast('{{Stats Time Period}}' as varchar) = '30-day' then date(ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '30' dayelse trueend))select *from base_data across join used_contracts b;