bitcoin_blockchain_with_as.ipynb

{
  "cells": [
    {
      "metadata": {
        "_cell_guid": "c00aa938-286a-461b-8fbd-3486b3188d38",
        "_uuid": "118088bc1f65d8030a50882edcdca039a70d6b4a"
      },
      "cell_type": "markdown",
      "source": "# Get Started\n\nAfter forking this notebook, run the code in the following cell:"
    },
    {
      "metadata": {
        "_cell_guid": "fafbc62b-2cf1-4d94-919b-f236e5fad3b2",
        "collapsed": true,
        "_uuid": "424a5ab971bbe2d7aec8fb83d76307d16095ad41",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# import package with helper functions \nimport bq_helper\n\n# create a helper object for this dataset\nbitcoin_blockchain = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n                                              dataset_name=\"bitcoin_blockchain\")",
      "execution_count": 1,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
        "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
      },
      "cell_type": "markdown",
      "source": "Then write the code to answer the questions below.\n\n#### Note\nThis dataset is bigger than the ones we've used previously, so your queries will be more than 1 Gigabyte. You can still run them by setting the \"max_gb_scanned\" argument in the `query_to_pandas_safe()` function to be large enough to run your query, or by using the `query_to_pandas()` function instead.\n\n## Questions\n#### 1) How many Bitcoin transactions were made each day in 2017?\n* You can use the \"timestamp\" column from the \"transactions\" table to answer this question. You can check the [notebook from Day 3](https://www.kaggle.com/rtatman/sql-scavenger-hunt-day-3/) for more information on timestamps."
    },
    {
      "metadata": {
        "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
        "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your Code Here\nquery = \"\"\" WITH time AS \n            (\n                SELECT TIMESTAMP_MILLIS(timestamp) AS trans_time,\n                    transaction_id\n                FROM `bigquery-public-data.bitcoin_blockchain.transactions`\n            )\n            SELECT COUNT(transaction_id) AS transactions,\n                EXTRACT(DAYOFYEAR FROM trans_time) AS day_of_year\n            FROM time\n            WHERE EXTRACT(YEAR FROM trans_time) = 2017\n            GROUP BY day_of_year\n            ORDER BY day_of_year\n        \"\"\"\ntransactions_per_day_2017 = bitcoin_blockchain.query_to_pandas(query)\nprint(transactions_per_day_2017)",
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": "     transactions  day_of_year\n0          180502            1\n1          290951            2\n2          301664            3\n3          328642            4\n4          288501            5\n5          346405            6\n6          282060            7\n7          260101            8\n8          269947            9\n9          323888           10\n10         303058           11\n11         295125           12\n12         272747           13\n13         242269           14\n14         235999           15\n15         267640           16\n16         292697           17\n17         297100           18\n18         300605           19\n19         298377           20\n20         263562           21\n21         236886           22\n22         235530           23\n23         278177           24\n24         304727           25\n25         313755           26\n26         263332           27\n27         246395           28\n28         241627           29\n29         247408           30\n..            ...          ...\n335        326354          336\n336        353044          337\n337        400686          338\n338        405709          339\n339        443587          340\n340        374917          341\n341        385092          342\n342        403399          343\n343        341420          344\n344        368598          345\n345        372977          346\n346        424579          347\n347        490644          348\n348        405676          349\n349        364222          350\n350        391910          351\n351        394239          352\n352        378622          353\n353        370277          354\n354        335482          355\n355        380648          356\n356        308211          357\n357        279523          358\n358        228926          359\n359        247440          360\n360        307642          361\n361        305053          362\n362        353824          363\n363        344407          364\n364        290422          365\n\n[365 rows x 2 columns]\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "_cell_guid": "c6fc9554-1290-4940-9c03-2929eb17613e",
        "_uuid": "6f5acc3073bb99100b5e1fd8f1111b8a75c19777"
      },
      "cell_type": "markdown",
      "source": "\n#### 2) How many transactions are associated with each merkle root?\n* You can use the \"merkle_root\" and \"transaction_id\" columns in the \"transactions\" table to answer this question. "
    },
    {
      "metadata": {
        "_cell_guid": "18629270-096f-412b-a3de-c863388d9f8f",
        "_uuid": "959b10f407dbd9f80ab1b5d33ed9c8eaafa6b30c",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your Code Here\nquery = \"\"\" SELECT merkle_root AS merkle,\n                COUNT(transaction_id) AS transactions\n            FROM `bigquery-public-data.bitcoin_blockchain.transactions`\n            GROUP BY merkle            \n\"\"\"\ntransactions_merkle = bitcoin_blockchain.query_to_pandas(query)\nprint(transactions_merkle)",
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": "       ...\n0      ...\n1      ...\n2      ...\n3      ...\n4      ...\n5      ...\n6      ...\n7      ...\n8      ...\n9      ...\n10     ...\n11     ...\n12     ...\n13     ...\n14     ...\n15     ...\n16     ...\n17     ...\n18     ...\n19     ...\n20     ...\n21     ...\n22     ...\n23     ...\n24     ...\n25     ...\n26     ...\n27     ...\n28     ...\n29     ...\n...    ...\n530728 ...\n530729 ...\n530730 ...\n530731 ...\n530732 ...\n530733 ...\n530734 ...\n530735 ...\n530736 ...\n530737 ...\n530738 ...\n530739 ...\n530740 ...\n530741 ...\n530742 ...\n530743 ...\n530744 ...\n530745 ...\n530746 ...\n530747 ...\n530748 ...\n530749 ...\n530750 ...\n530751 ...\n530752 ...\n530753 ...\n530754 ...\n530755 ...\n530756 ...\n530757 ...\n\n[530758 rows x 2 columns]\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "_cell_guid": "5a6f5219-6a0c-4446-8bd6-7f2a1772332b",
        "_uuid": "fa432e43e8dec711fad0b8b7af37f2e4db1a3079"
      },
      "cell_type": "markdown",
      "source": "---\n# Keep Going\n[Click here](https://www.kaggle.com/dansbecker/joins) to learn how to combine multiple data sources with the JOIN command.\n\n# Feedback\nBring any questions or feedback to the [Learn Discussion Forum](kaggle.com/learn-forum).\n\n----\n\n*This tutorial is part of the [SQL Series](https://www.kaggle.com/learn/sql) on Kaggle Learn.*"
    }
  ],
  "metadata": {
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}