{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"**This notebook is an exercise in the [Advanced SQL](https://www.kaggle.com/learn/advanced-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/alexisbcook/joins-and-unions).**\n\n---\n","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"# Introduction\n\nHere, you'll use different types of SQL **JOINs** to answer questions about the [Stack Overflow](https://www.kaggle.com/stackoverflow/stackoverflow) dataset.\n\nBefore you get started, run the following cell to set everything up.","metadata":{}},{"cell_type":"code","source":"# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql_advanced.ex1 import *\nprint(\"Setup Complete\")","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"The code cell below fetches the `posts_questions` table from the `stackoverflow` dataset.  We also preview the first five rows of the table.","metadata":{}},{"cell_type":"code","source":"from google.cloud import bigquery\n\n# Create a \"Client\" object\nclient = bigquery.Client()\n\n# Construct a reference to the \"stackoverflow\" dataset\ndataset_ref = client.dataset(\"stackoverflow\", project=\"bigquery-public-data\")\n\n# API request - fetch the dataset\ndataset = client.get_dataset(dataset_ref)\n\n# Construct a reference to the \"posts_questions\" table\ntable_ref = dataset_ref.table(\"posts_questions\")\n\n# API request - fetch the table\ntable = client.get_table(table_ref)\n\n# Preview the first five lines of the table\nclient.list_rows(table, max_results=5).to_dataframe()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We also take a look at the `posts_answers` table.","metadata":{}},{"cell_type":"code","source":"# Construct a reference to the \"posts_answers\" table\ntable_ref = dataset_ref.table(\"posts_answers\")\n\n# API request - fetch the table\ntable = client.get_table(table_ref)\n\n# Preview the first five lines of the table\nclient.list_rows(table, max_results=5).to_dataframe()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You will work with both of these tables to answer the questions below.\n\n# Exercises\n\n### 1) How long does it take for questions to receive answers?\n\nYou're interested in exploring the data to have a better understanding of how long it generally takes for questions to receive answers.  Armed with this knowledge, you plan to use this information to better design the order in which questions are presented to Stack Overflow users.\n\nWith this goal in mind, you write the query below, which focuses on questions asked in January 2018.  It returns a table with two columns:\n- `q_id` - the ID of the question\n- `time_to_answer` - how long it took (in seconds) for the question to receive an answer\n\nRun the query below (without changes), and take a look at the output.","metadata":{}},{"cell_type":"code","source":"first_query = \"\"\"\n              SELECT q.id AS q_id,\n                  MIN(TIMESTAMP_DIFF(a.creation_date, q.creation_date, SECOND)) as time_to_answer\n              FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n                  INNER JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n              ON q.id = a.parent_id\n              WHERE q.creation_date >= '2018-01-01' and q.creation_date < '2018-02-01'\n              GROUP BY q_id\n              ORDER BY time_to_answer\n              \"\"\"\n\nfirst_result = client.query(first_query).result().to_dataframe()\nprint(\"Percentage of answered questions: %s%%\" % \\\n      (sum(first_result[\"time_to_answer\"].notnull()) / len(first_result) * 100))\nprint(\"Number of questions:\", len(first_result))\nfirst_result.head()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"You're surprised at the results and strongly suspect that something is wrong with your query.  In particular,\n- According to the query, 100% of the questions from January 2018 received an answer.  But, you know that ~80% of the questions on the site usually receive an answer.\n- The total number of questions is surprisingly low.  You expected to see at least 150,000 questions represented in the table.\n\nGiven these observations, you think that the type of **JOIN** you have chosen has inadvertently excluded unanswered questions.  Using the code cell below, can you figure out what type of **JOIN** to use to fix the problem so that the table includes unanswered questions?\n\n**Note**: You need only amend the type of **JOIN** (i.e., **INNER**, **LEFT**, **RIGHT**, or **FULL**) to answer the question successfully.","metadata":{}},{"cell_type":"code","source":"# Your code here\ncorrect_query = \"\"\"\n                \"\"\"\n\n# Check your answer\nq_1.check()\n\n# Run the query, and return a pandas DataFrame\ncorrect_result = client.query(correct_query).result().to_dataframe()\nprint(\"Percentage of answered questions: %s%%\" % \\\n      (sum(correct_result[\"time_to_answer\"].notnull()) / len(correct_result) * 100))\nprint(\"Number of questions:\", len(correct_result))","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_1.hint()\n#q_1.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2) Initial questions and answers, Part 1\n\nYou're interested in understanding the initial experiences that users typically have with the Stack Overflow website.  Is it more common for users to first ask questions or provide answers?  After signing up, how long does it take for users to first interact with the website?  To explore this further, you draft the (partial) query in the code cell below.\n\nThe query returns a table with three columns:\n- `owner_user_id` - the user ID\n- `q_creation_date` - the first time the user asked a question \n- `a_creation_date` - the first time the user contributed an answer \n\nYou want to keep track of users who have asked questions, but have yet to provide answers.  And, your table should also include users who have answered questions, but have yet to pose their own questions.  \n\nWith this in mind, please fill in the appropriate **JOIN** (i.e., **INNER**, **LEFT**, **RIGHT**, or **FULL**) to return the correct information.  \n\n**Note**: You need only fill in the appropriate **JOIN**.  All other parts of the query should be left as-is.  (You also don't need to write any additional code to run the query, since the `check()` method will take care of this for you.)\n\nTo avoid returning too much data, we'll restrict our attention to questions and answers posed in January 2019.  We'll amend the timeframe in Part 2 of this question to be more realistic!","metadata":{}},{"cell_type":"code","source":"# Your code here\nq_and_a_query = \"\"\"\n                SELECT q.owner_user_id AS owner_user_id,\n                    MIN(q.creation_date) AS q_creation_date,\n                    MIN(a.creation_date) AS a_creation_date\n                FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n                    ____ `bigquery-public-data.stackoverflow.posts_answers` AS a\n                ON q.owner_user_id = a.owner_user_id \n                WHERE q.creation_date >= '2019-01-01' AND q.creation_date < '2019-02-01' \n                    AND a.creation_date >= '2019-01-01' AND a.creation_date < '2019-02-01'\n                GROUP BY owner_user_id\n                \"\"\"\n\n# Check your answer\nq_2.check()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_2.hint()\n#q_2.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3) Initial questions and answers, Part 2\n\nNow you'll address a more realistic (and complex!) scenario.  To answer this question, you'll need to pull information from *three* different tables!  This syntax very similar to the case when we have to join only two tables.  For instance, consider the three tables below.\n\n![three tables](https://i.imgur.com/OyhYtD1.png)\n\nWe can use two different **JOINs** to link together information from all three tables, in a single query.\n\n![double join](https://i.imgur.com/G6buS7P.png)\n\nWith this in mind, say you're interested in understanding users who joined the site in January 2019.  You want to track their activity on the site: when did they post their first questions and answers, if ever?\n\nWrite a query that returns the following columns:\n- `id` - the IDs of all users who created Stack Overflow accounts in January 2019 (January 1, 2019, to January 31, 2019, inclusive)\n- `q_creation_date` - the first time the user posted a question on the site; if the user has never posted a question, the value should be null\n- `a_creation_date` - the first time the user posted a question on the site; if the user has never posted a question, the value should be null\n\nNote that questions and answers posted after January 31, 2019, should still be included in the results.  And, all users who joined the site in January 2019 should be included (even if they have never posted a question or provided an answer).\n\nThe query from the previous question should be a nice starting point to answering this question!  You'll need to use the `posts_answers` and `posts_questions` tables.  You'll also need to use the `users` table from the Stack Overflow dataset.  The relevant columns from the `users` table are `id` (the ID of each user) and `creation_date` (when the user joined the Stack Overflow site, in DATETIME format).","metadata":{}},{"cell_type":"code","source":"# Your code here\nthree_tables_query = \"\"\"\n                     \"\"\"\n\n# Check your answer\nq_3.check()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_3.hint()\n#q_3.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4) How many distinct users posted on January 1, 2019?\n\nIn the code cell below, write a query that returns a table with a single column:\n- `owner_user_id` - the IDs of all users who posted at least one question or answer on January 1, 2019.  Each user ID should appear at most once.\n\nIn the `posts_questions` (and `posts_answers`) tables, you can get the ID of the original poster from the `owner_user_id` column.  Likewise, the date of the original posting can be found in the `creation_date` column.  \n\nIn order for your answer to be marked correct, your query must use a **UNION**.","metadata":{}},{"cell_type":"code","source":"# Your code here\nall_users_query = \"\"\"\n                  \"\"\"\n\n# Check your answer\nq_4.check()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_4.hint()\n#q_4.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Keep going\n\nLearn how to use **[analytic functions](https://www.kaggle.com/alexisbcook/analytic-functions)** to perform complex calculations with minimal SQL code.","metadata":{}},{"cell_type":"markdown","source":"---\n\n\n\n\n*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/advanced-sql/discussion) to chat with other learners.*","metadata":{}}]}