Methodology

1. For this bounty, I set out to find a generic way to detect addresses that are passive users. Usually, these kind of transactions (including the ones shared in the prompt) involve the user wallet being used in a transaction initiated by another wallet. In addition the passive user wallet is included in the msg_value:execute_msg JSON.
2. The next step involved me using the flatten function recursively on this JSON to find all addresses that matched the terra1 regex.
3. Next, from all the addresses found, I excluded that were labeled in the terra.labels table.
4. Then, I looked at the keys for many many many addresses and eliminated ones that didn't look like they were a user or used in a transaction for a passive user. These include ask_asset, offer_asset etc. All of these addresses were eliminated from the passive user list.

I spent many many hours on tuning this list of address types to not include in the "passive user" calculation. Even though its not exhaustive yet, I believe it might be useful for Terradash project.

> 'contract', 'spender', 'taker', 'contract_addr', 'fee_recipient', 'version', 'resolver', 'owner', 'collateral_token', 'pair_contract_addr', 'asset_token', 'dex', 'contract_address', 'ts', 'c_w20_token', 't', 'c', 'pair_add', 'pair_add2', 'token_addr', 'token', 'deposit_address', 'ts1', 'ts2', 'ts3', 'cw20', 'ts1_o', 'ts2_o', 'protocol_fee_contract', 'reward_contract', 'validator_contract', 'account_addr', 'pair', 'pool', 'creator', 'pair_address', 'anchor_token', 'admin', 'to', 'pools_contract', 'scc_contract', 'destination_contract', 'in_token_contract_addr', 'out_token_contract_addr', 'vault_contract_addr', 'staking_token', 'airdrop_contract', 'cw20_contract', 'cat_token_contract', 'pair_contract', 'input', 'terraswap_lp_token', 'airdrop_withdraw_contract', 'buyer', 'dapp', 'delegator_contract', 'distributed_token_address', 'resource_contract_address', 'reward_token', 'worker', 'vault_address', 'return_asset', 'lp_token', 'pair_addr', 'masset_token_address', 'nft_contract_address', 'royalty_defi', 'nft_address', 'name', 'pair1', 'pair2', 'pool_address', 'swap_addr', 'factory', 'swap_addr', 'sender', 'fields', 'memo', 'collector_address', 'airdrop_token_address', 'bidder_address', 'cw20_token_contract', 'creator_wallet', 'factory_address', 'game_token', 'gateway_addr', 'in_token', 'maker', 'market_contract', 'operator', 'out_token', 'pc', 'pool_addr', 'royalty_recipient', 'start_after_address', 'seller', 'strategy', 'anc_pool', 'anc_token', 'anchor_aust', 'anchor_custody', 'vault_contract', 'vault', 'ust_asset_pool', 'token_address', 'swap', 'registry_contract', 'player', 'parent_category_address', 'parent_category', 'anchor_interest_model', 'anchor_market', 'anchor_oracle', 'anchor_overseer', 'apr_query_adapter', 'ask', 'asset', 'asset_basset_pool', 'banker_contract', 'basset_rewards_contract', 'basset_token', 'basset_token_contract_addr', 'campaign', 'contract_pair', 'delegate', 'experience_contract_address', 'fee_receiver', 'fees_deposit_address', 'from_address', 'governance', 'investment_contract', 'irm_contract', 'liquidity_token', 'local_token_address', 'looper_manager', 'minter', 'minter_addr', 'nasset_token_contract_addr', 'nexus_vault_addr', 'nft_contract', 'offer', 'owner_address'

Based on this list, here is what the number of passive users look like. This looks high but I think we need to find and eliminate more address types to get to the right amount of "passive" users.

SQL Code

I'm sharing the SQL code for generating this trend if this is a useful starting point for someone:

WITH labeled as (
  SELECT *
  FROM terra.labels
  WHERE ADDRESS_NAME IS NOT NULL
)

, tx as (
  SELECT block_timestamp::date as date, f.value as address--, label
  FROM terra.msgs t, lateral flatten(t.msg_value:execute_msg, recursive=>true) f
  	-- INNER JOIN terra.labels l ON f.value = l.address
  WHERE address ilike 'terra1%' and msg_type = 'wasm/MsgExecuteContract' and address NOT IN (SELECT address_name FROM labeled)
  
  
  UNION 
  
  SELECT block_timestamp::date as date, f.value as address--, label
  FROM terra.msgs t, lateral flatten(t.msg_value, recursive=>true) f
  	-- INNER JOIN terra.labels l ON f.value = l.address
  WHERE address ilike 'terra1%' and msg_type IN ('bank/MsgMultiSend', 'bank/MsgSend') and address NOT IN (SELECT address_name FROM labeled)

  UNION 

  SELECT block_timestamp::date as date, f.value as address--, '' as label
  FROM terra.msgs t, lateral flatten(t.msg_value:execute_msg, recursive=>true) f
  WHERE address <> msg_value:sender and address ilike 'terra1%' and f.key NOT IN ('contract', 'spender', 'taker', 'contract_addr', 'fee_recipient', 'version', 'resolver', 'owner', 'collateral_token', 
  	'pair_contract_addr', 'asset_token', 'dex', 'contract_address', 'ts', 'c_w20_token', 't', 'c', 'pair_add', 'pair_add2', 'token_addr', 'token', 'deposit_address', 'ts1', 'ts2', 'ts3', 'cw20',
  	'ts1_o', 'ts2_o', 'protocol_fee_contract', 'reward_contract', 'validator_contract', 'account_addr', 'pair', 'pool', 'creator', 'pair_address', 'anchor_token', 'admin', 'to', 'pools_contract', 'scc_contract',
  	'destination_contract', 'in_token_contract_addr', 'out_token_contract_addr', 'vault_contract_addr', 'staking_token', 'airdrop_contract', 'cw20_contract', 'cat_token_contract', 'pair_contract', 'input',
  	'terraswap_lp_token', 'airdrop_withdraw_contract', 'buyer', 'dapp', 'delegator_contract', 'distributed_token_address', 'resource_contract_address', 'reward_token', 'worker', 'vault_address', 'return_asset',
  	'lp_token', 'pair_addr', 'masset_token_address', 'nft_contract_address', 'royalty_defi', 'nft_address', 'name', 'pair1', 'pair2', 'pool_address', 'swap_addr', 'factory', 'swap_addr', 'sender', 'fields',
  	'memo', 'collector_address', 'airdrop_token_address', 'bidder_address', 'cw20_token_contract', 'creator_wallet', 'factory_address', 'game_token', 'gateway_addr', 'in_token', 'maker', 'market_contract',
  	'operator', 'out_token', 'pc', 'pool_addr', 'royalty_recipient', 'start_after_address', 'seller', 'strategy', 'anc_pool', 'anc_token', 'anchor_aust', 'anchor_custody', 'vault_contract', 'vault', 'ust_asset_pool',
  	'token_address', 'swap', 'registry_contract', 'player', 'parent_category_address', 'parent_category', 'anchor_interest_model', 'anchor_market', 'anchor_oracle', 'anchor_overseer', 'apr_query_adapter', 'ask',
  	'asset', 'asset_basset_pool', 'banker_contract', 'basset_rewards_contract', 'basset_token', 'basset_token_contract_addr', 'campaign', 'contract_pair', 'delegate', 'experience_contract_address', 'fee_receiver', 
  	'fees_deposit_address', 'from_address', 'governance', 'investment_contract', 'irm_contract', 'liquidity_token', 'local_token_address', 'looper_manager', 'minter', 'minter_addr', 'nasset_token_contract_addr',
  	'nexus_vault_addr', 'nft_contract', 'offer', 'owner_address'
  	
  -- , 'register_addr', 'address', 'addr', 'recipient', 'target', 'borrower'
  
  ) 
  	and address NOT IN (SELECT address_name FROM labeled) and date > CURRENT_DATE - 30 and tx_status = 'SUCCEEDED'
  	and msg_type NOT IN ('oracle/MsgAggregateExchangeRateVote', 'oracle/MsgAggregateExchangeRatePrevote', 'oracle/MsgExchangeRateVote', 'oracle/MsgExchangeRatePrevote')
)

SELECT date, COUNT(DISTINCT address) as num_users
FROM tx 
WHERE date > CURRENT_DATE - 30 --and label IS NULL 
  -- and tx_id = '62C1884906DE7D3C76E43369E63E3A5272A914A936DD100E89445F07843F4031' 
GROUP BY 1

Passive users per type

Splitting the number of passive users into each type, we can see that the top category are "address" and "to_address". It's tough to check all the transactions with these address types but based on my research most of these transactions are using the address field to whitelist other address or sending a transfer which is the perfect definition of a "passive user".