WITH pubkey_metrics AS (

SELECT JSON_EXTRACT_PATH_TEXT(account.value, 'pubkey') AS pubkey

, block_timestamp::DATE AS day

, CASE WHEN pubkey = 'Zo1ggzTUKMY5bYnDvT5mtVeZxzf2FaLTbKkmvGUhUQk' THEN '01'

WHEN pubkey = 'C64kTdg1Hzv5KoQmZrQRcm2Qz7PkxtFBgw7EpFhvYn8W' THEN 'acumen'

WHEN pubkey = 'AMM55ShdkoGRB5jVYPjWziwk8m5MpwyDgsMWHaMSQWH6' THEN 'aldrinamm'

WHEN pubkey = 'CURVGoZn8zycx6FXwwevgBTB2gVvdbGTEpvMJDbgs2t4' THEN 'aldrinammv2'

WHEN pubkey = '6UeJYTLU1adaoHWeApWsoj1xNEDbWA2RhM2DLc8CrDDi' THEN 'apricotfinance'

WHEN pubkey = 'ATokenGPvbdGVxr1b2hvZbsiqW5xWH25efTNsLJA8knL' THEN 'associatedtoken'

WHEN pubkey = 'HvwYjjzPbXWpykgVZhqvvfeeaSraQVnTiQibofaFw9M7' THEN 'atrixfinancepoolprogram'

WHEN pubkey = 'WvmTNLpGMVbwJVYztYL4Hnsy82cJhQorxjnnXcRm3b6' THEN 'bonfidapool'

WHEN pubkey = 'BrEAK7zGZ6dM71zUDACDqJnekihmwF15noTddWTsknjC' THEN 'breaksolana'

WHEN pubkey = 'cndy3Z4yapfJBmL3ShUp5exZKqR3z33thTzeNMm2gRZ' THEN 'candymachinev2'

WHEN pubkey = 'creatS3mfzrTGjwuLD1Pa2HXJ1gmq6WXb4ssnwUbJez' THEN 'cardinalcreatorstandard'

WHEN pubkey = 'rwcn6Ry17ChPXpJCN2hoK5kwpgFarQqzycXwVJ3om7U' THEN 'cardinalrewardcenter'

WHEN pubkey = 'rwdNPNPS6zNvtF6FMvaxPRjzu2eC51mXaDT9rmWsojp' THEN 'cardinalrewardsdistributor'

WHEN pubkey = 'stkBL96RZkjY5ine4TvPihGqW8UHJfch2cokjAPzV8i' THEN 'cardinalstakepool'

WHEN pubkey = 'mgr99QFMYByTqGPWmNqunV7vBLmWWXdSrHUfV8Jf3JM' THEN 'cardinaltokenmanager'

WHEN pubkey = '3HUeooitcfKX1TSCx2xEpg2W31n6Qfmizu7nnbaEWYzs' THEN 'cega'

WHEN pubkey = 'Config1111111111111111111111111111111111111' THEN 'config'

WHEN pubkey = 'mmm3XBJg5gk8XJxEKBvdgptZz6SgK4tXvn36sodowMc' THEN 'coralcubeamm'

WHEN pubkey = '6MLxLqiXaaSUpkgMnWDTuejNZEz3kE7k2woyHGVFw319' THEN 'cremaswapv3'

WHEN pubkey = 'CTMAxxk34HjKWxQ3QLZK1HpaLXmBveao3ESePXbiyfzh' THEN 'cropperfinance'

WHEN pubkey = 'cysPXAjehMpVKUapzbMCCnpFxUFFryEWEaLgnb9NrR8' THEN 'cykura'

WHEN pubkey = 'Dooar9JkhdZ7J3LHN3A7YCuoGRUggXhQaG4kijfLGU2j' THEN 'dooarswap'

WHEN pubkey = 'dammHkt7jmytvbS3nHTxQNEcP59aE57nxwV21YdqEDN' THEN 'driftprotocol'

WHEN pubkey = '2qGyiNeWyZxNdkvWHc2jT5qkCnYa1j1gDLSSUmyoWMh8' THEN 'elixir'

WHEN pubkey = 'E1XRkj9fPF2NQUdoq41AHPqwMDHykYfn5PzBXAyDs7Be' THEN 'elixirv2'

WHEN pubkey = '9ehXDD5bnhSpFVRf99veikjgq8VajtRH7e3D9aVPLqYd' THEN 'famousfoxfederation'

WHEN pubkey = 'FoXpJL1exLBJgHVvdSHNKyKu2xX2uatctH9qp6dLmfpP' THEN 'famousfoxfederation'

WHEN pubkey = 'Feat1YXHhH6t1juaWF74WLcfv4XoNocjXA6sPWHNgAse' THEN 'featureproposal'

WHEN pubkey = '8guzmt92HbM7yQ69UJg564hRRX6N4nCdxWE5L6ENrA8P' THEN 'foxyswap'

WHEN pubkey = 'A66HabVL3DzNzeJgcHYtRRNW1ZRMKwBfrdSR4kLsZ9DJ' THEN 'frakt'

WHEN pubkey = 'raffyhEJMx59iDx778Y9gdaVcP2XDeusPcPBxwr3qAH' THEN 'fraktsraffle'