-- The ask: We’re working on giving Delphi insights on some Terra data. -- A DappRadar report had mentioned that 2M daily unique active wallets interact with dapps, -- we want to see that stats on Terra alone. -- tl;dr: Query on-chain data to find the number of daily active wallets on Terra from the past 90 days -- and give an analysis on findings. -- Humpback Whale - 'balance >= 1B $USD' -- Whale - '10M <= balance < 1B $USD -- Shark - '1M $USD <= balance < 10M $USD' -- Dolphin - '500K $USD <= balance < 1M $USD' -- Fish - '100K $USD <= balance < 500K $USD' -- Octopus - '10K $USD <= balance < 100K $USD' -- Crab - '1K $USD <= balance < 10K $USD' -- Shrimp - 'balance < 1K $USD' with active_wallets as ( select date_trunc('day', block_timestamp) as date, coalesce( msg_value:sender, msg_value: msgs[0]:"from_address", -- Used by authz/MsgExec msg_value: granter, -- Used by 'authz/MsgGrant', 'feegrant/MsgGrantAllowance' msg_value: depositor, -- Used by gov/MsgDeposit msg_value: proposer, -- gov/MsgSubmitProposal msg_value: voter, -- 'gov/MsgVote' msg_value: trader, -- market/MsgSwap msg_value: from_address, msg_value: to_address, msg_value: delegator_address, -- 'staking/MsgBeginRedelegate','staking/MsgCreateValidator','staking/MsgDelegate','staking/MsgUndelegate' msg_value: owner, msg_value: signer ) as address, rank() over ( partition by address order by block_timestamp asc ) as rank, * from terra.msgs where tx_status = 'SUCCEEDED' and address is not NULL and msg_type in( 'applications/transfer.v1.MsgTransfer' -- IBC Application Transfer for user, MsgTransfer defines a msg to transfer fungible tokens (i.e Coins) between ICS20 enabled chains., ,'authz/MsgExec' -- When a grantee wants to execute a transaction on behalf of a granter, they must send MsgExec., ,'authz/MsgGrant' -- MsgGrantAllowance adds permission for Grantee to spend up to Allowance, ,'bank/MsgSend' -- Send coins from one address to another., ,'distribution/MsgWithdrawDelegatorReward' -- Under special circumstances a delegator may wish to withdraw rewards from only a single validator., ,'feegrant/MsgGrantAllowance' -- MsgGrantAllowance adds permission for Grantee to spend up to Allowance of fees from the account of Granter., ,'gov/MsgDeposit' -- MsgDeposit defines a message to submit a deposit to an existing proposal., ,'gov/MsgSubmitProposal' -- MsgSubmitProposal defines an sdk.Msg type that supports submitting arbitrary proposal Content., ,'gov/MsgVote' -- MsgVote defines a message to cast a vote., ,'market/MsgSwap' -- A MsgSwap transaction denotes the Trader’s intent to swap their balance of OfferCoin for a new denomination AskDenom., ,'market/MsgSwapSend' -- A MsgSwapSend first swaps OfferCoin for AskDenom and then sends the acquired coins to ToAddress, ,'staking/MsgBeginRedelegate' -- MsgBeginRedelegate defines a SDK message for performing a redelegation of coins from a delegator and source validator to a destination validator., ,'staking/MsgCreateValidator' -- MsgCreateValidator defines a SDK message for creating a new validator., ,'staking/MsgDelegate' -- MsgDelegate defines a SDK message for performing a delegation of coins from a delegator to a validator., ,'staking/MsgUndelegate' -- MsgUndelegate defines a SDK message for performing an undelegation from a delegate and a validator. ) and msg_type not in ( 'core/channel.v1.MsgAcknowledgement' -- MsgAcknowledgement receives incoming IBC acknowledgement, ,'core/channel.v1.MsgChannelOpenConfirm' -- MsgChannelOpenConfirm defines a msg sent by a Relayer to Chain B to acknowledge the change of channel state to OPEN on Chain A., ,'core/channel.v1.MsgChannelOpenTry' -- MsgChannelOpenInit defines a msg sent by a Relayer to try to open a channel on Chain B., ,'core/channel.v1.MsgRecvPacket' -- MsgRecvPacket receives incoming IBC packet, ,'core/channel.v1.MsgTimeout' -- MsgTimeout receives timed-out packet, ,'core/client.v1.MsgCreateClient' -- MsgCreateClient defines a message to create an IBC client, ,'core/client.v1.MsgUpdateClient' -- MsgUpdateClient defines an sdk.Msg to update a IBC client state using the given header., ,'core/connection.v1.MsgConnectionOpenConfirm' -- MsgConnectionOpenConfirm defines a msg sent by a Relayer to Chain B to acknowledge the change of connection state to OPEN on Chain A., ,'core/connection.v1.MsgConnectionOpenTry' -- MsgConnectionOpenTry defines a msg sent by a Relayer to try to open a connection on Chain B., ,'distribution/MsgWithdrawValidatorCommission' -- MsgWithdrawValidatorCommission withdraws the full commission to the validator address., ,'oracle/MsgAggregateExchangeRatePrevote' -- MsgExchangeRatePrevote - struct for prevoting on the ExchangeRateVote., ,'oracle/MsgAggregateExchangeRateVote' -- Used by Validators, Prevotes are automatically generated using MsgAggregateExchangeRateVote.getPrevote -- MsgAggregateExchangeRateVote messages must precede MsgAggregateExchangeRatePrevote messages in sequence inside a transaction otherwise the MsgAggregateExchangeRatePrevote would override the current registered outstanding prevote for the validator. ,'oracle/MsgDelegateFeedConsent' -- Validators may also elect to delegate voting rights to another key to prevent the block signing key from being kept online. To do so, they must submit a MsgDelegateFeedConsent,, ,'slashing/MsgUnjail' -- MsgUnjail defines the Msg/Unjail request type for Validator, ,'staking/MsgEditValidator' -- MsgEditValidator defines a SDK message for editing an existing validator, no wallet address., ,'wasm/MsgExecuteContract' -- Invokes a function defined within the smart contract. Function and parameters are encoded in ExecuteMsg, which is a JSON message encoded in Base64., ,'wasm/MsgInstantiateContract' -- Creates a new instance of a smart contract. Initial configuration is provided in the InitMsg, which is a JSON message encoded in Base64., ,'wasm/MsgMigrateContract' -- Can be issued by the owner of a migratable smart contract to reset its code ID to another one. MigrateMsg is a JSON message encoded in Base64., ,'wasm/MsgStoreCode' -- Uploads new code to the blockchain and results in a new code ID, if successful. WASMByteCode is accepted as either uncompressed or gzipped binary data encoded as Base64., ,'wasm/MsgUpdateContractAdmin' -- Couldn't find information but looks like something to do with contract update. ) and regexp_like(address, 'terra1[a-z0-9]{38}') and block_timestamp >= CURRENT_DATE-90 ), daily_unique as ( select date, count(distinct(address)) as no_of_wallets from active_wallets group by date ), daily_unique_new as ( select date, count(distinct(address)) as no_of_wallets from active_wallets where rank = 1 group by date ), cumulative_unique_new as ( select date, sum(no_of_wallets) over ( order by date asc rows between unbounded preceding and current row ) as cumulative_no_of_wallets from daily_unique_new ), balance as ( select a.date as date, a.address as address, avg(b.balance_usd) as balance from active_wallets a join terra.daily_balances b on b.address = a.address and a.date = date_trunc('day', b.date) where balance_usd > 0 and block_timestamp >= CURRENT_DATE-90 group by a.date, a.address ) ( select date, COUNT_IF(balance >= 1 * pow(10, 9)) as no_of_wallets, 'Humpback Whale' as type from balance group by date ) union ( select date, COUNT_IF( balance >= 1 * pow(10, 7) -- 10M and balance < 1 * pow(10, 9) -- 1B ) as no_of_wallets, 'Whale' as type from balance group by date ) union ( select date, COUNT_IF( balance >= 1 * pow(10, 6) -- 1M and balance < 1 * pow(10, 7) -- 10M ) as no_of_wallets, 'Shark' as type from balance group by date ) union ( select date, COUNT_IF( balance >= 500 * pow(10, 3) -- 500K and balance < 1 * pow(10, 6) -- 1M ) as no_of_wallets, 'Dolphin' as type from balance group by date ) union ( select date, COUNT_IF( balance >= 100 * pow(10, 3) -- 100K and balance < 500 * pow(10, 3) -- 500K ) as no_of_wallets, 'Fish' as type from balance group by date ) union ( select date, COUNT_IF( balance >= 10 * pow(10, 3) -- 10K and balance < 100 * pow(10, 3) -- 100K ) as no_of_wallets, 'Octopus' as type from balance group by date ) union ( select date, COUNT_IF( balance >= 1 * pow(10, 3) -- 1K and balance < 10 * pow(10, 3) -- 10K ) as no_of_wallets, 'Crab' as type from balance group by date ) union ( select date, COUNT_IF( balance >= 1 and balance < 1 * pow(10, 3) -- 1K ) as no_of_wallets, 'Shrimp' as type from balance group by date )

[FLASH-Delphi] Number of daily active wallets