gift_analysis/gift_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "import clickhouse_connect\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "client = clickhouse_connect.get_client(\n",
    "    host='index.bostrom.bronbro.io', port=443,\n",
    "    username='bronbro', password='spac3b0x')\n",
    "\n",
    "IBC_COIN_NAMES = \\\n",
    "    {\n",
    "        'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2': 'uatom in osmosis',\n",
    "        'ibc/FE2CD1E6828EC0FAB8AF39BAC45BC25B965BA67CCBC50C13A14BD610B0D1E2C4': 'boot in osmosis',\n",
    "        'ibc/4F3B0EC2FE2D370D10C3671A1B7B06D2A964C721470C305CBB846ED60E6CAA20': 'hydrogen in osmosis',\n",
    "        'ibc/BCDB35B7390806F35E716D275E1E017999F8281A81B6F128F087EF34D1DFA761': 'tocyb in osmosis',\n",
    "        'ibc/46B44899322F3CD854D2D46DEEF881958467CDD4B3B10086DA49296BBED94BED': 'ujuno in osmosis',\n",
    "        'ibc/EA4C0A9F72E2CEDF10D0E7A9A6A22954DB3444910DB5BE980DF59B05A46DAD1C': 'udsm in osmosis',\n",
    "        'ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5': 'weth in osmosis',\n",
    "        'ibc/65381C5F3FD21442283D56925E62EA524DED8B6927F0FF94E21E0020954C40B5': 'weth.grv in osmosis',\n",
    "        'ibc/3FF92D26B407FD61AE95D975712A7C319CDE28DE4D80BDC9978D935932B991D7': 'dot in osmosis',\n",
    "        'ibc/75345531D87BD90BF108BE7240BD721CB2CB0A1F16D4EBA71B09EC3C43E15C8F': 'btc in osmosis',\n",
    "        'ibc/1E43D59E565D41FB4E54CA639B838FFD5BCFC20003D330A56CB1396231AA1CBA': 'sol in osmosis',\n",
    "        'ibc/D79E7D83AB399BFFF93433E54FAA480C191248FC556924A2A8351AE2638B3877': 'utia in osmosis',\n",
    "        'ibc/E610B83FD5544E00A8A1967A2EB3BEF25F1A8CFE8650FE247A8BD4ECA9DC9222': 'reth in osmosis',\n",
    "        'ibc/CEE970BB3D26F4B907097B6B660489F13F3B0DA765B83CC7D9A0BC0CE220FA6F': 'uflix in osmosis',\n",
    "        'ibc/E97634A40119F1898989C2A23224ED83FDD0A57EA46B3A094E287288D1672B44': 'ugraviton in osmosis',\n",
    "        'ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A': 'aevmos in osmosis',\n",
    "        'ibc/4ABBEF4C8926DDDB320AE5188CFD63267ABBCEFC0583E4AE05D6E5AA2401DDAB': 'usdt in osmosis',\n",
    "\n",
    "        'ibc/15E9C5CF5969080539DB395FA7D9C0868265217EFC528433671AAF9B1912D159': 'uatom in bostrom',\n",
    "        'ibc/BA313C4A19DFBF943586C0387E6B11286F9E416B4DD27574E6909CABE0E342FA': 'deprecated uatom in bostrom',\n",
    "        'ibc/13B2C536BB057AC79D5616B8EA1B9540EC1F2170718CAFF6F0083C966FFFED0B': 'uosmo in bostrom',\n",
    "        'ibc/43DB7553C43D81CB01E9A2644B49A241314B482C2E56F86E85A6539C60383151': 'pussy in bostrom',\n",
    "        'ibc/9B45B8C514B76D792BEC4850AE601E0E73CE7D307A567F34038432FC80D74780': 'liquidpussy in bostrom',\n",
    "        'ibc/8D9262E35CAE362FA74AE05E430550757CF8D842EC1B241F645D3CB7179AFD10': 'ujuno in bostrom',\n",
    "        'ibc/C23D820C5B6009E544AFC8AF5A2FEC288108AEDBFAEFDBBDD6BE54CC23069559': 'ugraviton in bostrom',\n",
    "        'ibc/B6CAD3F7469F3FAD18ED2230A6C7B15E654AB2E1B66E1C70879C04FEF874A863': 'weth.grv in bostrom',\n",
    "        'ibc/4B322204B4F59D770680FE4D7A565DDC3F37BFF035474B717476C66A4F83DD72': 'aevmos in bostrom',\n",
    "        'ibc/CA5E8F31288514D728AFD1F0533A7F6902AA1192C88C9540F814893C3EAFE244': 'udsm in bostrom'\n",
    "     }\n",
    "\n",
    "def query_to_df(query: str) -> pd.DataFrame:\n",
    "    query_res = client.query(query=query)\n",
    "    return pd.DataFrame(query_res.result_rows, columns=query_res.column_names)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:18:37.716959Z",
     "start_time": "2024-01-29T04:18:35.404651Z"
    }
   },
   "id": "b322a5a856416de7",
   "execution_count": 1
  },
  {
   "cell_type": "markdown",
   "source": [
    "## By Claimers"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "ecbeea6c8d656d87"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "query_1 = r'''\n",
    "SELECT *\n",
    "FROM (SELECT signer,\n",
    "             groupArrayIf(passport_nickname, passport_nickname != '')                        as passport_nicknames,\n",
    "             uniqExactIf(proof_address, proof_address != '')                                 as proof_addresses,\n",
    "             uniqExactIf(remove_address, remove_address != '')                               as remove_addresses,\n",
    "             uniqExactIf(gift_claiming_address, gift_claiming_address != '')                 as claim_addresses,\n",
    "             uniqExactIf(release_address, release_address != '')                             as release_addresses,\n",
    "             round(sum(gift_amount_gboot), 1)                                                as claim_gboot,\n",
    "             round(sumIf(gift_amount_gboot, startsWith(gift_claiming_address, '0x')), 1)     as claim_eth_gboot,\n",
    "             round(sumIf(gift_amount_gboot, startsWith(gift_claiming_address, 'cosmos')), 1) as claim_cosmos_gboot,\n",
    "             round(sumIf(gift_amount_gboot, startsWith(gift_claiming_address, 'osmo')), 1)   as claim_osmosis_gboot,\n",
    "             round(sumIf(gift_amount_gboot, startsWith(gift_claiming_address, 'terra')), 1)  as claim_terra_gboot,\n",
    "             claim_gboot * 0.34 - release_gboot                                              as releasable_gboot,\n",
    "             round(sum(released_gboot), 1)                                                   as release_gboot\n",
    "      FROM (SELECT signer,\n",
    "                   arrayZip(JSONExtractArrayRaw(messages), JSONExtractArrayRaw(raw_log)) as messages_logs_zip,\n",
    "                   arrayJoin(messages_logs_zip)                                          as message_log_tuple,\n",
    "                   message_log_tuple.1                                                   as message_row,\n",
    "                   message_log_tuple.2                                                   as raw_log_item,\n",
    "                   trim(BOTH '\"' FROM\n",
    "                        replaceAll(replaceAll(message_row, '\\\\', ''), '@', ''))          as message,\n",
    "                   JSONExtractString(message, 'type')                                    as message_type,\n",
    "                   JSONExtractString(message, 'sender')                                  as message_sender,\n",
    "                   JSONExtractString(message, 'contract')                                as message_contract,\n",
    "                   JSONExtractString(message, 'msg')                                     as message_msg,\n",
    "                   JSON_VALUE(message_msg, '$.create_passport.nickname')                 as passport_nickname,\n",
    "                   JSON_VALUE(message_msg, '$.proof_address.address')                    as proof_address,\n",
    "                   JSON_VALUE(message_msg, '$.remove_address.address')                   as remove_address,\n",
    "                   JSON_VALUE(message_msg, '$.release.gift_address')                     as release_address,\n",
    "                   round(toInt64OrZero(JSON_VALUE(message_msg, '$.claim.gift_amount')) / 1e9,\n",
    "                         1)                                                              as gift_amount_raw_gboot,\n",
    "                   JSON_VALUE(message_msg, '$.claim.gift_claiming_address')              as gift_claiming_address,\n",
    "                   toInt64OrZero(replace(\n",
    "                           JSON_VALUE(raw_log_item, '$.events[1].attributes[0].value'),\n",
    "                           'boot', '')) /\n",
    "                   1e9                                                                   as released_gboot,\n",
    "                   toInt64OrZero(JSON_VALUE(raw_log_item, '$.events[5].attributes[4].value')) /\n",
    "                   1e9                                                                   as gift_amount_gboot,\n",
    "                   date\n",
    "            FROM (SELECT height,\n",
    "                         signer,\n",
    "                         raw_log,\n",
    "                         messages,\n",
    "                         date\n",
    "                  FROM `spacebox`.transaction FINAL\n",
    "                           LEFT ANY\n",
    "                           JOIN (\n",
    "                      SELECT height,\n",
    "                             toDate(timestamp) as date\n",
    "                      FROM `spacebox`.block\n",
    "                          PREWHERE block.height > 10800000\n",
    "                      ) as b\n",
    "                                ON transaction.height = b.height\n",
    "                      PREWHERE transaction.success = true\n",
    "                      and transaction.height > 10800000\n",
    "                     )\n",
    "            WHERE message_type == '/cosmwasm.wasm.v1.MsgExecuteContract'\n",
    "              and message_contract in ('bostrom1xut80d09q0tgtch8p0z4k5f88d3uvt8cvtzm5h3tu3tsy4jk9xlsfzhxel',\n",
    "                                       'bostrom16t6tucgcqdmegye6c9ltlkr237z8yfndmasrhvh7ucrfuqaev6xq7cpvek')\n",
    "            ORDER BY height DESC)\n",
    "      GROUP BY signer)\n",
    "-- WHERE release_gboot > 0\n",
    "ORDER BY release_gboot DESC\n",
    "'''\n",
    "\n",
    "gift_by_singer = query_to_df(query_1)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:19:55.902577Z",
     "start_time": "2024-01-29T04:18:37.715085Z"
    }
   },
   "id": "4d9a0333579ccd9d",
   "execution_count": 2
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "                                               signer  \\\n0      bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6   \n1      bostrom1trt5q36aqdx6l2ca3r5hush2hm9vfu699j9ksn   \n2      bostrom1k6kyj0wukla82z2a0ufmrj99xmxhxhj22r04at   \n3      bostrom1ulefdrkmfluv5zvhc4nwuw5vtnaspx8sh9wpk3   \n4      bostrom1g4ywagfj8yc03ecjn7ghet5exxa5l4kqwuplv4   \n...                                               ...   \n15980  bostrom1ednc63kwckcz6arhzm28f7undd4gqlvygya0ks   \n15981  bostrom19jcktgz30hz8vd637cgcxmmqvrut3k0ky68fv3   \n15982  bostrom153cw09k759z52lkmh4xxsgy37gtt6splyf8388   \n15983  bostrom1gt4kqr6jsfd987qsgzj8wh7twg8es6ye50qglr   \n15984  bostrom19phgdjcswyf7q4x4ncwgde70rydpv5p90h9wyu   \n\n                    passport_nicknames  proof_addresses  remove_addresses  \\\n0                          [etwet46w4]             3640              3640   \n1                      [fbfbhbvcccccc]             2147              2141   \n2                       [blackhole666]              497               497   \n3                          [proj3proj]             1255              1251   \n4                        [qwrtgwrhwfq]              473               473   \n...                                ...              ...               ...   \n15980                       [ngnfn554]                2                 0   \n15981  [54545454545454545454545454542]                2                 0   \n15982                     [jasvoyager]                0                 0   \n15983                  [laloaaaaaaaaa]                3                 0   \n15984                        [kosmos1]                2                 0   \n\n       claim_addresses  release_addresses  claim_gboot  claim_eth_gboot  \\\n0                 1549               1505       1541.3           1541.3   \n1                 2142               2143       1020.5           1020.5   \n2                  497                497        450.4            450.4   \n3                 1256               1254        391.5            381.7   \n4                  119                119        366.2            366.2   \n...                ...                ...          ...              ...   \n15980                0                  0          0.0              0.0   \n15981                0                  0          0.0              0.0   \n15982                0                  0          0.0              0.0   \n15983                0                  0          0.0              0.0   \n15984                0                  0          0.0              0.0   \n\n       claim_cosmos_gboot  claim_osmosis_gboot  claim_terra_gboot  \\\n0                     0.0                  0.0                0.0   \n1                     0.0                  0.0                0.0   \n2                     0.0                  0.0                0.0   \n3                     0.0                  3.2                6.5   \n4                     0.0                  0.0                0.0   \n...                   ...                  ...                ...   \n15980                 0.0                  0.0                0.0   \n15981                 0.0                  0.0                0.0   \n15982                 0.0                  0.0                0.0   \n15983                 0.0                  0.0                0.0   \n15984                 0.0                  0.0                0.0   \n\n       releasable_gboot  release_gboot  \n0                88.842          435.2  \n1                12.970          334.0  \n2                -0.064          153.2  \n3                 1.710          131.4  \n4                 1.508          123.0  \n...                 ...            ...  \n15980             0.000            0.0  \n15981             0.000            0.0  \n15982             0.000            0.0  \n15983             0.000            0.0  \n15984             0.000            0.0  \n\n[15985 rows x 13 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>signer</th>\n      <th>passport_nicknames</th>\n      <th>proof_addresses</th>\n      <th>remove_addresses</th>\n      <th>claim_addresses</th>\n      <th>release_addresses</th>\n      <th>claim_gboot</th>\n      <th>claim_eth_gboot</th>\n      <th>claim_cosmos_gboot</th>\n      <th>claim_osmosis_gboot</th>\n      <th>claim_terra_gboot</th>\n      <th>releasable_gboot</th>\n      <th>release_gboot</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6</td>\n      <td>[etwet46w4]</td>\n      <td>3640</td>\n      <td>3640</td>\n      <td>1549</td>\n      <td>1505</td>\n      <td>1541.3</td>\n      <td>1541.3</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>88.842</td>\n      <td>435.2</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>bostrom1trt5q36aqdx6l2ca3r5hush2hm9vfu699j9ksn</td>\n      <td>[fbfbhbvcccccc]</td>\n      <td>2147</td>\n      <td>2141</td>\n      <td>2142</td>\n      <td>2143</td>\n      <td>1020.5</td>\n      <td>1020.5</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>12.970</td>\n      <td>334.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>bostrom1k6kyj0wukla82z2a0ufmrj99xmxhxhj22r04at</td>\n      <td>[blackhole666]</td>\n      <td>497</td>\n      <td>497</td>\n      <td>497</td>\n      <td>497</td>\n      <td>450.4</td>\n      <td>450.4</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>-0.064</td>\n      <td>153.2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>bostrom1ulefdrkmfluv5zvhc4nwuw5vtnaspx8sh9wpk3</td>\n      <td>[proj3proj]</td>\n      <td>1255</td>\n      <td>1251</td>\n      <td>1256</td>\n      <td>1254</td>\n      <td>391.5</td>\n      <td>381.7</td>\n      <td>0.0</td>\n      <td>3.2</td>\n      <td>6.5</td>\n      <td>1.710</td>\n      <td>131.4</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>bostrom1g4ywagfj8yc03ecjn7ghet5exxa5l4kqwuplv4</td>\n      <td>[qwrtgwrhwfq]</td>\n      <td>473</td>\n      <td>473</td>\n      <td>119</td>\n      <td>119</td>\n      <td>366.2</td>\n      <td>366.2</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>1.508</td>\n      <td>123.0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>15980</th>\n      <td>bostrom1ednc63kwckcz6arhzm28f7undd4gqlvygya0ks</td>\n      <td>[ngnfn554]</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.000</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>15981</th>\n      <td>bostrom19jcktgz30hz8vd637cgcxmmqvrut3k0ky68fv3</td>\n      <td>[54545454545454545454545454542]</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.000</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>15982</th>\n      <td>bostrom153cw09k759z52lkmh4xxsgy37gtt6splyf8388</td>\n      <td>[jasvoyager]</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.000</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>15983</th>\n      <td>bostrom1gt4kqr6jsfd987qsgzj8wh7twg8es6ye50qglr</td>\n      <td>[laloaaaaaaaaa]</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.000</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>15984</th>\n      <td>bostrom19phgdjcswyf7q4x4ncwgde70rydpv5p90h9wyu</td>\n      <td>[kosmos1]</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.000</td>\n      <td>0.0</td>\n    </tr>\n  </tbody>\n</table>\n<p>15985 rows × 13 columns</p>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gift_by_singer"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:19:55.924271Z",
     "start_time": "2024-01-29T04:19:55.906660Z"
    }
   },
   "id": "92ad83f2b7a85888",
   "execution_count": 3
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "                   release_gboot  signer  release_addresses\nrelease_addresses                                          \n0                            0.0    8160                  0\n1                         5654.3    4497               4497\n2                         4388.8    1559               3118\n3                         2598.1     554               1662\n4                         2361.9     285               1140\n5                         1578.1     183                915\n6                         1198.2     166                996\n7                          712.1     123                861\n8                         2873.0     416               3328\n9                           24.5       2                 18\n10                          14.3       1                 10\n11                          28.1       1                 11\n12                           8.6       1                 12\n14                          83.2       2                 28\n17                          30.2       2                 34\n19                           7.0       1                 19\n21                          17.7       1                 21\n22                          15.1       1                 22\n23                          17.8       1                 23\n25                          41.1       2                 50\n26                          14.9       1                 26\n27                          31.1       2                 54\n28                          41.2       2                 56\n30                          22.8       1                 30\n31                          17.4       1                 31\n33                          44.0       2                 66\n35                           3.3       1                 35\n43                          42.4       1                 43\n51                         112.3       1                 51\n65                          25.9       1                 65\n73                           7.6       1                 73\n74                          45.4       1                 74\n103                         60.9       1                103\n119                        123.0       1                119\n346                         37.6       1                346\n356                         55.9       1                356\n497                        153.2       1                497\n656                         64.4       1                656\n983                         96.3       1                983\n994                        198.9       2               1988\n1254                       131.4       1               1254\n1505                       435.2       1               1505\n2143                       334.0       1               2143",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>release_gboot</th>\n      <th>signer</th>\n      <th>release_addresses</th>\n    </tr>\n    <tr>\n      <th>release_addresses</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0.0</td>\n      <td>8160</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5654.3</td>\n      <td>4497</td>\n      <td>4497</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>4388.8</td>\n      <td>1559</td>\n      <td>3118</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2598.1</td>\n      <td>554</td>\n      <td>1662</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2361.9</td>\n      <td>285</td>\n      <td>1140</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>1578.1</td>\n      <td>183</td>\n      <td>915</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>1198.2</td>\n      <td>166</td>\n      <td>996</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>712.1</td>\n      <td>123</td>\n      <td>861</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>2873.0</td>\n      <td>416</td>\n      <td>3328</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>24.5</td>\n      <td>2</td>\n      <td>18</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>14.3</td>\n      <td>1</td>\n      <td>10</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>28.1</td>\n      <td>1</td>\n      <td>11</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>8.6</td>\n      <td>1</td>\n      <td>12</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>83.2</td>\n      <td>2</td>\n      <td>28</td>\n    </tr>\n    <tr>\n      <th>17</th>\n      <td>30.2</td>\n      <td>2</td>\n      <td>34</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>7.0</td>\n      <td>1</td>\n      <td>19</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>17.7</td>\n      <td>1</td>\n      <td>21</td>\n    </tr>\n    <tr>\n      <th>22</th>\n      <td>15.1</td>\n      <td>1</td>\n      <td>22</td>\n    </tr>\n    <tr>\n      <th>23</th>\n      <td>17.8</td>\n      <td>1</td>\n      <td>23</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>41.1</td>\n      <td>2</td>\n      <td>50</td>\n    </tr>\n    <tr>\n      <th>26</th>\n      <td>14.9</td>\n      <td>1</td>\n      <td>26</td>\n    </tr>\n    <tr>\n      <th>27</th>\n      <td>31.1</td>\n      <td>2</td>\n      <td>54</td>\n    </tr>\n    <tr>\n      <th>28</th>\n      <td>41.2</td>\n      <td>2</td>\n      <td>56</td>\n    </tr>\n    <tr>\n      <th>30</th>\n      <td>22.8</td>\n      <td>1</td>\n      <td>30</td>\n    </tr>\n    <tr>\n      <th>31</th>\n      <td>17.4</td>\n      <td>1</td>\n      <td>31</td>\n    </tr>\n    <tr>\n      <th>33</th>\n      <td>44.0</td>\n      <td>2</td>\n      <td>66</td>\n    </tr>\n    <tr>\n      <th>35</th>\n      <td>3.3</td>\n      <td>1</td>\n      <td>35</td>\n    </tr>\n    <tr>\n      <th>43</th>\n      <td>42.4</td>\n      <td>1</td>\n      <td>43</td>\n    </tr>\n    <tr>\n      <th>51</th>\n      <td>112.3</td>\n      <td>1</td>\n      <td>51</td>\n    </tr>\n    <tr>\n      <th>65</th>\n      <td>25.9</td>\n      <td>1</td>\n      <td>65</td>\n    </tr>\n    <tr>\n      <th>73</th>\n      <td>7.6</td>\n      <td>1</td>\n      <td>73</td>\n    </tr>\n    <tr>\n      <th>74</th>\n      <td>45.4</td>\n      <td>1</td>\n      <td>74</td>\n    </tr>\n    <tr>\n      <th>103</th>\n      <td>60.9</td>\n      <td>1</td>\n      <td>103</td>\n    </tr>\n    <tr>\n      <th>119</th>\n      <td>123.0</td>\n      <td>1</td>\n      <td>119</td>\n    </tr>\n    <tr>\n      <th>346</th>\n      <td>37.6</td>\n      <td>1</td>\n      <td>346</td>\n    </tr>\n    <tr>\n      <th>356</th>\n      <td>55.9</td>\n      <td>1</td>\n      <td>356</td>\n    </tr>\n    <tr>\n      <th>497</th>\n      <td>153.2</td>\n      <td>1</td>\n      <td>497</td>\n    </tr>\n    <tr>\n      <th>656</th>\n      <td>64.4</td>\n      <td>1</td>\n      <td>656</td>\n    </tr>\n    <tr>\n      <th>983</th>\n      <td>96.3</td>\n      <td>1</td>\n      <td>983</td>\n    </tr>\n    <tr>\n      <th>994</th>\n      <td>198.9</td>\n      <td>2</td>\n      <td>1988</td>\n    </tr>\n    <tr>\n      <th>1254</th>\n      <td>131.4</td>\n      <td>1</td>\n      <td>1254</td>\n    </tr>\n    <tr>\n      <th>1505</th>\n      <td>435.2</td>\n      <td>1</td>\n      <td>1505</td>\n    </tr>\n    <tr>\n      <th>2143</th>\n      <td>334.0</td>\n      <td>1</td>\n      <td>2143</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gift_by_singer.groupby('release_addresses').agg(\n",
    "    {'release_gboot': sum, 'signer': np.count_nonzero, 'release_addresses': sum})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:19:55.935455Z",
     "start_time": "2024-01-29T04:19:55.922329Z"
    }
   },
   "id": "566cddfbb3e84b5e",
   "execution_count": 4
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "signer                 bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6...\npassport_nicknames     [etwet46w4, fbfbhbvcccccc, blackhole666, proj3...\nproof_addresses                                                    59605\nremove_addresses                                                   21218\nclaim_addresses                                                    25266\nrelease_addresses                                                  27319\nclaim_gboot                                                      78597.2\nclaim_eth_gboot                                                  68580.5\nclaim_cosmos_gboot                                                5187.9\nclaim_osmosis_gboot                                               4005.5\nclaim_terra_gboot                                                  807.4\nreleasable_gboot                                                2971.848\nrelease_gboot                                                    23751.2\ndtype: object"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gift_by_singer.sum()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:19:56.289959Z",
     "start_time": "2024-01-29T04:19:56.284863Z"
    }
   },
   "id": "cbba36b84b4327f8",
   "execution_count": 5
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Transfers after Gift"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "1d400bf457ab766d"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "query_transfers = f'''\n",
    "WITH send as (SELECT address_from       as sender,\n",
    "                     address_to         as receiver,\n",
    "                     denom,\n",
    "                     sum(amount_coin)   as amount,\n",
    "                     count(amount_coin) as send_cnt\n",
    "              FROM (\n",
    "                       SELECT address_from,\n",
    "                              address_to,\n",
    "                              arrayJoin(JSONExtractArrayRaw(coins))       as coin,\n",
    "                              JSON_VALUE(coin, '$.denom')                 as denom,\n",
    "                              toInt64OrZero(JSON_VALUE(coin, '$.amount')) as amount_coin\n",
    "                       FROM `spacebox`.send_message\n",
    "                           PREWHERE send_message.height > 10800000)\n",
    "              GROUP BY (address_from,\n",
    "                        address_to,\n",
    "                        denom)\n",
    "              ORDER BY amount DESC),\n",
    "     ibc_transfers as (SELECT sender,\n",
    "                              receiver,\n",
    "                              denom,\n",
    "                              sum(amount_coin)   as amount,\n",
    "                              count(amount_coin) as send_cnt\n",
    "                       FROM (\n",
    "                                SELECT sender,\n",
    "                                       receiver,\n",
    "                                       JSON_VALUE(coin, '$.denom')                 as denom,\n",
    "                                       toInt64OrZero(JSON_VALUE(coin, '$.amount')) as amount_coin\n",
    "                                FROM `spacebox`.transfer_message\n",
    "                                    PREWHERE transfer_message.height > 10800000)\n",
    "                       GROUP BY (sender, receiver, denom)\n",
    "                       ORDER BY amount DESC),\n",
    "     all_transfers as (SELECT *\n",
    "                       FROM send\n",
    "\n",
    "                       UNION ALL\n",
    "\n",
    "                       SELECT *\n",
    "                       FROM ibc_transfers),\n",
    "     price as (SELECT 'boot' as denom, 1.0 as price\n",
    "               UNION ALL\n",
    "               SELECT 'hydrogen' as denom, 0.29 as price\n",
    "               UNION ALL\n",
    "               SELECT 'milliampere' as denom, 1545.9 as price\n",
    "               UNION ALL\n",
    "               SELECT 'millivolt' as denom, 14921.0 as price\n",
    "               UNION ALL\n",
    "               SELECT 'ibc/15E9C5CF5969080539DB395FA7D9C0868265217EFC528433671AAF9B1912D159' as denom, 799.0 as price\n",
    "               UNION ALL\n",
    "               SELECT 'ibc/13B2C536BB057AC79D5616B8EA1B9540EC1F2170718CAFF6F0083C966FFFED0B' as denom, 130.0 as price\n",
    "               UNION ALL\n",
    "               SELECT 'ibc/B6CAD3F7469F3FAD18ED2230A6C7B15E654AB2E1B66E1C70879C04FEF874A863' as denom, 0.00000018 as price)\n",
    "\n",
    "SELECT sender,\n",
    "       receiver,\n",
    "       denom,\n",
    "       amount,\n",
    "       send_cnt,\n",
    "       amount * price as value\n",
    "FROM all_transfers\n",
    "         ANY\n",
    "         LEFT JOIN price\n",
    "                   ON price.denom = all_transfers.denom\n",
    "ORDER BY value DESC'''\n",
    "\n",
    "transfers_df = query_to_df(query_transfers)\n",
    "transfers_df['denom'] = transfers_df['denom'].map(lambda x: IBC_COIN_NAMES[x] if x in IBC_COIN_NAMES.keys() else x)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:20:00.550498Z",
     "start_time": "2024-01-29T04:19:56.292559Z"
    }
   },
   "id": "ce544fe2d31c339",
   "execution_count": 6
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "                                               sender  \\\n0      bostrom1qs9w7ry45axfxjgxa4jmuhjthzfvj78sxh5p6e   \n1      bostrom1lsmmv6q0hayv9e9j3sjdaxean2wg77f0t0hth2   \n2      bostrom1xszmhkfjs3s00z2nvtn7evqxw3dtus6yr8e4pw   \n3      bostrom1lsmmv6q0hayv9e9j3sjdaxean2wg77f0t0hth2   \n4      bostrom19t8jeexz8ny0cjm9w7cn82lv6qe6tkqld29csn   \n...                                               ...   \n25460  bostrom1v6fnklkj5av596xrv9sucdvcgk7llhracu2yqm   \n25461  bostrom1zy553za8nenzukmv65240323jhuvxzym4pl6w9   \n25462  bostrom1cw4d6ayhdfk2wclvy8fvxff26l8naddqc5m3fu   \n25463  bostrom1pss5jer8r00zv6x562uttctug9u96f4kpqg44w   \n25464  bostrom1gdpk4xeyeqzdkdwc0c2zrd8ltrthnze3he54v6   \n\n                                                receiver  \\\n0      bostrom182jzjwdyl5fw43yujnlljddgtrkr04dpd30ywp...   \n1            osmo1lsmmv6q0hayv9e9j3sjdaxean2wg77f0q8sgll   \n2         bostrom19t8jeexz8ny0cjm9w7cn82lv6qe6tkqld29csn   \n3            osmo1lsmmv6q0hayv9e9j3sjdaxean2wg77f0q8sgll   \n4         bostrom1xszmhkfjs3s00z2nvtn7evqxw3dtus6yr8e4pw   \n...                                                  ...   \n25460       pussy1v6fnklkj5av596xrv9sucdvcgk7llhrajs5frj   \n25461       pussy1zy553za8nenzukmv65240323jhuvxzymldphdv   \n25462       pussy1cw4d6ayhdfk2wclvy8fvxff26l8naddqjc9u24   \n25463       pussy1pss5jer8r00zv6x562uttctug9u96f4ktvkck8   \n25464       evmos1yyqa5w0d9x8tzhjxfq4ttxaqfefu226f3mk7tz   \n\n                                                   denom               amount  \\\n0                                                   boot       97000000000000   \n1                                       uosmo in bostrom          68917273203   \n2                                               hydrogen       30000000000000   \n3                                                   boot        5059382739626   \n4                                       uosmo in bostrom          30000000000   \n...                                                  ...                  ...   \n25460                                   pussy in bostrom       77689238481900   \n25461                                   pussy in bostrom      142776946318859   \n25462                             liquidpussy in bostrom      154451960011957   \n25463                                   pussy in bostrom      202218100707996   \n25464  ibc/FA8CE16D380167E62FFD7D34BC0FEEA40DB5506D79...  5000000000000000000   \n\n       send_cnt         value  \n0             2  9.700000e+13  \n1          1543  8.959246e+12  \n2             3  8.700000e+12  \n3          2308  5.059383e+12  \n4             1  3.900000e+12  \n...         ...           ...  \n25460         1  0.000000e+00  \n25461         2  0.000000e+00  \n25462         4  0.000000e+00  \n25463         8  0.000000e+00  \n25464         1  0.000000e+00  \n\n[25465 rows x 6 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>sender</th>\n      <th>receiver</th>\n      <th>denom</th>\n      <th>amount</th>\n      <th>send_cnt</th>\n      <th>value</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>bostrom1qs9w7ry45axfxjgxa4jmuhjthzfvj78sxh5p6e</td>\n      <td>bostrom182jzjwdyl5fw43yujnlljddgtrkr04dpd30ywp...</td>\n      <td>boot</td>\n      <td>97000000000000</td>\n      <td>2</td>\n      <td>9.700000e+13</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>bostrom1lsmmv6q0hayv9e9j3sjdaxean2wg77f0t0hth2</td>\n      <td>osmo1lsmmv6q0hayv9e9j3sjdaxean2wg77f0q8sgll</td>\n      <td>uosmo in bostrom</td>\n      <td>68917273203</td>\n      <td>1543</td>\n      <td>8.959246e+12</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>bostrom1xszmhkfjs3s00z2nvtn7evqxw3dtus6yr8e4pw</td>\n      <td>bostrom19t8jeexz8ny0cjm9w7cn82lv6qe6tkqld29csn</td>\n      <td>hydrogen</td>\n      <td>30000000000000</td>\n      <td>3</td>\n      <td>8.700000e+12</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>bostrom1lsmmv6q0hayv9e9j3sjdaxean2wg77f0t0hth2</td>\n      <td>osmo1lsmmv6q0hayv9e9j3sjdaxean2wg77f0q8sgll</td>\n      <td>boot</td>\n      <td>5059382739626</td>\n      <td>2308</td>\n      <td>5.059383e+12</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>bostrom19t8jeexz8ny0cjm9w7cn82lv6qe6tkqld29csn</td>\n      <td>bostrom1xszmhkfjs3s00z2nvtn7evqxw3dtus6yr8e4pw</td>\n      <td>uosmo in bostrom</td>\n      <td>30000000000</td>\n      <td>1</td>\n      <td>3.900000e+12</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>25460</th>\n      <td>bostrom1v6fnklkj5av596xrv9sucdvcgk7llhracu2yqm</td>\n      <td>pussy1v6fnklkj5av596xrv9sucdvcgk7llhrajs5frj</td>\n      <td>pussy in bostrom</td>\n      <td>77689238481900</td>\n      <td>1</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>25461</th>\n      <td>bostrom1zy553za8nenzukmv65240323jhuvxzym4pl6w9</td>\n      <td>pussy1zy553za8nenzukmv65240323jhuvxzymldphdv</td>\n      <td>pussy in bostrom</td>\n      <td>142776946318859</td>\n      <td>2</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>25462</th>\n      <td>bostrom1cw4d6ayhdfk2wclvy8fvxff26l8naddqc5m3fu</td>\n      <td>pussy1cw4d6ayhdfk2wclvy8fvxff26l8naddqjc9u24</td>\n      <td>liquidpussy in bostrom</td>\n      <td>154451960011957</td>\n      <td>4</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>25463</th>\n      <td>bostrom1pss5jer8r00zv6x562uttctug9u96f4kpqg44w</td>\n      <td>pussy1pss5jer8r00zv6x562uttctug9u96f4ktvkck8</td>\n      <td>pussy in bostrom</td>\n      <td>202218100707996</td>\n      <td>8</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>25464</th>\n      <td>bostrom1gdpk4xeyeqzdkdwc0c2zrd8ltrthnze3he54v6</td>\n      <td>evmos1yyqa5w0d9x8tzhjxfq4ttxaqfefu226f3mk7tz</td>\n      <td>ibc/FA8CE16D380167E62FFD7D34BC0FEEA40DB5506D79...</td>\n      <td>5000000000000000000</td>\n      <td>1</td>\n      <td>0.000000e+00</td>\n    </tr>\n  </tbody>\n</table>\n<p>25465 rows × 6 columns</p>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transfers_df"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:20:00.560308Z",
     "start_time": "2024-01-29T04:20:00.554025Z"
    }
   },
   "id": "f9603d9d0f36c296",
   "execution_count": 7
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "                                               signer  \\\n0      bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6   \n1      bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6   \n2      bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6   \n3      bostrom1trt5q36aqdx6l2ca3r5hush2hm9vfu699j9ksn   \n5      bostrom1ulefdrkmfluv5zvhc4nwuw5vtnaspx8sh9wpk3   \n...                                               ...   \n18004  bostrom1ednc63kwckcz6arhzm28f7undd4gqlvygya0ks   \n18005  bostrom19jcktgz30hz8vd637cgcxmmqvrut3k0ky68fv3   \n18006  bostrom153cw09k759z52lkmh4xxsgy37gtt6splyf8388   \n18007  bostrom1gt4kqr6jsfd987qsgzj8wh7twg8es6ye50qglr   \n18008  bostrom19phgdjcswyf7q4x4ncwgde70rydpv5p90h9wyu   \n\n                                             receiver  release_addresses  \\\n0       cosmos1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwruw5uqsa               1505   \n1         osmo1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrux00sx0               1505   \n2       cosmos1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwruw5uqsa               1505   \n3      bostrom1082rt24yezp0qkln4h4r2xvx84re373qw8a29f               2143   \n5         osmo1ulefdrkmfluv5zvhc4nwuw5vtnaspx8sudfz7y               1254   \n...                                               ...                ...   \n18004                                             NaN                  0   \n18005                                             NaN                  0   \n18006                                             NaN                  0   \n18007                                             NaN                  0   \n18008                                             NaN                  0   \n\n       release_gboot         value             denom        amount  \\\n0              435.2  8.338380e+11  uatom in bostrom  1.043602e+09   \n1              435.2  3.414896e+10              boot  3.414896e+10   \n2              435.2  6.877418e+09              boot  6.877418e+09   \n3              334.0  3.340403e+11              boot  3.340403e+11   \n5              131.4  1.306055e+11              boot  1.306055e+11   \n...              ...           ...               ...           ...   \n18004            0.0           NaN               NaN           NaN   \n18005            0.0           NaN               NaN           NaN   \n18006            0.0           NaN               NaN           NaN   \n18007            0.0           NaN               NaN           NaN   \n18008            0.0           NaN               NaN           NaN   \n\n      receiver_network  \n0               cosmos  \n1                 osmo  \n2               cosmos  \n3              bostrom  \n5                 osmo  \n...                ...  \n18004             None  \n18005             None  \n18006             None  \n18007             None  \n18008             None  \n\n[15132 rows x 8 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>signer</th>\n      <th>receiver</th>\n      <th>release_addresses</th>\n      <th>release_gboot</th>\n      <th>value</th>\n      <th>denom</th>\n      <th>amount</th>\n      <th>receiver_network</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6</td>\n      <td>cosmos1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwruw5uqsa</td>\n      <td>1505</td>\n      <td>435.2</td>\n      <td>8.338380e+11</td>\n      <td>uatom in bostrom</td>\n      <td>1.043602e+09</td>\n      <td>cosmos</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6</td>\n      <td>osmo1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrux00sx0</td>\n      <td>1505</td>\n      <td>435.2</td>\n      <td>3.414896e+10</td>\n      <td>boot</td>\n      <td>3.414896e+10</td>\n      <td>osmo</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>bostrom1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwrud8gnw6</td>\n      <td>cosmos1l74ggk5tsn7d2l5qv8fxk3zvdc7fpwruw5uqsa</td>\n      <td>1505</td>\n      <td>435.2</td>\n      <td>6.877418e+09</td>\n      <td>boot</td>\n      <td>6.877418e+09</td>\n      <td>cosmos</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>bostrom1trt5q36aqdx6l2ca3r5hush2hm9vfu699j9ksn</td>\n      <td>bostrom1082rt24yezp0qkln4h4r2xvx84re373qw8a29f</td>\n      <td>2143</td>\n      <td>334.0</td>\n      <td>3.340403e+11</td>\n      <td>boot</td>\n      <td>3.340403e+11</td>\n      <td>bostrom</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>bostrom1ulefdrkmfluv5zvhc4nwuw5vtnaspx8sh9wpk3</td>\n      <td>osmo1ulefdrkmfluv5zvhc4nwuw5vtnaspx8sudfz7y</td>\n      <td>1254</td>\n      <td>131.4</td>\n      <td>1.306055e+11</td>\n      <td>boot</td>\n      <td>1.306055e+11</td>\n      <td>osmo</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>18004</th>\n      <td>bostrom1ednc63kwckcz6arhzm28f7undd4gqlvygya0ks</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>18005</th>\n      <td>bostrom19jcktgz30hz8vd637cgcxmmqvrut3k0ky68fv3</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>18006</th>\n      <td>bostrom153cw09k759z52lkmh4xxsgy37gtt6splyf8388</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>18007</th>\n      <td>bostrom1gt4kqr6jsfd987qsgzj8wh7twg8es6ye50qglr</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>18008</th>\n      <td>bostrom19phgdjcswyf7q4x4ncwgde70rydpv5p90h9wyu</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n<p>15132 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transfer_after_gift = \\\n",
    "    gift_by_singer[['signer', 'release_addresses', 'release_gboot']].merge(\n",
    "        transfers_df[['sender', 'receiver', 'value', 'denom', 'amount']], left_on='signer', right_on='sender', how='left')[[\n",
    "        'signer', 'receiver', 'release_addresses', 'release_gboot', 'value', 'denom', 'amount'\n",
    "    ]]\n",
    "transfer_after_gift['receiver_network'] = transfer_after_gift['receiver'].map(lambda x: x.split('1')[0] if type(x) == str else None)\n",
    "transfer_after_gift[~transfer_after_gift.receiver.isin(transfer_after_gift['signer'])]\n",
    "# transfer_after_gift"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:20:00.613643Z",
     "start_time": "2024-01-29T04:20:00.559245Z"
    }
   },
   "id": "55415f5a3d359cc2",
   "execution_count": 8
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "                                                                           amount  \\\nreceiver_network denom                                                              \nbostrom          boot                                                4.768857e+12   \n                 hydrogen                                            1.038138e+13   \n                 milliampere                                         1.000000e+03   \n                 millivolt                                           1.858310e+05   \n                 uatom in bostrom                                    1.045055e+06   \ncosmos           boot                                                5.960316e+10   \n                 hydrogen                                            2.008429e+10   \n                 ibc/CB41EF44BC15C8857B34EA151C8C480706E2A430A67...  8.575000e+05   \n                 millivolt                                           4.297000e+03   \n                 uatom in bostrom                                    8.255729e+09   \n                 uosmo in bostrom                                    5.737400e+07   \ndesmos           boot                                                2.663975e+06   \nevmos            boot                                                1.000001e+06   \n                 hydrogen                                            9.309372e+06   \n                 uatom in bostrom                                    6.100000e+05   \ngravity          boot                                                9.993797e+06   \n                 hydrogen                                            4.200000e+01   \n                 ibc/1DE3244B072A8939517C17C7B679F3B735B2D9AA6BE...  1.000000e+05   \n                 weth.grv in bostrom                                 5.953000e+18   \njuno             boot                                                1.000000e+04   \n                 hydrogen                                            4.200000e+01   \n                 uatom in bostrom                                    2.000000e+06   \n                 ujuno in bostrom                                    3.180603e+09   \nosmo             boot                                                1.666252e+13   \n                 deprecated uatom in bostrom                         1.826700e+08   \n                 hydrogen                                            9.893056e+12   \n                 ibc/29FC7F418F1EFB5DAB3DD089AADA1FE406DC78633BF...  1.000000e+03   \n                 ibc/5F78C42BCC76287AE6B3185C6C1455DFFF8D805B184...  6.975000e+06   \n                 ibc/ABF1AAE56C80610AEBAA8EF4DEF7D850E66F4F35AA5...  1.657612e+10   \n                 milliampere                                         1.089773e+08   \n                 millivolt                                           8.653062e+06   \n                 pool906F3CC4C0F4634031990DB81761BD390890F8A8A80...  1.600000e+01   \n                 pussy in bostrom                                    2.200000e+11   \n                 tocyb                                               2.260643e+09   \n                 uatom in bostrom                                    1.942116e+08   \n                 udsm in bostrom                                     4.803900e+07   \n                 uosmo in bostrom                                    2.540458e+10   \npussy            boot                                                1.768708e+10   \n                 hydrogen                                            1.591611e+10   \n                 ibc/47160B934644DED2F8E605B0521C6CC06F75A0465D8...  2.000000e+03   \n                 liquidpussy in bostrom                              9.300120e+08   \n                 milliampere                                         1.423000e+03   \n                 millivolt                                           5.450000e+02   \n                 pool70D7610CBA8E94B27BAD7806EBD826F5626C486BBF5...  2.401066e+08   \n                 poolE479A51F998E2979F89DA9AB897ED45A9AFE4E7DE32...  3.043116e+07   \n                 pussy in bostrom                                    4.935636e+14   \n                 tocyb                                               1.001062e+09   \n                 uatom in bostrom                                    7.861000e+06   \n                 uosmo in bostrom                                    1.000000e+05   \n\n                                                                       boot value  \nreceiver_network denom                                                             \nbostrom          boot                                                4.768857e+12  \n                 hydrogen                                            3.010600e+12  \n                 milliampere                                         1.545900e+06  \n                 millivolt                                           2.772784e+09  \n                 uatom in bostrom                                    8.349989e+08  \ncosmos           boot                                                5.960316e+10  \n                 hydrogen                                            5.824443e+09  \n                 ibc/CB41EF44BC15C8857B34EA151C8C480706E2A430A67...  0.000000e+00  \n                 millivolt                                           6.411554e+07  \n                 uatom in bostrom                                    6.596327e+12  \n                 uosmo in bostrom                                    7.458620e+09  \ndesmos           boot                                                2.663975e+06  \nevmos            boot                                                1.000001e+06  \n                 hydrogen                                            2.699718e+06  \n                 uatom in bostrom                                    4.873900e+08  \ngravity          boot                                                9.993797e+06  \n                 hydrogen                                            1.218000e+01  \n                 ibc/1DE3244B072A8939517C17C7B679F3B735B2D9AA6BE...  0.000000e+00  \n                 weth.grv in bostrom                                 1.071540e+12  \njuno             boot                                                1.000000e+04  \n                 hydrogen                                            1.218000e+01  \n                 uatom in bostrom                                    1.598000e+09  \n                 ujuno in bostrom                                    0.000000e+00  \nosmo             boot                                                1.666252e+13  \n                 deprecated uatom in bostrom                         0.000000e+00  \n                 hydrogen                                            2.868986e+12  \n                 ibc/29FC7F418F1EFB5DAB3DD089AADA1FE406DC78633BF...  0.000000e+00  \n                 ibc/5F78C42BCC76287AE6B3185C6C1455DFFF8D805B184...  0.000000e+00  \n                 ibc/ABF1AAE56C80610AEBAA8EF4DEF7D850E66F4F35AA5...  0.000000e+00  \n                 milliampere                                         1.684680e+11  \n                 millivolt                                           1.291123e+11  \n                 pool906F3CC4C0F4634031990DB81761BD390890F8A8A80...  0.000000e+00  \n                 pussy in bostrom                                    0.000000e+00  \n                 tocyb                                               0.000000e+00  \n                 uatom in bostrom                                    1.551751e+11  \n                 udsm in bostrom                                     0.000000e+00  \n                 uosmo in bostrom                                    3.302595e+12  \npussy            boot                                                1.768708e+10  \n                 hydrogen                                            4.615673e+09  \n                 ibc/47160B934644DED2F8E605B0521C6CC06F75A0465D8...  0.000000e+00  \n                 liquidpussy in bostrom                              0.000000e+00  \n                 milliampere                                         2.199816e+06  \n                 millivolt                                           8.131945e+06  \n                 pool70D7610CBA8E94B27BAD7806EBD826F5626C486BBF5...  0.000000e+00  \n                 poolE479A51F998E2979F89DA9AB897ED45A9AFE4E7DE32...  0.000000e+00  \n                 pussy in bostrom                                    0.000000e+00  \n                 tocyb                                               0.000000e+00  \n                 uatom in bostrom                                    6.280939e+09  \n                 uosmo in bostrom                                    1.300000e+07  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th></th>\n      <th>amount</th>\n      <th>boot value</th>\n    </tr>\n    <tr>\n      <th>receiver_network</th>\n      <th>denom</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th rowspan=\"5\" valign=\"top\">bostrom</th>\n      <th>boot</th>\n      <td>4.768857e+12</td>\n      <td>4.768857e+12</td>\n    </tr>\n    <tr>\n      <th>hydrogen</th>\n      <td>1.038138e+13</td>\n      <td>3.010600e+12</td>\n    </tr>\n    <tr>\n      <th>milliampere</th>\n      <td>1.000000e+03</td>\n      <td>1.545900e+06</td>\n    </tr>\n    <tr>\n      <th>millivolt</th>\n      <td>1.858310e+05</td>\n      <td>2.772784e+09</td>\n    </tr>\n    <tr>\n      <th>uatom in bostrom</th>\n      <td>1.045055e+06</td>\n      <td>8.349989e+08</td>\n    </tr>\n    <tr>\n      <th rowspan=\"6\" valign=\"top\">cosmos</th>\n      <th>boot</th>\n      <td>5.960316e+10</td>\n      <td>5.960316e+10</td>\n    </tr>\n    <tr>\n      <th>hydrogen</th>\n      <td>2.008429e+10</td>\n      <td>5.824443e+09</td>\n    </tr>\n    <tr>\n      <th>ibc/CB41EF44BC15C8857B34EA151C8C480706E2A430A677850D3772C8543BD92F3A</th>\n      <td>8.575000e+05</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>millivolt</th>\n      <td>4.297000e+03</td>\n      <td>6.411554e+07</td>\n    </tr>\n    <tr>\n      <th>uatom in bostrom</th>\n      <td>8.255729e+09</td>\n      <td>6.596327e+12</td>\n    </tr>\n    <tr>\n      <th>uosmo in bostrom</th>\n      <td>5.737400e+07</td>\n      <td>7.458620e+09</td>\n    </tr>\n    <tr>\n      <th>desmos</th>\n      <th>boot</th>\n      <td>2.663975e+06</td>\n      <td>2.663975e+06</td>\n    </tr>\n    <tr>\n      <th rowspan=\"3\" valign=\"top\">evmos</th>\n      <th>boot</th>\n      <td>1.000001e+06</td>\n      <td>1.000001e+06</td>\n    </tr>\n    <tr>\n      <th>hydrogen</th>\n      <td>9.309372e+06</td>\n      <td>2.699718e+06</td>\n    </tr>\n    <tr>\n      <th>uatom in bostrom</th>\n      <td>6.100000e+05</td>\n      <td>4.873900e+08</td>\n    </tr>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">gravity</th>\n      <th>boot</th>\n      <td>9.993797e+06</td>\n      <td>9.993797e+06</td>\n    </tr>\n    <tr>\n      <th>hydrogen</th>\n      <td>4.200000e+01</td>\n      <td>1.218000e+01</td>\n    </tr>\n    <tr>\n      <th>ibc/1DE3244B072A8939517C17C7B679F3B735B2D9AA6BE91CF5B04D01B645B33701</th>\n      <td>1.000000e+05</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>weth.grv in bostrom</th>\n      <td>5.953000e+18</td>\n      <td>1.071540e+12</td>\n    </tr>\n    <tr>\n      <th rowspan=\"4\" valign=\"top\">juno</th>\n      <th>boot</th>\n      <td>1.000000e+04</td>\n      <td>1.000000e+04</td>\n    </tr>\n    <tr>\n      <th>hydrogen</th>\n      <td>4.200000e+01</td>\n      <td>1.218000e+01</td>\n    </tr>\n    <tr>\n      <th>uatom in bostrom</th>\n      <td>2.000000e+06</td>\n      <td>1.598000e+09</td>\n    </tr>\n    <tr>\n      <th>ujuno in bostrom</th>\n      <td>3.180603e+09</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th rowspan=\"14\" valign=\"top\">osmo</th>\n      <th>boot</th>\n      <td>1.666252e+13</td>\n      <td>1.666252e+13</td>\n    </tr>\n    <tr>\n      <th>deprecated uatom in bostrom</th>\n      <td>1.826700e+08</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>hydrogen</th>\n      <td>9.893056e+12</td>\n      <td>2.868986e+12</td>\n    </tr>\n    <tr>\n      <th>ibc/29FC7F418F1EFB5DAB3DD089AADA1FE406DC78633BFF3D3E2B3D767368AC083D</th>\n      <td>1.000000e+03</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>ibc/5F78C42BCC76287AE6B3185C6C1455DFFF8D805B1847F94B9B625384B93885C7</th>\n      <td>6.975000e+06</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>ibc/ABF1AAE56C80610AEBAA8EF4DEF7D850E66F4F35AA5615999DAB4DA24C7E4692</th>\n      <td>1.657612e+10</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>milliampere</th>\n      <td>1.089773e+08</td>\n      <td>1.684680e+11</td>\n    </tr>\n    <tr>\n      <th>millivolt</th>\n      <td>8.653062e+06</td>\n      <td>1.291123e+11</td>\n    </tr>\n    <tr>\n      <th>pool906F3CC4C0F4634031990DB81761BD390890F8A8A80460EBDC6B151254DE7D1D</th>\n      <td>1.600000e+01</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>pussy in bostrom</th>\n      <td>2.200000e+11</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>tocyb</th>\n      <td>2.260643e+09</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>uatom in bostrom</th>\n      <td>1.942116e+08</td>\n      <td>1.551751e+11</td>\n    </tr>\n    <tr>\n      <th>udsm in bostrom</th>\n      <td>4.803900e+07</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>uosmo in bostrom</th>\n      <td>2.540458e+10</td>\n      <td>3.302595e+12</td>\n    </tr>\n    <tr>\n      <th rowspan=\"12\" valign=\"top\">pussy</th>\n      <th>boot</th>\n      <td>1.768708e+10</td>\n      <td>1.768708e+10</td>\n    </tr>\n    <tr>\n      <th>hydrogen</th>\n      <td>1.591611e+10</td>\n      <td>4.615673e+09</td>\n    </tr>\n    <tr>\n      <th>ibc/47160B934644DED2F8E605B0521C6CC06F75A0465D8B7F2846A318B3B45D4834</th>\n      <td>2.000000e+03</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>liquidpussy in bostrom</th>\n      <td>9.300120e+08</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>milliampere</th>\n      <td>1.423000e+03</td>\n      <td>2.199816e+06</td>\n    </tr>\n    <tr>\n      <th>millivolt</th>\n      <td>5.450000e+02</td>\n      <td>8.131945e+06</td>\n    </tr>\n    <tr>\n      <th>pool70D7610CBA8E94B27BAD7806EBD826F5626C486BBF5C490D1463D72314353C66</th>\n      <td>2.401066e+08</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>poolE479A51F998E2979F89DA9AB897ED45A9AFE4E7DE32BE3D30FB456E58F80D4E9</th>\n      <td>3.043116e+07</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>pussy in bostrom</th>\n      <td>4.935636e+14</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>tocyb</th>\n      <td>1.001062e+09</td>\n      <td>0.000000e+00</td>\n    </tr>\n    <tr>\n      <th>uatom in bostrom</th>\n      <td>7.861000e+06</td>\n      <td>6.280939e+09</td>\n    </tr>\n    <tr>\n      <th>uosmo in bostrom</th>\n      <td>1.000000e+05</td>\n      <td>1.300000e+07</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transfer_after_gift_agg = transfer_after_gift[~transfer_after_gift.receiver.isin(transfer_after_gift['signer'])].groupby(['receiver_network', 'denom'])[['amount', 'value']].sum().rename(columns={'value': 'boot value'})\n",
    "transfer_after_gift_agg"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:20:00.614655Z",
     "start_time": "2024-01-29T04:20:00.584768Z"
    }
   },
   "id": "21725af7d5b5cae5",
   "execution_count": 9
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-29T04:20:00.614848Z",
     "start_time": "2024-01-29T04:20:00.598184Z"
    }
   },
   "id": "4f9faaf1587dff3c",
   "execution_count": 9
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}