Query editor
-- @pipko/*########################################################################################################################################################------------------------------------------------------------------- HELPER TABLES ------------------------------------------------------------------------########################################################################################################################################################*/-- all days since beginning (for running counts / sums)with days as (selectperiod::date as daydate,date_trunc('month', period)::date as monthdate,extract(year from period) as year,extract(month from period) as month,extract(day from period) as dayfrom generate_series('2017-09-01'::date, current_date, '1 day') as period),target_stablecoins_list (contract_address) as (VALUES('\x6B175474E89094C44Da98b954EedeAC495271d0F'::bytea), -- DAI('\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'::bytea), -- USDC('\x99d8a9c45b2eca8864373a26d1459e3dff1e17f3'::bytea), -- MIM('\x853d955aCEf822Db058eb8505911ED77F175b99e'::bytea), -- FRAX('\xdAC17F958D2ee523a2206206994597C13D831ec7'::bytea), -- USDT('\x4fabb145d64652a948d72533023f6e7a623c7c53'::bytea) -- BUSD),-- target stablecoin listtarget_stablecoins AS (selectcontract_address,decimals,name,symbol,case when contract_address in (select contract_address from target_stablecoins_list) then name else 'Other' end as labelfrom erc20."stablecoins"wherecontract_address in (select contract_address from target_stablecoins_list) ORFALSE -- *** set to TRUE to override target stablecoins and produce Other category ***),-- stablecoin mint / burn address override (to adjust for tether for example)stablecoin_mint_burn_addr (symbol, contract_address, mint_addr, burn_addr) as (VALUES('USDT','\xdAC17F958D2ee523a2206206994597C13D831ec7'::bytea,'\xc6cde7c39eb2f0f0095f41570af89efc2c1ea828'::bytea,'\xc6cde7c39eb2f0f0095f41570af89efc2c1ea828'::bytea), -- USDT('TUSD','\x0000000000085d4780b73119b644ae5ecd22b376'::bytea,'\xdb25f211ab05b1c97d595516f45794528a807ad8'::bytea,'\xdb25f211ab05b1c97d595516f45794528a807ad8'::bytea), -- TUSD('DAI' ,'\x6B175474E89094C44Da98b954EedeAC495271d0F'::bytea,'\x0000000000000000000000000000000000000000'::bytea,'\x0000000000000000000000000000000000000000'::bytea) -- for example),stablecoins as (selectsc.*,CASE WHEN mb.mint_addr IS NOT NULL THEN mb.mint_addr ELSE '\x0000000000000000000000000000000000000000' END as mint_addr,CASE WHEN mb.burn_addr IS NOT NULL THEN mb.burn_addr ELSE '\x0000000000000000000000000000000000000000' END as burn_addrfrom target_stablecoins as scleft join stablecoin_mint_burn_addr as mb onsc.contract_address = mb.contract_address),cartprod_days_contract_address as (