ClickHouse_Fraud_Detection.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "ClickHouse Fraud Detection.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# Fraud Detection using ClickHouse"
      ],
      "metadata": {
        "id": "1cqOf1gR6koc"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "y0M4E17MKgJ5",
        "outputId": "db5319ec-332d-4669-9bd7-25ac99e82078"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Reading package lists... Done\n",
            "Building dependency tree       \n",
            "Reading state information... Done\n",
            "ca-certificates is already the newest version (20211016~18.04.1).\n",
            "dirmngr is already the newest version (2.2.4-1ubuntu1.6).\n",
            "The following package was automatically installed and is no longer required:\n",
            "  libnvidia-common-460\n",
            "Use 'sudo apt autoremove' to remove it.\n",
            "The following NEW packages will be installed:\n",
            "  apt-transport-https\n",
            "0 upgraded, 1 newly installed, 0 to remove and 20 not upgraded.\n",
            "Need to get 4,348 B of archives.\n",
            "After this operation, 154 kB of additional disk space will be used.\n",
            "Get:1 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 apt-transport-https all 1.6.14 [4,348 B]\n",
            "Fetched 4,348 B in 0s (31.9 kB/s)\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76, <> line 1.)\n",
            "debconf: falling back to frontend: Readline\n",
            "debconf: unable to initialize frontend: Readline\n",
            "debconf: (This frontend requires a controlling tty.)\n",
            "debconf: falling back to frontend: Teletype\n",
            "dpkg-preconfigure: unable to re-open stdin: \n",
            "Selecting previously unselected package apt-transport-https.\n",
            "(Reading database ... 155676 files and directories currently installed.)\n",
            "Preparing to unpack .../apt-transport-https_1.6.14_all.deb ...\n",
            "Unpacking apt-transport-https (1.6.14) ...\n",
            "Setting up apt-transport-https (1.6.14) ...\n",
            "Executing: /tmp/apt-key-gpghome.if6ABdEvQC/gpg.1.sh --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4\n",
            "gpg: key C8F1E19FE0C56BD4: public key \"ClickHouse Repository Key <milovidov@yandex-team.ru>\" imported\n",
            "gpg: Total number processed: 1\n",
            "gpg:               imported: 1\n",
            "deb http://repo.clickhouse.tech/deb/stable/ main/\n",
            "Get:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Ign:2 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:3 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "Hit:4 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:5 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:6 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Get:7 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ Packages [91.1 kB]\n",
            "Get:8 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Get:9 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Get:10 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [912 kB]\n",
            "Ign:11 http://repo.clickhouse.tech/deb/stable main/ InRelease\n",
            "Get:12 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Hit:14 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:15 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,311 kB]\n",
            "Hit:16 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:17 http://repo.clickhouse.tech/deb/stable main/ Release [749 B]\n",
            "Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,369 kB]\n",
            "Hit:19 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Get:20 http://repo.clickhouse.tech/deb/stable main/ Release.gpg [836 B]\n",
            "Get:21 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [2,093 kB]\n",
            "Get:22 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,938 kB]\n",
            "Get:23 http://repo.clickhouse.tech/deb/stable main/ Packages [230 kB]\n",
            "Get:24 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,534 kB]\n",
            "Get:25 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [1,073 kB]\n",
            "Fetched 14.8 MB in 3s (4,963 kB/s)\n",
            "Reading package lists... Done\n",
            "Reading package lists... Done\n",
            "Building dependency tree       \n",
            "Reading state information... Done\n",
            "The following package was automatically installed and is no longer required:\n",
            "  libnvidia-common-460\n",
            "Use 'sudo apt autoremove' to remove it.\n",
            "The following additional packages will be installed:\n",
            "  clickhouse-common-static libcap2-bin libpam-cap\n",
            "Suggested packages:\n",
            "  clickhouse-common-static-dbg\n",
            "The following NEW packages will be installed:\n",
            "  clickhouse-client clickhouse-common-static clickhouse-server libcap2-bin\n",
            "  libpam-cap\n",
            "0 upgraded, 5 newly installed, 0 to remove and 27 not upgraded.\n",
            "Need to get 224 MB of archives.\n",
            "After this operation, 733 MB of additional disk space will be used.\n",
            "Get:1 http://archive.ubuntu.com/ubuntu bionic/main amd64 libcap2-bin amd64 1:2.25-1.2 [20.6 kB]\n",
            "Get:2 http://archive.ubuntu.com/ubuntu bionic/main amd64 libpam-cap amd64 1:2.25-1.2 [7,268 B]\n",
            "Get:3 http://repo.clickhouse.tech/deb/stable main/ clickhouse-common-static 22.2.2.1 [224 MB]\n",
            "Get:4 http://repo.clickhouse.tech/deb/stable main/ clickhouse-client 22.2.2.1 [22.9 kB]\n",
            "Get:5 http://repo.clickhouse.tech/deb/stable main/ clickhouse-server 22.2.2.1 [45.5 kB]\n",
            "Fetched 224 MB in 8s (29.0 MB/s)\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76, <> line 5.)\n",
            "debconf: falling back to frontend: Readline\n",
            "debconf: unable to initialize frontend: Readline\n",
            "debconf: (This frontend requires a controlling tty.)\n",
            "debconf: falling back to frontend: Teletype\n",
            "dpkg-preconfigure: unable to re-open stdin: \n",
            "Selecting previously unselected package libcap2-bin.\n",
            "(Reading database ... 155680 files and directories currently installed.)\n",
            "Preparing to unpack .../libcap2-bin_1%3a2.25-1.2_amd64.deb ...\n",
            "Unpacking libcap2-bin (1:2.25-1.2) ...\n",
            "Selecting previously unselected package libpam-cap:amd64.\n",
            "Preparing to unpack .../libpam-cap_1%3a2.25-1.2_amd64.deb ...\n",
            "Unpacking libpam-cap:amd64 (1:2.25-1.2) ...\n",
            "Selecting previously unselected package clickhouse-common-static.\n",
            "Preparing to unpack .../clickhouse-common-static_22.2.2.1_amd64.deb ...\n",
            "Unpacking clickhouse-common-static (22.2.2.1) ...\n",
            "Selecting previously unselected package clickhouse-client.\n",
            "Preparing to unpack .../clickhouse-client_22.2.2.1_all.deb ...\n",
            "Unpacking clickhouse-client (22.2.2.1) ...\n",
            "Selecting previously unselected package clickhouse-server.\n",
            "Preparing to unpack .../clickhouse-server_22.2.2.1_all.deb ...\n",
            "Unpacking clickhouse-server (22.2.2.1) ...\n",
            "Setting up libpam-cap:amd64 (1:2.25-1.2) ...\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)\n",
            "debconf: falling back to frontend: Readline\n",
            "Setting up libcap2-bin (1:2.25-1.2) ...\n",
            "Setting up clickhouse-common-static (22.2.2.1) ...\n",
            "Setting up clickhouse-server (22.2.2.1) ...\n",
            "debconf: unable to initialize frontend: Dialog\n",
            "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)\n",
            "debconf: falling back to frontend: Readline\n",
            "ClickHouse binary is already located at /usr/bin/clickhouse\n",
            "Symlink /usr/bin/clickhouse-server already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-server to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-client already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-client to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-local already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-local to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-benchmark already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-benchmark to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-copier already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-copier to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-obfuscator already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-obfuscator to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-git-import to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-compressor already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-compressor to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-format already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-format to /usr/bin/clickhouse.\n",
            "Symlink /usr/bin/clickhouse-extract-from-config already exists but it points to /clickhouse. Will replace the old symlink to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-extract-from-config to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-keeper to /usr/bin/clickhouse.\n",
            "Creating symlink /usr/bin/clickhouse-keeper-converter to /usr/bin/clickhouse.\n",
            "Creating clickhouse group if it does not exist.\n",
            " groupadd -r clickhouse\n",
            "Creating clickhouse user if it does not exist.\n",
            " useradd -r --shell /bin/false --home-dir /nonexistent -g clickhouse clickhouse\n",
            "Will set ulimits for clickhouse user in /etc/security/limits.d/clickhouse.conf.\n",
            "Creating config directory /etc/clickhouse-server/config.d that is used for tweaks of main server configuration.\n",
            "Creating config directory /etc/clickhouse-server/users.d that is used for tweaks of users configuration.\n",
            "Config file /etc/clickhouse-server/config.xml already exists, will keep it and extract path info from it.\n",
            "/etc/clickhouse-server/config.xml has /var/lib/clickhouse/ as data path.\n",
            "/etc/clickhouse-server/config.xml has /var/log/clickhouse-server/ as log path.\n",
            "Users config file /etc/clickhouse-server/users.xml already exists, will keep it and extract users info from it.\n",
            "Creating log directory /var/log/clickhouse-server/.\n",
            "Creating data directory /var/lib/clickhouse/.\n",
            "Creating pid directory /var/run/clickhouse-server.\n",
            " chown -R clickhouse:clickhouse '/var/log/clickhouse-server/'\n",
            " chown -R clickhouse:clickhouse '/var/run/clickhouse-server'\n",
            " chown  clickhouse:clickhouse '/var/lib/clickhouse/'\n",
            " groupadd -r clickhouse-bridge\n",
            " useradd -r --shell /bin/false --home-dir /nonexistent -g clickhouse-bridge clickhouse-bridge\n",
            " chown -R clickhouse-bridge:clickhouse-bridge '/usr/bin/clickhouse-odbc-bridge'\n",
            " chown -R clickhouse-bridge:clickhouse-bridge '/usr/bin/clickhouse-library-bridge'\n",
            "Enter password for default user: \n",
            "\u001b[1mPassword for default user is saved in file /etc/clickhouse-server/users.d/default-password.xml.\u001b[0m\n",
            "Setting capabilities for clickhouse binary. This is optional.\n",
            "Cannot set 'net_admin' or 'ipc_lock' or 'sys_nice' capability for clickhouse binary. This is optional. Taskstats accounting will be disabled. To enable taskstats accounting you may add the required capability later manually.\n",
            " chown -R clickhouse:clickhouse '/etc/clickhouse-server'\n",
            "\n",
            "ClickHouse has been successfully installed.\n",
            "\n",
            "Start clickhouse-server with:\n",
            " sudo clickhouse start\n",
            "\n",
            "Start clickhouse-client with:\n",
            " clickhouse-client --password\n",
            "\n",
            "Setting up clickhouse-client (22.2.2.1) ...\n",
            "Processing triggers for man-db (2.8.3-2ubuntu0.1) ...\n",
            "Processing triggers for systemd (237-3ubuntu10.53) ...\n",
            " chown -R clickhouse: '/var/run/clickhouse-server/'\n",
            "Will run su -s /bin/sh 'clickhouse' -c '/usr/bin/clickhouse-server --config-file /etc/clickhouse-server/config.xml --pid-file /var/run/clickhouse-server/clickhouse-server.pid --daemon'\n",
            "Waiting for server to start\n",
            "Waiting for server to start\n",
            "Server started\n",
            "src/tcmalloc.cc:283] Attempt to free invalid pointer 0x7fa7b2816000 \n"
          ]
        }
      ],
      "source": [
        "!sudo apt-get install apt-transport-https ca-certificates dirmngr\n",
        "!sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4\n",
        "!echo \"deb http://repo.clickhouse.tech/deb/stable/ main/\" | sudo tee \\\n",
        "    /etc/apt/sources.list.d/clickhouse.list\n",
        "!sudo apt-get update\n",
        "!sudo apt-get install -y clickhouse-server clickhouse-client\n",
        "!sudo service clickhouse-server start\n",
        "!clickhouse-client"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install clickhouse-driver"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4vqDCXIjM0xX",
        "outputId": "4d541d2a-72ba-4a28-d656-8169f965d777"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting clickhouse-driver\n",
            "  Downloading clickhouse_driver-0.2.4-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (622 kB)\n",
            "\u001b[K     |████████████████████████████████| 622 kB 5.2 MB/s \n",
            "\u001b[?25hRequirement already satisfied: pytz in /usr/local/lib/python3.7/dist-packages (from clickhouse-driver) (2022.2.1)\n",
            "Requirement already satisfied: tzlocal in /usr/local/lib/python3.7/dist-packages (from clickhouse-driver) (1.5.1)\n",
            "Installing collected packages: clickhouse-driver\n",
            "Successfully installed clickhouse-driver-0.2.4\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from clickhouse_driver import Client\n",
        "import pandas as pd\n",
        "from datetime import datetime"
      ],
      "metadata": {
        "id": "2dd1-mZqM1YV"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "client = Client(host='localhost', password='fraud')"
      ],
      "metadata": {
        "id": "ywQhDxGcRQHv"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "client.execute('CREATE DATABASE fraud_detection')\n",
        "client.execute('SHOW DATABASES')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Lfx69QTi8wkC",
        "outputId": "530bda32-6c30-4b44-874f-0b00a40768fe"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('INFORMATION_SCHEMA',),\n",
              " ('default',),\n",
              " ('fraud_detection',),\n",
              " ('information_schema',),\n",
              " ('system',)]"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "client = Client(host='localhost',\n",
        "                user='default',\n",
        "                password='fraud',\n",
        "                port=9000,\n",
        "                database='fraud_detection',\n",
        "                settings={'columnar': True}\n",
        "         )"
      ],
      "metadata": {
        "id": "RA8B2Ak78xIV"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df = pd.read_csv('./transactions.csv')\n",
        "df['dt'] = pd.to_datetime(df['dt'], format='%Y-%m-%d %H:%M:%S')\n",
        "df.info()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "eeqWBj1SX4j6",
        "outputId": "eb6b575f-221a-4c60-b8f5-94b28fece27b"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 75 entries, 0 to 74\n",
            "Data columns (total 3 columns):\n",
            " #   Column  Non-Null Count  Dtype         \n",
            "---  ------  --------------  -----         \n",
            " 0   sender  75 non-null     object        \n",
            " 1   dt      75 non-null     datetime64[ns]\n",
            " 2   amount  75 non-null     float64       \n",
            "dtypes: datetime64[ns](1), float64(1), object(1)\n",
            "memory usage: 1.9+ KB\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "9I5XHiN3YvHO",
        "outputId": "33c29166-c28d-404e-d050-e0f58c6058ec"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       sender                  dt  amount\n",
              "0  GaiHxoxzAI 2022-04-07 05:57:04   35.39\n",
              "1  GaiHxoxzAI 2022-04-07 05:12:46   60.78\n",
              "2  GaiHxoxzAI 2022-04-07 21:30:06   96.28\n",
              "3  GaiHxoxzAI 2022-04-07 01:51:59   62.29\n",
              "4  GaiHxoxzAI 2022-04-07 09:49:55   57.10"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-9b8476a6-db95-46bf-ae21-88c97cdcde1e\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>dt</th>\n",
              "      <th>amount</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 05:57:04</td>\n",
              "      <td>35.39</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 05:12:46</td>\n",
              "      <td>60.78</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 21:30:06</td>\n",
              "      <td>96.28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 01:51:59</td>\n",
              "      <td>62.29</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 09:49:55</td>\n",
              "      <td>57.10</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9b8476a6-db95-46bf-ae21-88c97cdcde1e')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-9b8476a6-db95-46bf-ae21-88c97cdcde1e button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-9b8476a6-db95-46bf-ae21-88c97cdcde1e');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "client.execute('DROP TABLE IF EXISTS transactions')\n",
        "client.execute(\n",
        "    '''\n",
        "    CREATE TABLE transactions (\n",
        "        sender String,\n",
        "        dt DateTime,\n",
        "        amount Float32\n",
        "    )\n",
        "    ENGINE = Memory\n",
        "    '''\n",
        ")\n",
        "client.execute('SHOW TABLES FROM fraud_detection')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "q1cu0WbDM2JI",
        "outputId": "2940d2ae-d35e-4445-c4a1-860aee1dade5"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('transactions',)]"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "client.execute('INSERT INTO transactions VALUES', df.to_dict('records'))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4V8zWFrwSMGA",
        "outputId": "db57e00b-a8bb-443a-ab9b-0491919b7dd2"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "75"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "client.execute('DESCRIBE transactions')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f2_SkbXXd7Zz",
        "outputId": "ff41c2a0-fe15-4925-e7b0-061470767b73"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('sender', 'String', '', '', '', '', ''),\n",
              " ('dt', 'DateTime', '', '', '', '', ''),\n",
              " ('amount', 'Float32', '', '', '', '', '')]"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "def select(query):\n",
        "    return client.query_dataframe(query)"
      ],
      "metadata": {
        "id": "zq1pRNCkSMbA"
      },
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "select(\n",
        "    '''\n",
        "    SELECT *\n",
        "    FROM transactions\n",
        "    LIMIT 10\n",
        "    '''\n",
        ")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "Ndd23A7CSMg7",
        "outputId": "8d147a6f-d2b0-4675-cab9-e7aa48c93141"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       sender                  dt     amount\n",
              "0  GaiHxoxzAI 2022-04-07 05:57:04  35.389999\n",
              "1  GaiHxoxzAI 2022-04-07 05:12:46  60.779999\n",
              "2  GaiHxoxzAI 2022-04-07 21:30:06  96.279999\n",
              "3  GaiHxoxzAI 2022-04-07 01:51:59  62.290001\n",
              "4  GaiHxoxzAI 2022-04-07 09:49:55  57.099998\n",
              "5  GaiHxoxzAI 2022-04-07 02:07:34  24.170000\n",
              "6  GaiHxoxzAI 2022-04-07 23:51:43  85.449997\n",
              "7  GaiHxoxzAI 2022-04-07 02:32:33  26.049999\n",
              "8  RwahcKRENT 2022-04-07 01:26:02  25.780001\n",
              "9  RwahcKRENT 2022-04-07 04:21:47  75.430000"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-cbe1f829-bf24-4e07-a6ef-18f2be477ca9\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>dt</th>\n",
              "      <th>amount</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 05:57:04</td>\n",
              "      <td>35.389999</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 05:12:46</td>\n",
              "      <td>60.779999</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 21:30:06</td>\n",
              "      <td>96.279999</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 01:51:59</td>\n",
              "      <td>62.290001</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 09:49:55</td>\n",
              "      <td>57.099998</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 02:07:34</td>\n",
              "      <td>24.170000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 23:51:43</td>\n",
              "      <td>85.449997</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>GaiHxoxzAI</td>\n",
              "      <td>2022-04-07 02:32:33</td>\n",
              "      <td>26.049999</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>RwahcKRENT</td>\n",
              "      <td>2022-04-07 01:26:02</td>\n",
              "      <td>25.780001</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>RwahcKRENT</td>\n",
              "      <td>2022-04-07 04:21:47</td>\n",
              "      <td>75.430000</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-cbe1f829-bf24-4e07-a6ef-18f2be477ca9')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-cbe1f829-bf24-4e07-a6ef-18f2be477ca9 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-cbe1f829-bf24-4e07-a6ef-18f2be477ca9');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "The transaction chain is considered fraudulent if the following conditions are met:  \n",
        "- each transaction from the same sender\n",
        "- transactions' sum >= 150\n",
        "- 2 or more consecutive transactions with interruption < 1 hour  \n",
        "  \n",
        "Print for each sender:  \n",
        "Transaction chain start time, transaction chain end time, number of transactions in chain, sum of chain transactions\n",
        "\n",
        "P.S. This task conditions is taken from HackerRank \"Advanced SQL\" certification. The data set for the solution was generated by me"
      ],
      "metadata": {
        "id": "UZX73X5901Hr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "select(\n",
        "    '''\n",
        "    WITH\n",
        "    transactions_lags AS (\n",
        "        SELECT sender,\n",
        "               dt,\n",
        "               lagInFrame(dt, 1) OVER (\n",
        "                   PARTITION BY sender\n",
        "                   ORDER BY dt\n",
        "                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS time_lag,\n",
        "               amount\n",
        "        FROM transactions),\n",
        "    transactions_hours AS (\n",
        "        SELECT sender,\n",
        "               dt,\n",
        "               dateDiff('minute', time_lag, dt) AS minute_diff,\n",
        "               amount\n",
        "        FROM transactions_lags),\n",
        "    transactions_series AS (\n",
        "        SELECT sender,\n",
        "               dt,\n",
        "               COUNT(IF(minute_diff > 60, 1, NULL)) OVER (\n",
        "                   PARTITION BY sender\n",
        "                   ORDER BY dt) AS series,\n",
        "               amount\n",
        "        FROM transactions_hours),\n",
        "    transactions_all AS (\n",
        "        SELECT sender,\n",
        "               FIRST_VALUE(dt) OVER (\n",
        "                   PARTITION BY sender, series\n",
        "                   ORDER BY dt) AS transactions_start,\n",
        "               LAST_VALUE(dt) OVER (\n",
        "                   PARTITION BY sender, series\n",
        "                   ORDER BY dt\n",
        "                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS transactions_end,\n",
        "               ROW_NUMBER() OVER (\n",
        "                   PARTITION BY sender, series\n",
        "                   ORDER BY dt) AS trans_num_in_series,\n",
        "               SUM(amount) OVER (\n",
        "               PARTITION BY sender, series\n",
        "               ORDER BY dt) AS transactions_sum\n",
        "        FROM transactions_series)\n",
        "    SELECT sender,\n",
        "           transactions_start,\n",
        "           transactions_end,\n",
        "           MAX(trans_num_in_series) AS transactions_count,\n",
        "           ROUND(MAX(transactions_sum), 2) AS transactions_sum\n",
        "    FROM transactions_all\n",
        "    GROUP BY sender, transactions_start, transactions_end\n",
        "    HAVING transactions_count > 1 AND transactions_sum >= 150\n",
        "    ORDER BY sender\n",
        "    '''\n",
        ")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "id": "dUeEDXRHSM4s",
        "outputId": "47bcedd6-11a2-4b05-b77d-ddec4a96120c"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       sender  transactions_start    transactions_end  transactions_count  \\\n",
              "0  PEcbzs4jzR 2022-04-07 11:19:30 2022-04-07 11:35:53                   2   \n",
              "1  PEcbzs4jzR 2022-04-07 04:06:48 2022-04-07 05:21:01                   3   \n",
              "2  g7ivjANgTO 2022-04-07 08:33:28 2022-04-07 09:04:44                   4   \n",
              "\n",
              "   transactions_sum  \n",
              "0            193.92  \n",
              "1            206.81  \n",
              "2            207.30  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-44adf5e2-c629-4d2b-a3e3-d00726128dfe\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>transactions_start</th>\n",
              "      <th>transactions_end</th>\n",
              "      <th>transactions_count</th>\n",
              "      <th>transactions_sum</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>PEcbzs4jzR</td>\n",
              "      <td>2022-04-07 11:19:30</td>\n",
              "      <td>2022-04-07 11:35:53</td>\n",
              "      <td>2</td>\n",
              "      <td>193.92</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>PEcbzs4jzR</td>\n",
              "      <td>2022-04-07 04:06:48</td>\n",
              "      <td>2022-04-07 05:21:01</td>\n",
              "      <td>3</td>\n",
              "      <td>206.81</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>g7ivjANgTO</td>\n",
              "      <td>2022-04-07 08:33:28</td>\n",
              "      <td>2022-04-07 09:04:44</td>\n",
              "      <td>4</td>\n",
              "      <td>207.30</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-44adf5e2-c629-4d2b-a3e3-d00726128dfe')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-44adf5e2-c629-4d2b-a3e3-d00726128dfe button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-44adf5e2-c629-4d2b-a3e3-d00726128dfe');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "As we can see we can easily use ClickHouse for this task, as well as MySQL or PostgreSQL. The advantage of using ClickHouse is that it allows to solve this task in real time."
      ],
      "metadata": {
        "id": "IIJ2QfG-Ahvi"
      }
    }
  ]
}