nft/NFT analysis.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "* Updated to GDS 2.0 version\n",
        "* Link to original blog post: https://towardsdatascience.com/exploring-the-nft-transaction-with-neo4j-cba80ead7e0b"
      ],
      "metadata": {
        "id": "Av0GVkuLV6NB"
      },
      "id": "Av0GVkuLV6NB"
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "08a820f8",
      "metadata": {
        "id": "08a820f8"
      },
      "outputs": [],
      "source": [
        "from neo4j import GraphDatabase\n",
        "import pandas as pd\n",
        "\n",
        "host = 'bolt://localhost:7687'\n",
        "user = 'neo4j'\n",
        "password = 'letmein'\n",
        "driver = GraphDatabase.driver(host,auth=(user, password))\n",
        "\n",
        "def neo4j_query(query, params=None):\n",
        "    with driver.session() as session:\n",
        "        result = session.run(query, params)\n",
        "        return pd.DataFrame([r.values() for r in result], columns=result.keys())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "0b7a8826",
      "metadata": {
        "id": "0b7a8826",
        "outputId": "9ba2ca35-8760-46b7-b6e5-1957bb47403c"
      },
      "outputs": [
        {
          "data": {
            "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>pageSize</th>\n",
              "      <th>totalTime</th>\n",
              "      <th>transactionWasTerminated</th>\n",
              "      <th>nodesPerPage</th>\n",
              "      <th>nodesTotal</th>\n",
              "      <th>nodePages</th>\n",
              "      <th>nodesTime</th>\n",
              "      <th>relsPerPage</th>\n",
              "      <th>relsTotal</th>\n",
              "      <th>relPages</th>\n",
              "      <th>...</th>\n",
              "      <th>stringPropRecordsTotal</th>\n",
              "      <th>stringPropPages</th>\n",
              "      <th>stringPropsTime</th>\n",
              "      <th>arrayPropsPerPage</th>\n",
              "      <th>arrayPropRecordsTotal</th>\n",
              "      <th>arrayPropPages</th>\n",
              "      <th>arrayPropsTime</th>\n",
              "      <th>indexesLoaded</th>\n",
              "      <th>indexPages</th>\n",
              "      <th>indexTime</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>8192</td>\n",
              "      <td>4058</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>11286844</td>\n",
              "      <td>24576</td>\n",
              "      <td>108</td>\n",
              "      <td>0</td>\n",
              "      <td>22891323</td>\n",
              "      <td>106496</td>\n",
              "      <td>...</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>False</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1 rows × 32 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "   pageSize  totalTime  transactionWasTerminated  nodesPerPage  nodesTotal  \\\n",
              "0      8192       4058                     False             0    11286844   \n",
              "\n",
              "   nodePages  nodesTime  relsPerPage  relsTotal  relPages  ...  \\\n",
              "0      24576        108            0   22891323    106496  ...   \n",
              "\n",
              "   stringPropRecordsTotal  stringPropPages  stringPropsTime  \\\n",
              "0                       0                0                0   \n",
              "\n",
              "   arrayPropsPerPage  arrayPropRecordsTotal  arrayPropPages  arrayPropsTime  \\\n",
              "0                  0                      0               0               0   \n",
              "\n",
              "   indexesLoaded  indexPages  indexTime  \n",
              "0          False           0          0  \n",
              "\n",
              "[1 rows x 32 columns]"
            ]
          },
          "execution_count": 2,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "CALL apoc.warmup.run\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "78c05418",
      "metadata": {
        "id": "78c05418"
      },
      "source": [
        "# Exploring the NFT transaction with Neo4j\n",
        "## Exploratory graph analysis of 6 million NFT transactions\n",
        "A couple of months ago, an article in Nature was published that maps 6.1 million transactions between June 2017 and April 2021 obtained primarily from Ethereum and WAX blockchains. It is an exciting article that analyzes the statistical properties of the market as well as some network characteristics. The authors also made the underlying NFT transaction dataset available under the CC-BY 4.0 license.\n",
        "Transactions can be described as links between the traders, so what better way to represent the data than a graph. First, Michael Hunger and I imported the dataset into Neo4j, a native graph database. Then I performed a simple exploratory graph analysis to better understand the underlying dataset, which I will demonstrate in this blog post.\n",
        "Setting up the environment\n",
        "First, you need to download and install the Neo4j Desktop application. The dataset is available as a database dump, which you can download by clicking this link. You need to copy the file to the Neo4j Desktop files folder and then select Create new DBMS from dump.\n",
        "\n",
        "It is advisable to choose the latest version of the Neo4j database available. Next, you will also need to install the APOC and GDS libraries.\n",
        "\n",
        "As this is a slightly larger dataset, increasing the heap and page cache memory allocation is also recommended. I chose 4GB of memory for heap allocation and 2GB for the page cache.\n",
        "\n",
        "Congratulations, you have successfully prepared the Neo4j environment.\n",
        "## Graph schema model\n",
        "\n",
        "The dataset revolves around transactions between traders. Each transaction has a date and the price in crypto and USD. As this is a dataset of NFT transactions, each transaction describes which NFT was sold. Interestingly, an image URL is available for most NFTs so that you can inspect the image sold on the internet. Some of the NFTs have up to four available image URLs for redundancy. We also know if an NFT is a part of a collection and which category it belongs to.\n",
        "## Exploratory graph analysis\n",
        "Now we can begin with the exploratory graph analysis. As always, I have prepared a Jupyter notebook that contains all the queries in this post.\n",
        "First, we will evaluate the number of nodes and relationships with the `apoc.meta.stats` procedure."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "e45aa900",
      "metadata": {
        "id": "e45aa900",
        "outputId": "9bc4890a-54ed-4124-b99e-f73a109be487"
      },
      "outputs": [
        {
          "data": {
            "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>nodeCount</th>\n",
              "      <th>relCount</th>\n",
              "      <th>labels</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>11286844</td>\n",
              "      <td>22891323</td>\n",
              "      <td>{'Category': 6, 'Transaction': 6071027, 'Trade...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   nodeCount  relCount                                             labels\n",
              "0   11286844  22891323  {'Category': 6, 'Transaction': 6071027, 'Trade..."
            ]
          },
          "execution_count": 3,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "CALL apoc.meta.stats\n",
        "YIELD nodeCount, relCount, labels\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "79798c36",
      "metadata": {
        "id": "79798c36"
      },
      "source": [
        "There are around 11 million nodes and 23 million relationships in the graph. The dataset contains 6 million transactions for around 4.6 million NFTs. The NFTs are part of 4600 collections and 6 categories.\n",
        "Now we will dig deeper and start examining the count of transactions and total volume and the average price of NFTs by year."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "d271e492",
      "metadata": {
        "id": "d271e492",
        "outputId": "110cd829-38a8-448a-c6a3-4d70e6b99f4c"
      },
      "outputs": [
        {
          "data": {
            "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>year</th>\n",
              "      <th>transactions</th>\n",
              "      <th>totalVolume</th>\n",
              "      <th>averagePrice</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2017</td>\n",
              "      <td>253100</td>\n",
              "      <td>1.829279e+07</td>\n",
              "      <td>72.274933</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2018</td>\n",
              "      <td>449373</td>\n",
              "      <td>1.667865e+07</td>\n",
              "      <td>37.118441</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2019</td>\n",
              "      <td>746489</td>\n",
              "      <td>1.867730e+07</td>\n",
              "      <td>25.086969</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020</td>\n",
              "      <td>1245954</td>\n",
              "      <td>7.762350e+07</td>\n",
              "      <td>62.551672</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021</td>\n",
              "      <td>3376111</td>\n",
              "      <td>7.559576e+08</td>\n",
              "      <td>223.996997</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   year  transactions   totalVolume  averagePrice\n",
              "0  2017        253100  1.829279e+07     72.274933\n",
              "1  2018        449373  1.667865e+07     37.118441\n",
              "2  2019        746489  1.867730e+07     25.086969\n",
              "3  2020       1245954  7.762350e+07     62.551672\n",
              "4  2021       3376111  7.559576e+08    223.996997"
            ]
          },
          "execution_count": 4,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (t:Transaction)\n",
        "RETURN t.Datetime_updated.year AS year, \n",
        "       count(*) AS transactions, \n",
        "       sum(t.Price_USD) AS totalVolume, \n",
        "       avg(t.Price_USD) AS averagePrice\n",
        "ORDER BY year \n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "70eb2b83",
      "metadata": {
        "id": "70eb2b83"
      },
      "source": [
        "In 2018, there were only 450 thousand transactions, whereas, in 2021, there were almost three and a half million transactions till April. Over the years, the average price and the total volume have been rising to nearly 750 million USD in the first four months of 2021. There was definitely a boom in the NFT world.\n",
        "Next, we will evaluate how many transactions have been priced under or above one dollar."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "5e67fd78",
      "metadata": {
        "id": "5e67fd78",
        "outputId": "f7a6fdd9-1789-4209-d177-33ec4eca4227"
      },
      "outputs": [
        {
          "data": {
            "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>moreThanDollar</th>\n",
              "      <th>count</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>False</td>\n",
              "      <td>2730965</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>True</td>\n",
              "      <td>3331779</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   moreThanDollar    count\n",
              "0           False  2730965\n",
              "1            True  3331779"
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (t:Transaction)\n",
        "WHERE exists(t.Price_USD)\n",
        "RETURN CASE WHEN t.Price_USD > 1 THEN true ELSE false END AS moreThanDollar, count(*) AS count\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "86145a40",
      "metadata": {
        "id": "86145a40"
      },
      "source": [
        "Around 55% percent of the transactions have been priced over one dollar. That leaves about 2.7 million transactions that were under a dollar.\n",
        "It seems that the high-value transactions are not that frequent, so when some occur, it makes the news. Let's examine the distribution of transactions over one dollar with the `apoc.agg.statistics` procedure."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "c9de24b9",
      "metadata": {
        "id": "c9de24b9",
        "outputId": "66e00df8-ae75-4600-f8ca-5014cf8bde35"
      },
      "outputs": [
        {
          "data": {
            "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>result</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>{'total': 3331779, 'min': 1.0000050750633935, ...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                              result\n",
              "0  {'total': 3331779, 'min': 1.0000050750633935, ..."
            ]
          },
          "execution_count": 6,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (t:Transaction)\n",
        "WHERE exists(t.Price_USD) AND t.Price_USD > 1\n",
        "RETURN apoc.agg.statistics(t.Price_USD) AS result\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "50cfd17b",
      "metadata": {
        "id": "50cfd17b"
      },
      "source": [
        "The average price of transactions over one dollar is 266 USD. On the other hand, 99 percent of transactions were under 3349 USD. That means that only a tiny percent of transactions were  very expensive, with the highest value of transaction being 7.5 million USD.\n",
        "We can examine which NFTs were sold at the highest price with the following Cypher statement."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "bfaf887b",
      "metadata": {
        "id": "bfaf887b",
        "outputId": "14385a69-6f75-44b2-e420-bddfbe2d982c"
      },
      "outputs": [
        {
          "data": {
            "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>token_id</th>\n",
              "      <th>image_url</th>\n",
              "      <th>price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>('Cryptopunks', '7804')</td>\n",
              "      <td>https://lh3.googleusercontent.com/KPQPI9GRwIMP...</td>\n",
              "      <td>7.501893e+06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>('Cryptopunks', '3100')</td>\n",
              "      <td>https://lh3.googleusercontent.com/q9gazeJi0cEH...</td>\n",
              "      <td>7.501893e+06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>('Somnium-space', '0')</td>\n",
              "      <td>https://lh3.googleusercontent.com/MWyOpZRNd0Ln...</td>\n",
              "      <td>2.684347e+06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>('Cryptopunks', '3011')</td>\n",
              "      <td>https://lh3.googleusercontent.com/tblRChM9Ych3...</td>\n",
              "      <td>1.723131e+06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>('Superrare', '14')</td>\n",
              "      <td>https://lh3.googleusercontent.com/Fzc7-PJ-e1YC...</td>\n",
              "      <td>1.643615e+06</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                  token_id                                          image_url  \\\n",
              "0  ('Cryptopunks', '7804')  https://lh3.googleusercontent.com/KPQPI9GRwIMP...   \n",
              "1  ('Cryptopunks', '3100')  https://lh3.googleusercontent.com/q9gazeJi0cEH...   \n",
              "2   ('Somnium-space', '0')  https://lh3.googleusercontent.com/MWyOpZRNd0Ln...   \n",
              "3  ('Cryptopunks', '3011')  https://lh3.googleusercontent.com/tblRChM9Ych3...   \n",
              "4      ('Superrare', '14')  https://lh3.googleusercontent.com/Fzc7-PJ-e1YC...   \n",
              "\n",
              "          price  \n",
              "0  7.501893e+06  \n",
              "1  7.501893e+06  \n",
              "2  2.684347e+06  \n",
              "3  1.723131e+06  \n",
              "4  1.643615e+06  "
            ]
          },
          "execution_count": 7,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (n:NFT)<-[:FOR_NFT]-(t:Transaction)\n",
        "WHERE exists(t.Price_USD)\n",
        "WITH n, t.Price_USD as price\n",
        "ORDER BY price DESC LIMIT 5\n",
        "RETURN n.ID_token as token_id, n.Image_url_1 as image_url, price\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "b12b33e7",
      "metadata": {
        "id": "b12b33e7"
      },
      "source": [
        "As a data analyst, I am very suspicious that two NFTs were valued at identical prices, and on top of that, they are both for 7.5 million USD. As we have the image URL available, you can inspect which images were valued at over a million USD. It looks like Cryptopunks collectibles are highly valued.\n",
        "We can dig deeper and inspect which accounts were involved in the 7.5 million USD transactions."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "699fd194",
      "metadata": {
        "id": "699fd194",
        "outputId": "be0abb2b-27e1-4abd-f292-cf0e34fe365e"
      },
      "outputs": [
        {
          "data": {
            "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>buyer</th>\n",
              "      <th>seller</th>\n",
              "      <th>date</th>\n",
              "      <th>price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0x0000000000000000000000000000000000000000</td>\n",
              "      <td>0x6611fe71c233e4e7510b2795c242c9a57790b376</td>\n",
              "      <td>2021-03-11T20:57:34.000000000+00:00</td>\n",
              "      <td>7501893.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>0xf4b4a58974524e183c275f3c6ea895bc2368e738</td>\n",
              "      <td>0x03911fecabd6b4809c88e2e6eb856ec932b2ee3e</td>\n",
              "      <td>2021-03-11T00:49:11.000000000+00:00</td>\n",
              "      <td>7501893.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                        buyer  \\\n",
              "0  0x0000000000000000000000000000000000000000   \n",
              "1  0xf4b4a58974524e183c275f3c6ea895bc2368e738   \n",
              "\n",
              "                                       seller  \\\n",
              "0  0x6611fe71c233e4e7510b2795c242c9a57790b376   \n",
              "1  0x03911fecabd6b4809c88e2e6eb856ec932b2ee3e   \n",
              "\n",
              "                                  date      price  \n",
              "0  2021-03-11T20:57:34.000000000+00:00  7501893.0  \n",
              "1  2021-03-11T00:49:11.000000000+00:00  7501893.0  "
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (n:NFT)<-[:FOR_NFT]-(t:Transaction),\n",
        "      (t)<-[:BOUGHT]-(buyer),\n",
        "      (t)<-[:SOLD]-(seller)\n",
        "WHERE t.Price_USD = 7501893.0\n",
        "RETURN buyer.address AS buyer, \n",
        "       seller.address AS seller, \n",
        "       t.Datetime_updated_seconds AS date, \n",
        "       t.Price_USD AS price\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "dee16865",
      "metadata": {
        "id": "dee16865"
      },
      "source": [
        "What a strange coincidence that both transactions were executed on the same day, only around 19 hours in between. The buyers and sellers seem unrelated, although the address that contains all zeroes caught my eye. After some investigating, I've learned that when someone mints an NFT on the OpenSea platform, the null address always shows up as a creator. On the other hand, you could also send an NFT to the null address to effectively delete the NFT. At least that's why some of the posts on the internet claim. After I put on my Sherlock Holmes hat, I found the NullAddress profile on OpenSea, where you can buy NFTs from them. This left me even more confused as I would assume that selling an NFT to the null address would effectively burn the NFT token, but who would send a 7.5 million USD worth NFT to burn it. Perhaps I just misunderstood it all, as this is my first analysis of the NFT ecosystem, and I have no prior knowledge of it. In any case, it all seems a bit suspicious. The two transactions have an identical price and were executed on the same day. I've checked if the three addresses have any transactions between each other, but the dataset contains no such transactions.\n",
        "\n",
        "\n",
        "Moving on, we will evaluate the NFT collections and their average cost."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "bd0da3e5",
      "metadata": {
        "id": "bd0da3e5",
        "outputId": "2a6c796c-9d21-47e4-8955-922a9976246b"
      },
      "outputs": [
        {
          "data": {
            "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>collection</th>\n",
              "      <th>averagePrice</th>\n",
              "      <th>numberOfNfts</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Saturdaynightlive</td>\n",
              "      <td>360856.408928</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Appresidential</td>\n",
              "      <td>181832.879943</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Trippderrickbarnesxflipkick</td>\n",
              "      <td>118838.578811</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Fairumnft</td>\n",
              "      <td>99849.750000</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Chainsaw</td>\n",
              "      <td>96546.024285</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                    collection   averagePrice  numberOfNfts\n",
              "0            Saturdaynightlive  360856.408928             1\n",
              "1               Appresidential  181832.879943             1\n",
              "2  Trippderrickbarnesxflipkick  118838.578811             1\n",
              "3                    Fairumnft   99849.750000             1\n",
              "4                     Chainsaw   96546.024285            12"
            ]
          },
          "execution_count": 9,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (collection)<-[:IN_COLLECTION]-(n:NFT)<-[:FOR_NFT]-(t:Transaction)\n",
        "WHERE exists (t.Price_USD)\n",
        "RETURN collection.Collection AS collection, \n",
        "       avg(t.Price_USD) AS averagePrice, \n",
        "       count(distinct n) AS numberOfNfts\n",
        "ORDER BY averagePrice DESC\n",
        "LIMIT 5\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "91278cba",
      "metadata": {
        "id": "91278cba"
      },
      "source": [
        "Most of the highest-value collections have only a single NFT in our dataset. Interestingly, the Cryptopunks don't even make it to the top five, even though some million USD transactions are around them.\n",
        "Next, we will explore which traders executed the most transactions."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "8b53c380",
      "metadata": {
        "id": "8b53c380",
        "outputId": "8e2b9a5a-3337-4d61-903f-c10eed704426"
      },
      "outputs": [
        {
          "data": {
            "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>username</th>\n",
              "      <th>bought</th>\n",
              "      <th>sold</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0x76481caa104b5f6bccb540dae4cefaf1c398ebea</td>\n",
              "      <td>130231</td>\n",
              "      <td>192586</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>0x327305a797d92a39cee1a225d7e2a1cc42b1a8fa</td>\n",
              "      <td>0</td>\n",
              "      <td>149142</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>0x4FabDA</td>\n",
              "      <td>28</td>\n",
              "      <td>49338</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>0xfc624f8f58db41bdb95aedee1de3c1cf047105f1</td>\n",
              "      <td>1976</td>\n",
              "      <td>43571</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>StrongHands</td>\n",
              "      <td>43384</td>\n",
              "      <td>437</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                     username  bought    sold\n",
              "0  0x76481caa104b5f6bccb540dae4cefaf1c398ebea  130231  192586\n",
              "1  0x327305a797d92a39cee1a225d7e2a1cc42b1a8fa       0  149142\n",
              "2                                    0x4FabDA      28   49338\n",
              "3  0xfc624f8f58db41bdb95aedee1de3c1cf047105f1    1976   43571\n",
              "4                                 StrongHands   43384     437"
            ]
          },
          "execution_count": 10,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (t:Trader)\n",
        "RETURN coalesce(t.username, t.address) AS username,\n",
        "       size((t)-[:BOUGHT]->()) AS bought,\n",
        "       size((t)-[:SOLD]->()) AS sold\n",
        "ORDER BY bought + sold desc LIMIT 5;\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "18acad80",
      "metadata": {
        "id": "18acad80"
      },
      "source": [
        "The top five traders have bought or sold more than 40 thousand NFTs. Since all the data is available on the internet as well, we can look at the top profile. It seems they haven't made a single transactions since April 2020. On the other hand, StrongHands profile on OpenSea has 17 thousand NFTs one could purchase.\n",
        "We have looked at traders with the highest transactions count, and now we will look at traders with the highest volume in USD."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "6bd6467d",
      "metadata": {
        "id": "6bd6467d",
        "outputId": "e5225593-05df-40a1-d14f-88d3816729d2"
      },
      "outputs": [
        {
          "data": {
            "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>username</th>\n",
              "      <th>address</th>\n",
              "      <th>boughtVolume</th>\n",
              "      <th>soldVolume</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>NullAddress</td>\n",
              "      <td>0x0000000000000000000000000000000000000000</td>\n",
              "      <td>5.506430e+07</td>\n",
              "      <td>3.142260e+03</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>None</td>\n",
              "      <td>0x327305a797d92a39cee1a225d7e2a1cc42b1a8fa</td>\n",
              "      <td>0.000000e+00</td>\n",
              "      <td>2.660329e+07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>SethS</td>\n",
              "      <td>0x6611fe71c233e4e7510b2795c242c9a57790b376</td>\n",
              "      <td>8.304005e+05</td>\n",
              "      <td>1.314407e+07</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Pranksy</td>\n",
              "      <td>0xd387a6e4e84a6c86bd90c158c6028a58cc8ac459</td>\n",
              "      <td>3.931823e+06</td>\n",
              "      <td>9.985689e+06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>SomniumSpace</td>\n",
              "      <td>0xb98cdacd006b9d47c37ca63cc86f916ee23fc550</td>\n",
              "      <td>1.166480e+03</td>\n",
              "      <td>1.310994e+07</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       username                                     address  boughtVolume  \\\n",
              "0   NullAddress  0x0000000000000000000000000000000000000000  5.506430e+07   \n",
              "1          None  0x327305a797d92a39cee1a225d7e2a1cc42b1a8fa  0.000000e+00   \n",
              "2         SethS  0x6611fe71c233e4e7510b2795c242c9a57790b376  8.304005e+05   \n",
              "3       Pranksy  0xd387a6e4e84a6c86bd90c158c6028a58cc8ac459  3.931823e+06   \n",
              "4  SomniumSpace  0xb98cdacd006b9d47c37ca63cc86f916ee23fc550  1.166480e+03   \n",
              "\n",
              "     soldVolume  \n",
              "0  3.142260e+03  \n",
              "1  2.660329e+07  \n",
              "2  1.314407e+07  \n",
              "3  9.985689e+06  \n",
              "4  1.310994e+07  "
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (t:Trader)\n",
        "OPTIONAL MATCH (t)-[:BOUGHT]->(bt)\n",
        "WITH t, sum(bt.Price_USD) AS boughtVolume\n",
        "OPTIONAL MATCH (t)-[:SOLD]->(st)\n",
        "WITH t, boughtVolume, sum(st.Price_USD) AS soldVolume\n",
        "RETURN t.username AS username, t.address AS address,boughtVolume, soldVolume\n",
        "ORDER BY boughtVolume + soldVolume\n",
        "DESC LIMIT 5\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "13d4648e",
      "metadata": {
        "id": "13d4648e"
      },
      "source": [
        "The highest volume has the NullAddress account. I have no idea why it has over 50 million USD in buyings. Even before, we had an example where someone sent an NFT to NullAddress for 4200 ETH. Maybe you could pick an arbitrary number of the transaction amount when you burn the NFT. Perhaps some experts can help me out with this.\n",
        "On the other hand, the second address is only interested in selling, which is also peculiar. I've inspected the highest transaction of the address in the dataset, and it seems this is a wallet for the Sorare project, which looks like fantasy football with NFT tokens. My guess is that they mint the tokens themselves and only sell them to their platform users.\n",
        "Next, we will explore which users have resold an NFT with the highest profit."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1de9dc59",
      "metadata": {
        "id": "1de9dc59",
        "outputId": "cccf6678-3e50-4245-a258-899893a4f9c7"
      },
      "outputs": [
        {
          "data": {
            "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>trader</th>\n",
              "      <th>nft</th>\n",
              "      <th>tokenID</th>\n",
              "      <th>soldTime</th>\n",
              "      <th>soldAmount</th>\n",
              "      <th>boughtTime</th>\n",
              "      <th>boughtAmount</th>\n",
              "      <th>difference</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>None</td>\n",
              "      <td>https://lh3.googleusercontent.com/tblRChM9Ych3...</td>\n",
              "      <td>('Cryptopunks', '3011')</td>\n",
              "      <td>2021-04-27T15:44:50.000000000+00:00</td>\n",
              "      <td>1723131.135</td>\n",
              "      <td>2021-03-12T01:00:13.000000000+00:00</td>\n",
              "      <td>712808.00</td>\n",
              "      <td>1010323.135</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>PixelVault_</td>\n",
              "      <td>https://lh3.googleusercontent.com/cBxKNeyUzvwl...</td>\n",
              "      <td>('Superrare', '12')</td>\n",
              "      <td>2021-03-31T15:39:49.000000000+00:00</td>\n",
              "      <td>883388.375</td>\n",
              "      <td>2021-03-18T19:09:05.000000000+00:00</td>\n",
              "      <td>99253.00</td>\n",
              "      <td>784135.375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>None</td>\n",
              "      <td>https://lh3.googleusercontent.com/l6EtSH2XoyOi...</td>\n",
              "      <td>('Cryptopunks', '2306')</td>\n",
              "      <td>2021-04-18T11:04:44.000000000+00:00</td>\n",
              "      <td>994856.440</td>\n",
              "      <td>2021-02-11T04:01:32.000000000+00:00</td>\n",
              "      <td>220262.50</td>\n",
              "      <td>774593.940</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>TokenAngels</td>\n",
              "      <td>https://lh3.googleusercontent.com/tblRChM9Ych3...</td>\n",
              "      <td>('Cryptopunks', '3011')</td>\n",
              "      <td>2021-03-12T01:00:13.000000000+00:00</td>\n",
              "      <td>712808.000</td>\n",
              "      <td>2020-10-12T18:11:59.000000000+00:00</td>\n",
              "      <td>15981.84</td>\n",
              "      <td>696826.160</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Pranksy</td>\n",
              "      <td>https://lh3.googleusercontent.com/1Tc5JdWMknIk...</td>\n",
              "      <td>('Cryptopunks', '2140')</td>\n",
              "      <td>2021-03-02T01:02:54.000000000+00:00</td>\n",
              "      <td>1147815.000</td>\n",
              "      <td>2021-02-22T10:44:16.000000000+00:00</td>\n",
              "      <td>676912.00</td>\n",
              "      <td>470903.000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        trader                                                nft  \\\n",
              "0         None  https://lh3.googleusercontent.com/tblRChM9Ych3...   \n",
              "1  PixelVault_  https://lh3.googleusercontent.com/cBxKNeyUzvwl...   \n",
              "2         None  https://lh3.googleusercontent.com/l6EtSH2XoyOi...   \n",
              "3  TokenAngels  https://lh3.googleusercontent.com/tblRChM9Ych3...   \n",
              "4      Pranksy  https://lh3.googleusercontent.com/1Tc5JdWMknIk...   \n",
              "\n",
              "                   tokenID                             soldTime   soldAmount  \\\n",
              "0  ('Cryptopunks', '3011')  2021-04-27T15:44:50.000000000+00:00  1723131.135   \n",
              "1      ('Superrare', '12')  2021-03-31T15:39:49.000000000+00:00   883388.375   \n",
              "2  ('Cryptopunks', '2306')  2021-04-18T11:04:44.000000000+00:00   994856.440   \n",
              "3  ('Cryptopunks', '3011')  2021-03-12T01:00:13.000000000+00:00   712808.000   \n",
              "4  ('Cryptopunks', '2140')  2021-03-02T01:02:54.000000000+00:00  1147815.000   \n",
              "\n",
              "                            boughtTime  boughtAmount   difference  \n",
              "0  2021-03-12T01:00:13.000000000+00:00     712808.00  1010323.135  \n",
              "1  2021-03-18T19:09:05.000000000+00:00      99253.00   784135.375  \n",
              "2  2021-02-11T04:01:32.000000000+00:00     220262.50   774593.940  \n",
              "3  2020-10-12T18:11:59.000000000+00:00      15981.84   696826.160  \n",
              "4  2021-02-22T10:44:16.000000000+00:00     676912.00   470903.000  "
            ]
          },
          "execution_count": 12,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH (t:Trader)-[:SOLD]->(st:Transaction)-[:FOR_NFT]->(nft)\n",
        "WHERE st.Price_USD > 100000\n",
        "MATCH (t)-[:BOUGHT]->(bt:Transaction)-[:FOR_NFT]->(nft)\n",
        "WHERE st.Datetime_updated_seconds > bt.Datetime_updated_seconds\n",
        "RETURN t.username as trader, nft.Image_url_1 as nft, nft.ID_token AS tokenID, \n",
        "       st.Datetime_updated_seconds AS soldTime, \n",
        "       st.Price_USD AS soldAmount, \n",
        "       bt.Datetime_updated_seconds as boughtTime, \n",
        "       bt.Price_USD AS boughtAmount,\n",
        "       st.Price_USD - bt.Price_USD AS difference\n",
        "ORDER BY difference DESC LIMIT 5\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "32f15c35",
      "metadata": {
        "id": "32f15c35"
      },
      "source": [
        "It seems that Cryptopunk were (are?) the most lucrative tokens. For example, a user bought the Cyptopunk 3011 NFT on the 12th of March 2021 at 700 thousand USD and then resold it a good month later for 1.7 million USD. That is a good deal for a pixel image of a dude smoking a pipe. Now, you might say why would someone sell an NFT for 700 thousand when they could get a million more, but here is the catch. That person bought it for 16 thousand USD in October 2020 and sold it in March 2021 for 700 thousand. So effectively, the price of the NFT went 100x from October 2020 to March 2021.\n",
        "## Graph algorithms\n",
        "Lastly, we will run few graph algorithms on the dataset. We will construct an inferred directed weighted network of transactions between traders. The direction of the relationship will indicate the flow of the money, while the weight will represent the volume. We will use the Cypher Projection to project a virtual network that is a transformed version of the underlying stored graph."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "4ece3e39",
      "metadata": {
        "id": "4ece3e39",
        "outputId": "19224ad4-1182-4066-f84f-328341dbfcc5"
      },
      "outputs": [
        {
          "data": {
            "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>nodeQuery</th>\n",
              "      <th>relationshipQuery</th>\n",
              "      <th>graphName</th>\n",
              "      <th>nodeCount</th>\n",
              "      <th>relationshipCount</th>\n",
              "      <th>createMillis</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>MATCH (t:Trader) WHERE NOT t.username = 'NullA...</td>\n",
              "      <td>MATCH (t1:Trader)-[:BOUGHT]-&gt;(t)&lt;-[:SOLD]-(t2:...</td>\n",
              "      <td>nft</td>\n",
              "      <td>70805</td>\n",
              "      <td>338639</td>\n",
              "      <td>77408</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                           nodeQuery  \\\n",
              "0  MATCH (t:Trader) WHERE NOT t.username = 'NullA...   \n",
              "\n",
              "                                   relationshipQuery graphName  nodeCount  \\\n",
              "0  MATCH (t1:Trader)-[:BOUGHT]->(t)<-[:SOLD]-(t2:...       nft      70805   \n",
              "\n",
              "   relationshipCount  createMillis  \n",
              "0             338639         77408  "
            ]
          },
          "execution_count": 13,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "CALL gds.graph.project.cypher(\"nft\", \n",
        "\"MATCH (t:Trader) WHERE NOT t.username = 'NullAddress' RETURN id(t) as id\",\n",
        "\"MATCH (t1:Trader)-[:BOUGHT]->(t)<-[:SOLD]-(t2:Trader)\n",
        " RETURN id(t1) AS source, id(t2) as target, apoc.coll.max([sum(coalesce(t.Price_USD,0.1)),1]) as weight\", {validateRelationships:false})\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "008795f6",
      "metadata": {
        "id": "008795f6"
      },
      "source": [
        "The first algoritm we will execute is the Weakly Connected Component algorithm. It is used to find disconnected parts or islands within the network."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9c3b2918",
      "metadata": {
        "id": "9c3b2918",
        "outputId": "f64efc78-6040-4a84-fc2c-fe9b5e138470"
      },
      "outputs": [
        {
          "data": {
            "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>componentCount</th>\n",
              "      <th>componentDistribution</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>5287</td>\n",
              "      <td>{'p99': 3, 'min': 1, 'max': 64774, 'mean': 13....</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   componentCount                              componentDistribution\n",
              "0            5287  {'p99': 3, 'min': 1, 'max': 64774, 'mean': 13...."
            ]
          },
          "execution_count": 14,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "CALL gds.wcc.stats(\"nft\")\n",
        "YIELD componentCount, componentDistribution\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "4e80237b",
      "metadata": {
        "id": "4e80237b"
      },
      "source": [
        "The NFT transactions network is quite disconnected. There are 5287 separate components, and the largest one contains only around 12% of traders. That means that there are several small components where traders only conducted business between a small group of users. What's even stranger is that more than 75% of components consist of a single user. That means that bought the sender, as well as the recipient, is the same user.\n",
        "Let's examine a sample trader with self-loops."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "60a473f1",
      "metadata": {
        "id": "60a473f1",
        "outputId": "2fa4996a-ce7a-42d0-ab68-cc2b3ca6a253"
      },
      "outputs": [
        {
          "data": {
            "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>result</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>[None, 0xa5437ea384eebabfef3347606055cfbb9ff44...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>[None, 0xf325fe3959979471bf5a14f74fc658663424e...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>[None, 0xdf321ceee408d6a1fd7a44a624da393e373d6...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>[None, 0x735d66b71a437da8ef2c3220b83d42ec5c7d5...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                              result\n",
              "0  [None, 0xa5437ea384eebabfef3347606055cfbb9ff44...\n",
              "1  [None, 0xf325fe3959979471bf5a14f74fc658663424e...\n",
              "2  [None, 0xdf321ceee408d6a1fd7a44a624da393e373d6...\n",
              "3  [None, 0x735d66b71a437da8ef2c3220b83d42ec5c7d5..."
            ]
          },
          "execution_count": 15,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "MATCH p=(t:Trader)-[:BOUGHT]->()<-[:SOLD]-(t)\n",
        "WHERE t.username = \"grake\"\n",
        "RETURN [node in nodes(p) | node.Transaction_hash] AS result\n",
        "LIMIT 10\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e3615ccd",
      "metadata": {
        "id": "e3615ccd"
      },
      "source": [
        "At first, I thought this was a mistake in the underlying dataset. However, I inspected a single transaction on the OpenSea platform, and it looks like a user can sell an NFT to themselves. However, I think that there are some mechanics of the platform, which leaves the logs confusing.\n",
        "\n",
        "Lastly. we will execute the ArticleRank algorithm, which is a variation of the PageRank algorithm to find the network influencers."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "3fad2ac6",
      "metadata": {
        "id": "3fad2ac6",
        "outputId": "e160701c-e65e-4953-fdad-e72d0423e77f"
      },
      "outputs": [
        {
          "data": {
            "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>username</th>\n",
              "      <th>score</th>\n",
              "      <th>boughtCount</th>\n",
              "      <th>soldCount</th>\n",
              "      <th>boughtVolume</th>\n",
              "      <th>soldVolume</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>YellowHeartFactories</td>\n",
              "      <td>81.984069</td>\n",
              "      <td>3</td>\n",
              "      <td>5423</td>\n",
              "      <td>5.176749e+03</td>\n",
              "      <td>7.096732e+05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Pranksy</td>\n",
              "      <td>35.339142</td>\n",
              "      <td>3044</td>\n",
              "      <td>20198</td>\n",
              "      <td>3.931823e+06</td>\n",
              "      <td>9.985689e+06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>0xfc624f8f58db41bdb95aedee1de3c1cf047105f1</td>\n",
              "      <td>27.586534</td>\n",
              "      <td>1976</td>\n",
              "      <td>43571</td>\n",
              "      <td>1.299069e+05</td>\n",
              "      <td>3.551704e+05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>0x4FabDA</td>\n",
              "      <td>23.406713</td>\n",
              "      <td>28</td>\n",
              "      <td>49338</td>\n",
              "      <td>6.820018e+04</td>\n",
              "      <td>5.425523e+05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ethernitychain</td>\n",
              "      <td>17.147044</td>\n",
              "      <td>0</td>\n",
              "      <td>1055</td>\n",
              "      <td>NaN</td>\n",
              "      <td>6.286660e+05</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                     username      score  boughtCount  \\\n",
              "0                        YellowHeartFactories  81.984069            3   \n",
              "1                                     Pranksy  35.339142         3044   \n",
              "2  0xfc624f8f58db41bdb95aedee1de3c1cf047105f1  27.586534         1976   \n",
              "3                                    0x4FabDA  23.406713           28   \n",
              "4                              ethernitychain  17.147044            0   \n",
              "\n",
              "   soldCount  boughtVolume    soldVolume  \n",
              "0       5423  5.176749e+03  7.096732e+05  \n",
              "1      20198  3.931823e+06  9.985689e+06  \n",
              "2      43571  1.299069e+05  3.551704e+05  \n",
              "3      49338  6.820018e+04  5.425523e+05  \n",
              "4       1055           NaN  6.286660e+05  "
            ]
          },
          "execution_count": 16,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "CALL gds.articleRank.stream(\"nft\")\n",
        "YIELD nodeId, score\n",
        "WITH gds.util.asNode(nodeId) AS node, score\n",
        "ORDER BY score DESC\n",
        "LIMIT 5\n",
        "RETURN node.username as username,\n",
        "      score,\n",
        "      size((node)-[:BOUGHT]->()) as boughtCount,\n",
        "      size((node)-[:SOLD]->()) as soldCount,\n",
        "      apoc.coll.sum([(node)-[:BOUGHT]->(t) | coalesce(t.Price_USD,0) ]) as boughtVolume,\n",
        "      apoc.coll.sum([(node)-[:SOLD]->(t) | coalesce(t.Price_USD,0) ]) as soldVolume\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "69615167",
      "metadata": {
        "id": "69615167",
        "outputId": "47d5fbe1-b43d-4f4e-ddbe-51265d2f2d9c"
      },
      "outputs": [
        {
          "data": {
            "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>graphName</th>\n",
              "      <th>database</th>\n",
              "      <th>memoryUsage</th>\n",
              "      <th>sizeInBytes</th>\n",
              "      <th>nodeProjection</th>\n",
              "      <th>relationshipProjection</th>\n",
              "      <th>nodeQuery</th>\n",
              "      <th>relationshipQuery</th>\n",
              "      <th>nodeCount</th>\n",
              "      <th>relationshipCount</th>\n",
              "      <th>nodeFilter</th>\n",
              "      <th>relationshipFilter</th>\n",
              "      <th>density</th>\n",
              "      <th>creationTime</th>\n",
              "      <th>modificationTime</th>\n",
              "      <th>schema</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>nft</td>\n",
              "      <td>neo4j</td>\n",
              "      <td></td>\n",
              "      <td>-1</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>MATCH (t:Trader) WHERE NOT t.username = 'NullA...</td>\n",
              "      <td>MATCH (t1:Trader)-[:BOUGHT]-&gt;(t)&lt;-[:SOLD]-(t2:...</td>\n",
              "      <td>70805</td>\n",
              "      <td>338639</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>0.000068</td>\n",
              "      <td>2022-01-08T20:39:24.267423000+01:00</td>\n",
              "      <td>2022-01-08T20:40:41.680117000+01:00</td>\n",
              "      <td>{'relationships': {'__ALL__': {'weight': 'Floa...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  graphName database memoryUsage  sizeInBytes nodeProjection  \\\n",
              "0       nft    neo4j                       -1           None   \n",
              "\n",
              "  relationshipProjection                                          nodeQuery  \\\n",
              "0                   None  MATCH (t:Trader) WHERE NOT t.username = 'NullA...   \n",
              "\n",
              "                                   relationshipQuery  nodeCount  \\\n",
              "0  MATCH (t1:Trader)-[:BOUGHT]->(t)<-[:SOLD]-(t2:...      70805   \n",
              "\n",
              "   relationshipCount nodeFilter relationshipFilter   density  \\\n",
              "0             338639       None               None  0.000068   \n",
              "\n",
              "                          creationTime                     modificationTime  \\\n",
              "0  2022-01-08T20:39:24.267423000+01:00  2022-01-08T20:40:41.680117000+01:00   \n",
              "\n",
              "                                              schema  \n",
              "0  {'relationships': {'__ALL__': {'weight': 'Floa...  "
            ]
          },
          "execution_count": 17,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "neo4j_query(\"\"\"\n",
        "CALL gds.graph.drop('nft')\n",
        "\"\"\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "97494f58",
      "metadata": {
        "id": "97494f58"
      },
      "outputs": [],
      "source": [
        ""
      ]
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "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.8"
    },
    "colab": {
      "name": "NFT analysis.ipynb",
      "provenance": []
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}