GetVeloData.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "72c7d465",
   "metadata": {},
   "outputs": [],
   "source": [
    "from shroomdk import ShroomDK\n",
    "import json\n",
    "import pandas as pd \n",
    "\n",
    "sdk = ShroomDK(\"5c0c5e4b-47eb-4cc2-835d-a2ddad6d1a60\")\n",
    "\n",
    "\n",
    "table = \"optimism.velodrome.ez_lp_actions\"\n",
    "\n",
    "df_list = []\n",
    "\n",
    "\n",
    "assets = ['USDC', 'WETH', 'DAI', 'sUSD', 'OP', 'FRAX', 'DOLA', 'sETH', 'MAI', 'wstETH', 'VELO']\n",
    "pools = pd.read_csv('veloPools.csv')\n",
    "\n",
    "for assetA in assets : \n",
    "    for assetB in assets : \n",
    "        if len(pools[(pools.TOKEN0_SYMBOL == assetA) & (pools.TOKEN1_SYMBOL == assetB)]) > 0 :\n",
    "            pairs.append(\"'\" + assetA + \"', '\" + assetB + \"'\")\n",
    "            \n",
    "pairs = []\n",
    "\n",
    "minAmount = 10000\n",
    "\n",
    "\n",
    "        \n",
    "\n",
    "def getLPData(table, Years, Months):\n",
    "    tableName = table.replace(\".\", \"_\")\n",
    "\n",
    "    sql = f\"\"\"\n",
    "\n",
    "        with raw as (\n",
    "            select \n",
    "                ORIGIN_FROM_ADDRESS as user,\n",
    "                sum(TOKEN0_AMOUNT_USD) as token0Amt,\n",
    "                sum(TOKEN1_AMOUNT_USD) as token1Amt\n",
    "\n",
    "              from optimism.velodrome.ez_lp_actions\n",
    "\n",
    "            where TOKEN0_AMOUNT_USD > {minAmount} or TOKEN1_AMOUNT_USD > {minAmount}\n",
    "\n",
    "            group by user \n",
    "        ) \n",
    "\n",
    "        SELECT * \n",
    "        FROM {table}\n",
    "        WHERE  \n",
    "            ORIGIN_FROM_ADDRESS IN (SELECT user FROM raw) and \n",
    "            TOKEN0_SYMBOL in ( 'USDC', 'WETH', 'DAI', 'sUSD', 'OP', 'FRAX', 'DOLA', 'sETH', 'MAI', 'wstETH', 'VELO' ) and\n",
    "            TOKEN1_SYMBOL in ( 'USDC', 'WETH', 'DAI', 'sUSD', 'OP', 'FRAX', 'DOLA', 'sETH', 'MAI', 'wstETH', 'VELO' ) \n",
    "\n",
    "\n",
    "    \"\"\"\n",
    "\n",
    "    results = sdk.query(sql)\n",
    "    data = json.loads(results.json())\n",
    "    df = pd.DataFrame(data['records'])\n",
    "    print(\"Total Records : \" + str(len(df)))\n",
    "\n",
    "        \n",
    "    return df \n",
    "    #finalDf.to_csv(tableName + \".csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7029366e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Records : 23278\n"
     ]
    }
   ],
   "source": [
    "df = getLPData(table, Years, Months)\n",
    "df.to_csv(\"VeloLPData.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5db3be6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def getTradeData():\n",
    "    print(\"Grabbing Trade Data For Velo\" )\n",
    "    sql = f\"\"\"\n",
    "        select \n",
    "            min(BLOCK_TIMESTAMP) as first_trade,\n",
    "            max(BLOCK_TIMESTAMP) as last_trade,\n",
    "            sum(AMOUNT_IN_USD) as totalVolume, \n",
    "            avg(AMOUNT_IN_USD) as avgTradeSize,\n",
    "            count(*) as total_trades,  \n",
    "            ORIGIN_FROM_ADDRESS as user, \n",
    "            LP_FEE_SYMBOL as pair\n",
    "\n",
    "        from optimism.velodrome.ez_swaps\n",
    "        \n",
    "        where AMOUNT_IN_USD > 5000 \n",
    "        \n",
    "        group by \n",
    "          user,\n",
    "          pair \n",
    "\n",
    "    \"\"\"\n",
    "\n",
    "    results = sdk.query(sql)\n",
    "    data = json.loads(results.json())\n",
    "    df = pd.DataFrame(data['records'])\n",
    "    print(\"Total Records : \" + str(len(df)))\n",
    "\n",
    "        \n",
    "    #finalDf = pd.concat(df_list)    \n",
    "    return df \n",
    "    #finalDf.to_csv(tableName + \".csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fb38dccb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Grabbing Trade Data For Velo\n",
      "Total Records : 7656\n"
     ]
    }
   ],
   "source": [
    "tradeDf = getTradeData()\n",
    "tradeDf.to_csv(\"tradeData.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9373e3d4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}