sql_bigquery_2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Tiếp-tục-với-SQL\" data-toc-modified-id=\"Tiếp-tục-với-SQL-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Tiếp tục với SQL</a></span><ul class=\"toc-item\"><li><span><a href=\"#Order-By\" data-toc-modified-id=\"Order-By-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Order By</a></span><ul class=\"toc-item\"><li><span><a href=\"#Ngày-tháng\" data-toc-modified-id=\"Ngày-tháng-1.1.1\"><span class=\"toc-item-num\">1.1.1&nbsp;&nbsp;</span>Ngày tháng</a></span></li><li><span><a href=\"#EXTRACT\" data-toc-modified-id=\"EXTRACT-1.1.2\"><span class=\"toc-item-num\">1.1.2&nbsp;&nbsp;</span>EXTRACT</a></span></li><li><span><a href=\"#Ví-dụ:-Ngày-nào-trong-tuần-có-nhiều-tai-nạn-xe-máy-chết-người-nhất?\" data-toc-modified-id=\"Ví-dụ:-Ngày-nào-trong-tuần-có-nhiều-tai-nạn-xe-máy-chết-người-nhất?-1.1.3\"><span class=\"toc-item-num\">1.1.3&nbsp;&nbsp;</span>Ví dụ: Ngày nào trong tuần có nhiều tai nạn xe máy chết người nhất?</a></span></li><li><span><a href=\"#Bài-tập-thực-hành\" data-toc-modified-id=\"Bài-tập-thực-hành-1.1.4\"><span class=\"toc-item-num\">1.1.4&nbsp;&nbsp;</span>Bài tập thực hành</a></span></li></ul></li><li><span><a href=\"#As-&amp;-With\" data-toc-modified-id=\"As-&amp;-With-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>As &amp; With</a></span><ul class=\"toc-item\"><li><span><a href=\"#AS\" data-toc-modified-id=\"AS-1.2.1\"><span class=\"toc-item-num\">1.2.1&nbsp;&nbsp;</span>AS</a></span></li><li><span><a href=\"#WITH-...-AS\" data-toc-modified-id=\"WITH-...-AS-1.2.2\"><span class=\"toc-item-num\">1.2.2&nbsp;&nbsp;</span>WITH ... AS</a></span></li><li><span><a href=\"#Bài-tập-thực-hành\" data-toc-modified-id=\"Bài-tập-thực-hành-1.2.3\"><span class=\"toc-item-num\">1.2.3&nbsp;&nbsp;</span>Bài tập thực hành</a></span></li></ul></li><li><span><a href=\"#Joining-Data\" data-toc-modified-id=\"Joining-Data-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Joining Data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Thực-hành-cuối\" data-toc-modified-id=\"Thực-hành-cuối-1.3.1\"><span class=\"toc-item-num\">1.3.1&nbsp;&nbsp;</span>Thực hành cuối</a></span></li></ul></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tiếp tục với SQL\n",
    "## Order By\n",
    "**ORDER BY** thường là dòng lệnh cuối cùng trong Query của bạn, nó giúp sắp xếp kết quả theo thứ tự tăng hoặc giảm (với tham số **DESC**)\n",
    "![Ảnh](https://i.imgur.com/IElLJrR.png)\n",
    "\n",
    "### Ngày tháng\n",
    "Kiểu dữ liệu ngày tháng rất thường xuất hiện trong database đời thực. Có 2 cách để lưu trữ ngày tháng trong BigQuery: như một **DATE** hoặc như một **DATETIME**\n",
    "* Định dạng **DATE** có năm trước, tháng rồi ngày\n",
    "`YYYY-[M]M-[D]D` nên `2019-01-10` được nhận dạng là 10 tháng 1 năm 2019\n",
    "* **DATETIME** giống như kiểu date nhưng có thêm thời gian phía sau\n",
    "\n",
    "### EXTRACT\n",
    "SQL rất thông minh về ngày tháng, và chúng ta có thể hỏi thông tin nhiều hơn là nó thể hiện trong ô lưu trữ. Ví dụ, query sau trả về một cột với số tuần trong năm (số trong khoảng 1-53)\n",
    "![ảnh](https://i.imgur.com/A5hqGxY.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ví dụ: Ngày nào trong tuần có nhiều tai nạn xe máy chết người nhất?\n",
    "Lần này ta dùng database US Traffic Fatality Records, chứa dữ liệu về tai nạn giao thông tại Mỹ mà có ít nhất 1 người chết. Dùng bảng `accident_2015`, code sau xem 5 dòng đầu tiên của bảng."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "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>state_number</th>\n",
       "      <th>state_name</th>\n",
       "      <th>consecutive_number</th>\n",
       "      <th>number_of_vehicle_forms_submitted_all</th>\n",
       "      <th>number_of_motor_vehicles_in_transport_mvit</th>\n",
       "      <th>number_of_parked_working_vehicles</th>\n",
       "      <th>number_of_forms_submitted_for_persons_not_in_motor_vehicles</th>\n",
       "      <th>number_of_persons_not_in_motor_vehicles_in_transport_mvit</th>\n",
       "      <th>number_of_persons_in_motor_vehicles_in_transport_mvit</th>\n",
       "      <th>number_of_forms_submitted_for_persons_in_motor_vehicles</th>\n",
       "      <th>...</th>\n",
       "      <th>minute_of_ems_arrival_at_hospital</th>\n",
       "      <th>related_factors_crash_level_1</th>\n",
       "      <th>related_factors_crash_level_1_name</th>\n",
       "      <th>related_factors_crash_level_2</th>\n",
       "      <th>related_factors_crash_level_2_name</th>\n",
       "      <th>related_factors_crash_level_3</th>\n",
       "      <th>related_factors_crash_level_3_name</th>\n",
       "      <th>number_of_fatalities</th>\n",
       "      <th>number_of_drunk_drivers</th>\n",
       "      <th>timestamp_of_crash</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>190204</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-09-11 20:20:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>190233</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-11-01 00:30:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>190179</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-05-04 16:18:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>190248</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>99</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-11-17 12:26:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>190231</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-10-31 04:49:00+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 70 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   state_number state_name  consecutive_number  \\\n",
       "0            19       Iowa              190204   \n",
       "1            19       Iowa              190233   \n",
       "2            19       Iowa              190179   \n",
       "3            19       Iowa              190248   \n",
       "4            19       Iowa              190231   \n",
       "\n",
       "   number_of_vehicle_forms_submitted_all  \\\n",
       "0                                      1   \n",
       "1                                      1   \n",
       "2                                      1   \n",
       "3                                      1   \n",
       "4                                      1   \n",
       "\n",
       "   number_of_motor_vehicles_in_transport_mvit  \\\n",
       "0                                           1   \n",
       "1                                           1   \n",
       "2                                           1   \n",
       "3                                           1   \n",
       "4                                           1   \n",
       "\n",
       "   number_of_parked_working_vehicles  \\\n",
       "0                                  0   \n",
       "1                                  0   \n",
       "2                                  0   \n",
       "3                                  0   \n",
       "4                                  0   \n",
       "\n",
       "   number_of_forms_submitted_for_persons_not_in_motor_vehicles  \\\n",
       "0                                                  0             \n",
       "1                                                  0             \n",
       "2                                                  0             \n",
       "3                                                  0             \n",
       "4                                                  0             \n",
       "\n",
       "   number_of_persons_not_in_motor_vehicles_in_transport_mvit  \\\n",
       "0                                                  0           \n",
       "1                                                  0           \n",
       "2                                                  0           \n",
       "3                                                  0           \n",
       "4                                                  0           \n",
       "\n",
       "   number_of_persons_in_motor_vehicles_in_transport_mvit  \\\n",
       "0                                                  1       \n",
       "1                                                  1       \n",
       "2                                                  2       \n",
       "3                                                  4       \n",
       "4                                                  1       \n",
       "\n",
       "   number_of_forms_submitted_for_persons_in_motor_vehicles  ...  \\\n",
       "0                                                  1        ...   \n",
       "1                                                  1        ...   \n",
       "2                                                  2        ...   \n",
       "3                                                  4        ...   \n",
       "4                                                  1        ...   \n",
       "\n",
       "   minute_of_ems_arrival_at_hospital  related_factors_crash_level_1  \\\n",
       "0                                  2                              0   \n",
       "1                                 88                              0   \n",
       "2                                  1                              0   \n",
       "3                                 99                              0   \n",
       "4                                 88                              0   \n",
       "\n",
       "   related_factors_crash_level_1_name  related_factors_crash_level_2  \\\n",
       "0                                None                              0   \n",
       "1                                None                              0   \n",
       "2                                None                              0   \n",
       "3                                None                              0   \n",
       "4                                None                              0   \n",
       "\n",
       "   related_factors_crash_level_2_name  related_factors_crash_level_3  \\\n",
       "0                                None                              0   \n",
       "1                                None                              0   \n",
       "2                                None                              0   \n",
       "3                                None                              0   \n",
       "4                                None                              0   \n",
       "\n",
       "   related_factors_crash_level_3_name  number_of_fatalities  \\\n",
       "0                                None                     1   \n",
       "1                                None                     1   \n",
       "2                                None                     1   \n",
       "3                                None                     2   \n",
       "4                                None                     1   \n",
       "\n",
       "   number_of_drunk_drivers        timestamp_of_crash  \n",
       "0                        1 2015-09-11 20:20:00+00:00  \n",
       "1                        1 2015-11-01 00:30:00+00:00  \n",
       "2                        0 2015-05-04 16:18:00+00:00  \n",
       "3                        0 2015-11-17 12:26:00+00:00  \n",
       "4                        0 2015-10-31 04:49:00+00:00  \n",
       "\n",
       "[5 rows x 70 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from google.cloud import bigquery\n",
    "\n",
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()\n",
    "\n",
    "# Construct a reference to the \"nhtsa_traffic_fatalities\" dataset\n",
    "dataset_ref = client.dataset(\"nhtsa_traffic_fatalities\", project=\"bigquery-public-data\")\n",
    "dataset = client.get_dataset(dataset_ref)\n",
    "\n",
    "# Construct a reference to the \"accident_2015\" table\n",
    "table_ref = dataset_ref.table(\"accident_2015\")\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the \"accident_2015\" table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Xem bảng ta thấy:\n",
    "* cột `consecutive_number` chứa ID duy nhất cho mỗi tai nạn và\n",
    "* cột `timestamp_of_crash` chứa ngày của tai nạn theo định dạng DATETIME\n",
    "\n",
    "vì vậy ta có thể\n",
    "* **EXTRACT** ngày trong tuần từ cột `timestamp_of_crash` và\n",
    "* **GROUP BY** ngày trong tuần trước khi **COUNT** cột `consecutive_number` để xác định số tai nạn cho mỗi ngày trong tuần.\n",
    "* sau đó sắp xếp với **ORDER BY** theo số vụ tai nạn giảm dần"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to find out the number of accidents for each day of the week\n",
    "query = \"\"\"\n",
    "        SELECT COUNT(consecutive_number) AS num_accidents, \n",
    "               EXTRACT(DAYOFWEEK FROM timestamp_of_crash) AS day_of_week\n",
    "        FROM `bigquery-public-data.nhtsa_traffic_fatalities.accident_2015`\n",
    "        GROUP BY day_of_week\n",
    "        ORDER BY num_accidents DESC\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "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>num_accidents</th>\n",
       "      <th>day_of_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5659</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5298</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4916</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4460</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4182</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4038</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3985</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_accidents  day_of_week\n",
       "0           5659            7\n",
       "1           5298            1\n",
       "2           4916            6\n",
       "3           4460            5\n",
       "4           4182            4\n",
       "5           4038            2\n",
       "6           3985            3"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**8)\n",
    "query_job = client.query(query, job_config=safe_config)\n",
    "accidents_by_day = query_job.to_dataframe()\n",
    "\n",
    "# Print the DataFrame\n",
    "accidents_by_day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Để biết ngày trả về này là thứ mấy, ta tham khảo [BigQuery document] (https://cloud.google.com/bigquery/docs/reference/legacy-sql#dayofweek) về hàm DAYOFWEEK.\n",
    "### Bài tập thực hành\n",
    "1. Hãy thử với một dataset khác từ Ngân hàng Thế giới (The World Bank) có tên `world_bank_intl_education`. Đây là dataset dữ liệu về giáo dục, ta sẽ quan tâm đến bảng `international_education`.\n",
    "Dùng `dataset_ref = client.dataset(\"world_bank_intl_education\", project=\"bigquery-public-data\")` và các ví dụ trên, hãy view 5 dòng đầu tiên của bảng `international_education`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_ref = client.dataset(\"world_bank_intl_education\", project=\"bigquery-public-data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "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>country_name</th>\n",
       "      <th>country_code</th>\n",
       "      <th>indicator_name</th>\n",
       "      <th>indicator_code</th>\n",
       "      <th>value</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>India</td>\n",
       "      <td>IND</td>\n",
       "      <td>GDP per capita (constant 2005 US$)</td>\n",
       "      <td>NY.GDP.PCAP.KD</td>\n",
       "      <td>1.861491e+03</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>India</td>\n",
       "      <td>IND</td>\n",
       "      <td>Population, ages 15-64, total</td>\n",
       "      <td>SP.POP.1564.TO</td>\n",
       "      <td>8.739087e+08</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Iraq</td>\n",
       "      <td>IRQ</td>\n",
       "      <td>Mortality rate, under-5 (per 1,000)</td>\n",
       "      <td>SH.DYN.MORT</td>\n",
       "      <td>3.120000e+01</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Iraq</td>\n",
       "      <td>IRQ</td>\n",
       "      <td>Population, female (% of total)</td>\n",
       "      <td>SP.POP.TOTL.FE.ZS</td>\n",
       "      <td>4.938656e+01</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kenya</td>\n",
       "      <td>KEN</td>\n",
       "      <td>Population of the official age for upper secon...</td>\n",
       "      <td>SP.SEC.UTOT.MA.IN</td>\n",
       "      <td>2.072105e+06</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country_name country_code  \\\n",
       "0        India          IND   \n",
       "1        India          IND   \n",
       "2         Iraq          IRQ   \n",
       "3         Iraq          IRQ   \n",
       "4        Kenya          KEN   \n",
       "\n",
       "                                      indicator_name     indicator_code  \\\n",
       "0                 GDP per capita (constant 2005 US$)     NY.GDP.PCAP.KD   \n",
       "1                      Population, ages 15-64, total     SP.POP.1564.TO   \n",
       "2                Mortality rate, under-5 (per 1,000)        SH.DYN.MORT   \n",
       "3                    Population, female (% of total)  SP.POP.TOTL.FE.ZS   \n",
       "4  Population of the official age for upper secon...  SP.SEC.UTOT.MA.IN   \n",
       "\n",
       "          value  year  \n",
       "0  1.861491e+03  2016  \n",
       "1  8.739087e+08  2016  \n",
       "2  3.120000e+01  2016  \n",
       "3  4.938656e+01  2016  \n",
       "4  2.072105e+06  2016  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# code ở đây, ta có DataFrame như dưới"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Giá trị trong cột `indicator_code` mô tả loại dữ liệu mà hàng đó hiển thị. Một mã mà ta quan tâm là `SE.XPD.TOTL.GD.ZS`, tương ứng với \"Government expenditure on education as % of GDP (%)\" hay phần % GDP chính phủ chi tiêu cho giáo dục.\n",
    "\n",
    "Câu hỏi ta quan tâm là: **Những nước nào tiêu nhiều tiền nhất cho giáo dục tính theo tỉ lệ % GDP trong khoảng thời gian từ 2010-2017?**\n",
    "\n",
    "Để trả lời câu hỏi này, ta chỉ cần lọc những hàng có mã `SE.XPD.TOTL.GD.ZS` và viết query trả về giá trị trung bình trong cột `value` cho mỗi nước trong dataset. Yêu cầu:\n",
    "* Kết quả phải có tên nước, chứ không phải mã nước (country_code). Mỗi hàng tương ứng với 1 nước\n",
    "* Hàm tổng hợp ở đây là **AVG()**, dùng tên edu_spending cho cột tạo bởi hàm này\n",
    "* Sắp xếp kết quả sao cho nước tiêu % lớn nhất ở trên đầu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code goes here\n",
    "country_spend_pct_query = \"\"\"\n",
    "                          SELECT \n",
    "                          FROM `bigquery-public-data.world_bank_intl_education.international_education`\n",
    "                          WHERE \n",
    "                          GROUP BY \n",
    "                          ORDER BY \n",
    "                          \"\"\"\n",
    "\n",
    "# limit set to 100 MB\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**8)\n",
    "country_spend_pct_query_job = client.query(country_spend_pct_query, job_config=safe_config)\n",
    "country_spending_results = country_spend_pct_query_job.to_dataframe()\n",
    "\n",
    "# View top few rows of results\n",
    "print(country_spending_results.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Có hàng nghìn codes như kiểu `SE.XPD.TOTL.GD.ZS` trong dataset, nhiều code thì chỉ phù hợp với vài nước. Hãy viết 1 query chọn các code và indicator name với ít nhất 175 dòng trong năm 2016\n",
    "Yêu cầu:\n",
    "    * Mỗi code là 1 dòng trong kết quả\n",
    "    * Các cột trong kết quả phải được gọi là `indicator_code`, `indicator_name` và `num_rows`\n",
    "    * Chỉ chọn các code xuất hiện ít nhất trong 175 dòng trong dataset\n",
    "    * Sắp xếp giảm dần"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "code_count_query = \"\"\"\n",
    "                   \"\"\"\n",
    "\n",
    "# Set up the query\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**8)\n",
    "code_count_query_job = client.query(code_count_query, job_config=safe_config)\n",
    "code_count_results = code_count_query_job.to_dataframe()\n",
    "\n",
    "print(code_count_results.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## As & With\n",
    "Với chỉ những gì chúng ta đã học, SQL query cũng đã có thể khá dài dòng, khiến cho việc đọc hiểu (để sửa lỗi) càng phức tạp. Ta có thể dùng **AS** và **WITH** để khiến câu lệnh sạch hơn, gọn hơn, dễ đọc hơn\n",
    "### AS\n",
    "AS còn được gọi là **aliasing** tương tự như `as` trong Python khi ta dùng `import pandas as pd`. Cách dùng trong SQL là ngay sau cột được select như đã thấy ở trên.\n",
    "### WITH ... AS\n",
    "**AS** kết hợp với **WITH** càng bá đạo hơn khi được dùng để tạo **CTE** hay **common table expression** tức là một bảng tạm được tạo ra trong query của bạn. CTEs hữu ích vì giúp chia query thành các khúc nhỏ dễ đọc hơn và ta có thể query tiếp trên chính chúng. Vd: với bảng sau\n",
    "![table](https://i.imgur.com/MXrsiAZ.png)\n",
    "ta có thể tạo bảng tạm với các con vật lớn tuổi rồi query trên bảng CTE đó\n",
    "![CTE](https://i.imgur.com/3xQZM4p.png)\n",
    "\n",
    "Ta có thể viết query này mà không dùng CTE nhưng nếu đây chỉ là một phần trong một query rất dài, bỏ qua CTE sẽ khiến câu lệnh rất khó đọc. *Lưu ý là CTEs chỉ tồn tại trong 1 query khác khi được tạo ra, và không thể được tham chiếu đến từ các query sau đó nữa.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bài tập thực hành\n",
    "Bây giờ ta sẽ làm việc với dataset về taxi trips trong thành phố Chicago"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_ref = client.dataset(\"chicago_taxi_trips\", project=\"bigquery-public-data\")\n",
    "dataset = client.get_dataset(dataset_ref)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trước khi truy cập được dữ liệu, ta cần tìm tên bảng, bạn có nhớ cách liệt kê tên bảng với biến `client` không? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_tables = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sau khi có được tên bảng, ta xem thử vài dòng đầu tiên để xem dữ liệu có vấn đề gì không."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_ref = \n",
    "table = \n",
    "client.list_rows(table, max_results=15).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Một trong những việc cần làm trước tiên là xác định xem dữ liệu có quá cũ, không được cập nhật không. Vì điều này có thể ảnh hưởng đến các giả định về giao thông gần đây. Viết một query đếm số chuyến taxi trong mỗi năm.\n",
    "\n",
    "Kết quả cần có 2 cột: `year` và `num_trips`\n",
    "Gợi ý:\n",
    "* Dùng hàm `EXTRACT(YEAR FROM xxx)` để lấy năm từ cột `trip_start_timestamp`\n",
    "* Với **FROM** thì lưu ý dấu backtick và format theo dạng chuỗi `project_name.dataset_name.table_name` với project là `bigquery-public-data`, và dataset là `chicago_taxi_trips`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rides_per_year_query = \"\"\"\n",
    "                        \"\"\"\n",
    "\n",
    "# Set up the quota\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**8)\n",
    "rides_per_year_query_job = # Your code goes here\n",
    "rides_per_year_result = # Your code goes here\n",
    "\n",
    "# View results\n",
    "print(rides_per_year_result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Kết quả nôm na sẽ là thế này\n",
    "```\n",
    "   year  num_trips\n",
    "0  2019   13973012\n",
    "1  2018   20732088\n",
    "2  2017   24988003\n",
    "3  2016   31759339\n",
    "4  2015   32385875\n",
    "5  2014   37395436\n",
    "6  2013   27217716\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Nếu muốn đào sâu hơn một chút vào những chuyến xe của năm 2017. Ta dùng query tương tự như trên và có 2 thay đổi nhỏ\n",
    "    * Dùng **WHERE** để giới hạn data chỉ trong năm 2017\n",
    "    * Extract và **GROUP BY** theo tháng thay vì theo năm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rides_per_month_query = \"\"\"\n",
    "                    \n",
    "                        \"\"\"\n",
    "\n",
    "# Set up the query\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**8)\n",
    "rides_per_month_query_job = # Your code goes here\n",
    "rides_per_month_result =  # Your code goes here\n",
    "\n",
    "# View results\n",
    "print(rides_per_month_result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Level cao hơn: viết query liệt kê số chuyến đi và tốc độ trung bình tương ứng trong mỗi giờ trong ngày. Kết quả nên có 3 cột:\n",
    "    * `hour_of_day` sắp xếp theo cột này, lấy từ cột `trip_start_timestamp`\n",
    "    * `num_trips` đếm tổng số chuyến trong mỗi giờ (nghĩa là có bao nhiêu chuyến đi bắt đầu từ giữa 6AM và 7AM, không phụ thuộc vào ngày nào xảy ra) \n",
    "    * `avg_mph` tốc độ trung bình, đo theo miles/giờ cho các chuyến đi trong khung giờ đó. Tốc độ trung bình được tính như sau: `3600 * SUM(trip_miles) / SUM(trip_seconds)`\n",
    "    \n",
    "    Một vài giới hạn dữ liệu:\n",
    "    * `trip_start_timestamp` giữa 2017-01-01 và 2017-07-01\n",
    "    * `trip_seconds > 0` và `trip_miles > 0`\n",
    "    dùng CTE để xác lập các giới hạn này. Vì dữ liệu này cực lớn, CTE này chỉ nên chọn các cột mà bạn cần cho kết quả cuối cùng (thực ra là ta sẽ tạo các cột kết quả trong SELECT phía dưới CTE)\n",
    "    \n",
    "    Good luck"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speeds_query = \"\"\"\n",
    "               WITH RelevantRides AS\n",
    "               (\n",
    "                   SELECT ____\n",
    "                   FROM ____\n",
    "                   WHERE ____\n",
    "               )\n",
    "               SELECT ______\n",
    "               FROM RelevantRides\n",
    "               GROUP BY ____\n",
    "               ORDER BY ____\n",
    "               \"\"\"\n",
    "\n",
    "# Set up the query\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**8)\n",
    "speeds_query_job = ____ # Your code here\n",
    "speeds_result = ____ # Your code here\n",
    "\n",
    "# View results\n",
    "print(speeds_result)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining Data\n",
    "Nếu dữ liệu nằm ở nhiều bảng khác nhau thì ta cần đến **JOIN**. Đây là một công cụ đặc biệt quan trọng trong SQL thực tế.\n",
    "\n",
    "Giả sử có 2 bảng `owner` và `pets`\n",
    "![table](https://i.imgur.com/eXvIORm.png)\n",
    "\n",
    "Chạy query với **INNER JOIN** ta kết hợp 2 bảng thành\n",
    "![join](https://i.imgur.com/fLlng42.png)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Thực hành cuối\n",
    "[Stack Overflow](https://stackoverflow.com/) là một trang ai cũng biết nếu là dân lập trình. Ta hãy xem có thể thiết lập một dịch vụ dựa trên dữ liệu public của họ để tìm những người dùng trong Stack Overflow chứng minh họ có kỹ năng với một công nghệ nào đó qua việc trả lời các câu hỏi liên quan đến lĩnh vực đó. Từ đó giới thiệu cho người có nhu cầu tuyển dụng."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_ref = client.dataset(\"stackoverflow\", project=\"bigquery-public-data\")\n",
    "dataset = client.get_dataset(dataset_ref)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Giống như trên, ta cần danh sách các bảng"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_tables = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Vì ta quan tâm đến người đã trả lời các câu hỏi liên quan đến một topic nào đó, bảng `posts_answers` là nơi đương nhiên phải nhìn."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "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>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>accepted_answer_id</th>\n",
       "      <th>answer_count</th>\n",
       "      <th>comment_count</th>\n",
       "      <th>community_owned_date</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>last_activity_date</th>\n",
       "      <th>last_edit_date</th>\n",
       "      <th>last_editor_display_name</th>\n",
       "      <th>last_editor_user_id</th>\n",
       "      <th>owner_display_name</th>\n",
       "      <th>owner_user_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>post_type_id</th>\n",
       "      <th>score</th>\n",
       "      <th>tags</th>\n",
       "      <th>view_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50075045</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;To return a json try to replace&lt;/p&gt;\\n\\n&lt;pre...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:36:26.320000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:36:26.320000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>5855039</td>\n",
       "      <td>50058348</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50075058</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;Very good practice is to import feature mod...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:37:48.770000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:37:48.770000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>6370870</td>\n",
       "      <td>42300636</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50075091</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;You can adjust your current query to get th...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:41:45.040000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:41:45.040000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>8024897</td>\n",
       "      <td>50031556</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50075112</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;Provided stacktrace related with the incomp...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:44:39.143000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:44:39.143000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2674303</td>\n",
       "      <td>50004504</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50075119</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;So it seems it just took quite a long time ...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 09:45:09.930000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-04-28 15:19:39.537000+00:00</td>\n",
       "      <td>2018-04-28 15:19:39.537000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>1843511.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1843511</td>\n",
       "      <td>50061108</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id title                                               body  \\\n",
       "0  50075045  None  <p>To return a json try to replace</p>\\n\\n<pre...   \n",
       "1  50075058  None  <p>Very good practice is to import feature mod...   \n",
       "2  50075091  None  <p>You can adjust your current query to get th...   \n",
       "3  50075112  None  <p>Provided stacktrace related with the incomp...   \n",
       "4  50075119  None  <p>So it seems it just took quite a long time ...   \n",
       "\n",
       "  accepted_answer_id answer_count  comment_count community_owned_date  \\\n",
       "0               None         None              0                 None   \n",
       "1               None         None              0                 None   \n",
       "2               None         None              0                 None   \n",
       "3               None         None              0                 None   \n",
       "4               None         None              0                 None   \n",
       "\n",
       "                     creation_date favorite_count  \\\n",
       "0 2018-04-28 09:36:26.320000+00:00           None   \n",
       "1 2018-04-28 09:37:48.770000+00:00           None   \n",
       "2 2018-04-28 09:41:45.040000+00:00           None   \n",
       "3 2018-04-28 09:44:39.143000+00:00           None   \n",
       "4 2018-04-28 09:45:09.930000+00:00           None   \n",
       "\n",
       "                last_activity_date                   last_edit_date  \\\n",
       "0 2018-04-28 09:36:26.320000+00:00                              NaT   \n",
       "1 2018-04-28 09:37:48.770000+00:00                              NaT   \n",
       "2 2018-04-28 09:41:45.040000+00:00                              NaT   \n",
       "3 2018-04-28 09:44:39.143000+00:00                              NaT   \n",
       "4 2018-04-28 15:19:39.537000+00:00 2018-04-28 15:19:39.537000+00:00   \n",
       "\n",
       "  last_editor_display_name  last_editor_user_id owner_display_name  \\\n",
       "0                     None                  NaN               None   \n",
       "1                     None                  NaN               None   \n",
       "2                     None                  NaN               None   \n",
       "3                     None                  NaN               None   \n",
       "4                     None            1843511.0               None   \n",
       "\n",
       "   owner_user_id  parent_id  post_type_id  score  tags view_count  \n",
       "0        5855039   50058348             2      0  None       None  \n",
       "1        6370870   42300636             2      0  None       None  \n",
       "2        8024897   50031556             2      0  None       None  \n",
       "3        2674303   50004504             2      0  None       None  \n",
       "4        1843511   50061108             2      0  None       None  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answers_table_ref = dataset_ref.table(\"posts_answers\")\n",
    "answers_table = client.get_table(answers_table_ref)\n",
    "\n",
    "# Preview the first five lines of the \"posts_answers\" table\n",
    "client.list_rows(answers_table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cũng chưa rõ làm sao để tìm những người trả lời câu hỏi ở topic nào đó. Nhưng bảng `post_answers` có cột `parent_id`. Nếu bạn quen thuộc với trang web này, bạn có thể sẽ nhận ra `parent_id` chính là câu hỏi mà được post hiện tại trả lời, nên ta cũng nhìn bảng này 1 chút"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "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>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>accepted_answer_id</th>\n",
       "      <th>answer_count</th>\n",
       "      <th>comment_count</th>\n",
       "      <th>community_owned_date</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>last_activity_date</th>\n",
       "      <th>last_edit_date</th>\n",
       "      <th>last_editor_display_name</th>\n",
       "      <th>last_editor_user_id</th>\n",
       "      <th>owner_display_name</th>\n",
       "      <th>owner_user_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>post_type_id</th>\n",
       "      <th>score</th>\n",
       "      <th>tags</th>\n",
       "      <th>view_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15432504</td>\n",
       "      <td>Div wont go into wrapper?</td>\n",
       "      <td>&lt;p&gt;I'm having a bit of trouble with a div, my ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-15 12:29:18.540000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-04-08 21:00:53.857000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2060014</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>csshtmlwrapper</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15437295</td>\n",
       "      <td>I want each HTML line to alternate in color in...</td>\n",
       "      <td>&lt;p&gt;I have scrolling HTML text in Actionscript ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-15 16:19:14.063000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-15 16:21:50.683000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2174752</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>htmlcolorstextboxalternate</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15459643</td>\n",
       "      <td>What kind of design pattern is this?</td>\n",
       "      <td>&lt;p&gt;On my site, I have features, which I want t...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-17 10:17:44.483000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-17 11:16:17.483000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>362378</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>ruby-on-railsrubydesign-patterns</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15475567</td>\n",
       "      <td>Sending post request no framework</td>\n",
       "      <td>&lt;pre&gt;&lt;code&gt;public class ClientTestHttp {\\n\\n  ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-18 11:20:10.303000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-19 08:50:05.527000+00:00</td>\n",
       "      <td>2013-03-18 11:24:52.963000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>852866.0</td>\n",
       "      <td>None</td>\n",
       "      <td>2140215</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>netty</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15475774</td>\n",
       "      <td>custom b2b app also in regular store</td>\n",
       "      <td>&lt;p&gt;I was wondering if it would be possible to ...</td>\n",
       "      <td>18689167.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-03-18 11:31:40.753000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-09-08 22:14:14.940000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2118320</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>iositunesappstore-approvalb2b</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                                              title  \\\n",
       "0  15432504                          Div wont go into wrapper?   \n",
       "1  15437295  I want each HTML line to alternate in color in...   \n",
       "2  15459643               What kind of design pattern is this?   \n",
       "3  15475567                  Sending post request no framework   \n",
       "4  15475774               custom b2b app also in regular store   \n",
       "\n",
       "                                                body  accepted_answer_id  \\\n",
       "0  <p>I'm having a bit of trouble with a div, my ...                 NaN   \n",
       "1  <p>I have scrolling HTML text in Actionscript ...                 NaN   \n",
       "2  <p>On my site, I have features, which I want t...                 NaN   \n",
       "3  <pre><code>public class ClientTestHttp {\\n\\n  ...                 NaN   \n",
       "4  <p>I was wondering if it would be possible to ...          18689167.0   \n",
       "\n",
       "   answer_count  comment_count community_owned_date  \\\n",
       "0             4              0                 None   \n",
       "1             1              0                 None   \n",
       "2             2              4                 None   \n",
       "3             1              0                 None   \n",
       "4             1              0                 None   \n",
       "\n",
       "                     creation_date favorite_count  \\\n",
       "0 2013-03-15 12:29:18.540000+00:00           None   \n",
       "1 2013-03-15 16:19:14.063000+00:00           None   \n",
       "2 2013-03-17 10:17:44.483000+00:00           None   \n",
       "3 2013-03-18 11:20:10.303000+00:00           None   \n",
       "4 2013-03-18 11:31:40.753000+00:00           None   \n",
       "\n",
       "                last_activity_date                   last_edit_date  \\\n",
       "0 2013-04-08 21:00:53.857000+00:00                              NaT   \n",
       "1 2013-03-15 16:21:50.683000+00:00                              NaT   \n",
       "2 2013-03-17 11:16:17.483000+00:00                              NaT   \n",
       "3 2013-03-19 08:50:05.527000+00:00 2013-03-18 11:24:52.963000+00:00   \n",
       "4 2013-09-08 22:14:14.940000+00:00                              NaT   \n",
       "\n",
       "  last_editor_display_name  last_editor_user_id owner_display_name  \\\n",
       "0                     None                  NaN               None   \n",
       "1                     None                  NaN               None   \n",
       "2                     None                  NaN               None   \n",
       "3                     None             852866.0               None   \n",
       "4                     None                  NaN               None   \n",
       "\n",
       "   owner_user_id parent_id  post_type_id  score  \\\n",
       "0        2060014      None             1      0   \n",
       "1        2174752      None             1      0   \n",
       "2         362378      None             1      0   \n",
       "3        2140215      None             1      0   \n",
       "4        2118320      None             1      0   \n",
       "\n",
       "                               tags  view_count  \n",
       "0                    csshtmlwrapper         256  \n",
       "1        htmlcolorstextboxalternate         256  \n",
       "2  ruby-on-railsrubydesign-patterns         256  \n",
       "3                             netty         256  \n",
       "4     iositunesappstore-approvalb2b         256  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions_table_ref = dataset_ref.table(\"posts_questions\")\n",
    "questions_table = client.get_table(questions_table_ref)\n",
    "\n",
    "# Preview the first five lines of the \"posts_questions\" table\n",
    "client.list_rows(questions_table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Nhìn vào 2 bảng này, bạn có thấy cột nào liên quan đến topic hay công nghệ của câu hỏi không? Nếu có, ta có thể tìm IDs của người dùng đã trả lời câu hỏi liên quan đó không?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Ta thấy rất nhiều dữ liệu ta cần là dạng text, nên ta cần thêm 1 công cụ nữa để giới hạn các kết quả trả về. Đó là **LIKE**, kết hợp với ký hiệu `%` đại diện cho bất kỳ số lượng chữ cái nào (`WHERE Name LIKE '%o%'` chạy với bảng pets ở trên sẽ cho ra 'Moon' và 'Tom'). Giờ hãy viết query chọn các cột `id`, `title` từ bảng `posts_questions` và giới hạn kết quả chứa từ \"bigquery\" trong cột `tags`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Ta muốn có danh sách các người dùng đã trả lời nhiều câu hỏi. Viết 1 hàm backend mà 1 website có thể gọi chứa query trả về 1 hàng cho mỗi người dùng đã trả lời ít nhất 1 câu hỏi với tag chứa chuỗi bất kỳ. Kết quả sẽ có 2 cột:\n",
    "    * `user_id` chứa `owner_user_id` từ bảng `post_answers`\n",
    "    * `number_of_answers` chứa số câu trả lời mà người dùng này đã viết liên quan đến từ khóa `topic` đưa vào. Gợi ý: ta có thể đưa tham số vào query với `{}` tương tự như cách Python truyền tham số."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def expert_finder(topic, client):\n",
    "    '''\n",
    "    Returns a DataFrame with the user IDs who have written Stack Overflow answers on a topic.\n",
    "\n",
    "    Inputs:\n",
    "        topic: A string with the topic of interest\n",
    "        client: A Client object that specifies the connection to the Stack Overflow dataset\n",
    "\n",
    "    Outputs:\n",
    "        results: A DataFrame with columns for user_id and number_of_answers. Follows similar logic to bigquery_experts_results shown above.\n",
    "        '''\n",
    "    my_query = \"\"\"\n",
    "                \"\"\"\n",
    "    \n",
    "    \n",
    "    results = \n",
    "    return results"
   ]
  }
 ],
 "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.6.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}