BigQuery-GCP-Solutions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1320056e-8da3-4842-aed1-7a11ba64b7f7",
   "metadata": {},
   "source": [
    "# Solutions to Workshop Exercises\n",
    "\n",
    "## **Exercise 3.1 Solution**\n",
    "\n",
    "**Query**: Find the top 10 subreddits discussing \"election\" the most between 2013 and 2015\\.\n",
    "\n",
    "```\n",
    "SELECT subreddit, COUNT(*) as comment_count\n",
    "FROM `pushshift.rt_reddit.comments`\n",
    "WHERE body LIKE '%election%'\n",
    "GROUP BY subreddit\n",
    "ORDER BY comment_count DESC\n",
    "LIMIT 10;\n",
    "```\n",
    "\n",
    "**Explanation**:\n",
    "\n",
    "- `body LIKE '%election%'`: Filters comments containing the word \"election\".\n",
    "\n",
    "## **Exercise 3.2 Solution**\n",
    "\n",
    "**Query**: Identify the most active users (authors) discussing \"election\".\n",
    "\n",
    "```\n",
    "SELECT author, COUNT(*) as comment_count\n",
    "FROM `pushshift.rt_reddit.comments`\n",
    "WHERE body LIKE '%election%'\n",
    "GROUP BY author\n",
    "ORDER BY comment_count DESC\n",
    "LIMIT 10;\n",
    "\n",
    "```\n",
    "\n",
    "## **Exercise 3.3 Solution**\n",
    "\n",
    "**Note**: Sentiment analysis requires additional tools or libraries.\n",
    "\n",
    "**Approach**:\n",
    "\n",
    "- Export comments containing \"election\" to a CSV file.  \n",
    "- Use Python with a library like `TextBlob` or `NLTK` to perform sentiment analysis.\n",
    "\n",
    "```py\n",
    "import csv\n",
    "from textblob import TextBlob\n",
    "\n",
    "# Read comments from CSV\n",
    "with open('election_comments.csv', 'r', encoding='utf-8') as csvfile:\n",
    "    reader = csv.DictReader(csvfile)\n",
    "    sentiments = []\n",
    "    for row in reader:\n",
    "        analysis = TextBlob(row['body'])\n",
    "        sentiments.append({\n",
    "            'comment_id': row['id'],\n",
    "            'sentiment': analysis.sentiment.polarity\n",
    "        })\n",
    "\n",
    "# Analyze average sentiment\n",
    "average_sentiment = sum([s['sentiment'] for s in sentiments]) / len(sentiments)\n",
    "print(f\"Average sentiment: {average_sentiment}\")\n",
    "```\n",
    "\n",
    "## **Exercise 4 Solution**\n",
    "\n",
    "**Objective**: Compare Reddit discussions with Wikipedia pageviews.\n",
    "\n",
    "Return to [Challenge Exercise 4](?tab=t.0#heading=h.p8i4kgv2vhyk)\n",
    "\n",
    "1. Identify Google Trends terms from the past week  \n",
    "2. Query the Reddit dataset from 2015-2016  \n",
    "3. Join with Reddit dataset with Google Trends to find articles that contain the terms from the trends\n",
    "\n",
    "**Query Steps**:\n",
    "\n",
    "1. **Get Top Trending terms in the past week:**\n",
    "\n",
    "```\n",
    "Top trending terms in the past week:\n",
    "\n",
    "-- Extract the top trending terms from the past week\n",
    "SELECT\n",
    "  refresh_date AS date,\n",
    "  term,\n",
    "  SUM(score) AS trend_score\n",
    "FROM\n",
    "  `bigquery-public-data.google_trends.top_terms`\n",
    "WHERE\n",
    "  refresh_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()\n",
    "GROUP BY\n",
    "  date, term\n",
    "ORDER BY\n",
    "  trend_score DESC\n",
    "LIMIT 50;  -- Adjust the limit to see more or fewer top trends\n",
    "\n",
    "```\n",
    "\n",
    "Full Solution:\n",
    "\n",
    "```\n",
    "-- Step 1: Extract Google Trends data for the week from Nov 1 to Nov 7, 2024\n",
    "WITH google_trends AS (\n",
    "  SELECT\n",
    "    refresh_date AS date,\n",
    "    term,\n",
    "    SUM(score) AS trend_score\n",
    "  FROM\n",
    "    `bigquery-public-data.google_trends.top_terms`\n",
    "  WHERE\n",
    "    refresh_date BETWEEN '2024-11-01' AND '2024-11-07'\n",
    "  GROUP BY\n",
    "    date, term\n",
    "),\n",
    "\n",
    "-- Step 2: Extract Reddit comments from 2016 containing election-related keywords\n",
    "reddit_data AS (\n",
    "  SELECT\n",
    "    DATE(created_utc) AS date,\n",
    "    body,\n",
    "    subreddit,\n",
    "    COUNT(*) OVER (PARTITION BY DATE(created_utc)) AS reddit_comment_count\n",
    "  FROM\n",
    "    `pushshift.rt_reddit.comments`\n",
    "  WHERE\n",
    "    subreddit IN ('politics', 'The_Donald', 'news', 'worldnews')  -- Example subreddits\n",
    ")\n",
    "\n",
    "-- Step 3: Join Google Trends data with Reddit comments data from 2016 to find comments containing the trending terms\n",
    "SELECT\n",
    "  reddit_data.date AS reddit_date,\n",
    "  reddit_data.subreddit,\n",
    "  reddit_data.body AS comment,\n",
    "  google_trends.term AS trending_term,\n",
    "  google_trends.trend_score\n",
    "FROM\n",
    "  reddit_data\n",
    "INNER JOIN\n",
    "  google_trends\n",
    "ON\n",
    "  LOWER(reddit_data.body) LIKE CONCAT('%', LOWER(google_trends.term), '%')  -- Case-insensitive match of trending terms in comments\n",
    "ORDER BY\n",
    "  reddit_data.date DESC;\n",
    "\n",
    "```\n",
    "\n",
    "Additional Solutions\n",
    "\n",
    "2. **Get Daily Comment Counts from Reddit**:\n",
    "\n",
    "```\n",
    "SELECT\n",
    " DATE(TIMESTAMP(created_utc)) as date,\n",
    " COUNT(*) as reddit_comment_count\n",
    "FROM `pushshift.rt_reddit.comments`\n",
    "WHERE body LIKE '%election%'\n",
    "GROUP BY date\n",
    "```\n",
    "\n",
    "3. **Get Daily Pageviews from Wikipedia**:\n",
    "\n",
    "```\n",
    "SELECT\n",
    " DATE(datehour) as date,\n",
    " SUM(views) as wiki_pageviews\n",
    "FROM `bigquery-public-data.wikipedia.pageviews_2016`\n",
    "WHERE REGEXP_CONTAINS(title, r'(?i)election')\n",
    "AND DATE(datehour)<>current_date()\n",
    "GROUP BY date\n",
    "\n",
    "```\n",
    "\n",
    "4. **Join the Two Results**:\n",
    "\n",
    "```\n",
    "WITH reddit_data AS (\n",
    "  -- Reddit query here\n",
    "),\n",
    "wiki_data AS (\n",
    "  -- Wikipedia query here\n",
    ")\n",
    "SELECT\n",
    "  reddit_data.date,\n",
    "  reddit_data.reddit_comment_count,\n",
    "  wiki_data.wiki_pageviews\n",
    "FROM reddit_data\n",
    "JOIN wiki_data ON reddit_data.date = wiki_data.date\n",
    "ORDER BY reddit_data.date;\n",
    "```\n",
    "\n",
    "Complete solution:\n",
    "\n",
    "```\n",
    "WITH reddit_data AS (\n",
    "SELECT\n",
    " DATE(TIMESTAMP(created_utc)) as date,\n",
    " COUNT(*) as reddit_comment_count\n",
    "FROM `pushshift.rt_reddit.comments`\n",
    "WHERE body LIKE '%election%'\n",
    "GROUP BY date\n",
    "),\n",
    "wiki_data AS (\n",
    "SELECT\n",
    " DATE(datehour) as date,\n",
    " SUM(views) as wiki_pageviews\n",
    "FROM `bigquery-public-data.wikipedia.pageviews_2015`\n",
    "WHERE REGEXP_CONTAINS(title, r'(?i)election')\n",
    "AND DATE(datehour)<>current_date()\n",
    "GROUP BY date\n",
    ")\n",
    "SELECT\n",
    "  reddit_data.date,\n",
    "  reddit_data.reddit_comment_count,\n",
    "  wiki_data.wiki_pageviews\n",
    "FROM reddit_data\n",
    "JOIN wiki_data ON reddit_data.date = wiki_data.date\n",
    "ORDER BY reddit_data.date;\n",
    "```\n",
    "\n",
    "## **Exercise 5.1 Solution**\n",
    "\n",
    "**Query**: Find companies in California in the Tech industry.\n",
    "\n",
    "```\n",
    "SELECT *\n",
    "FROM `your_project.dnb_data.companies`\n",
    "WHERE state = 'CA'\n",
    "  AND industry = 'Technology';\n",
    "```\n",
    "\n",
    "## **Exercise 6 Solution**\n",
    "\n",
    "**Modified Python Script to Save Data**:\n",
    "\n",
    "```py\n",
    "import praw\n",
    "import csv\n",
    "import datetime\n",
    "\n",
    "reddit = praw.Reddit(\n",
    "    client_id='YOUR_CLIENT_ID',\n",
    "    client_secret='YOUR_CLIENT_SECRET',\n",
    "    user_agent='WorkshopScript by /u/YourRedditUsername'\n",
    ")\n",
    "\n",
    "subreddit = reddit.subreddit('all')\n",
    "with open('recent_election_comments.csv', 'w', newline='', encoding='utf-8') as csvfile:\n",
    "    fieldnames = ['comment_id', 'author', 'body', 'created_utc']\n",
    "    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\n",
    "    writer.writeheader()\n",
    "\n",
    "    for comment in subreddit.stream.comments(skip_existing=True):\n",
    "        if 'election' in comment.body.lower():\n",
    "            writer.writerow({\n",
    "                'comment_id': comment.id,\n",
    "                'author': str(comment.author),\n",
    "                'body': comment.body,\n",
    "                'created_utc': datetime.datetime.utcfromtimestamp(comment.created_utc).isoformat()\n",
    "            })\n",
    "```\n",
    "\n",
    "## **Exercise 7 Solution**\n",
    "\n",
    "**Load Scraped Data into BigQuery**:\n",
    "\n",
    "1. **Prepare the Data**:  \n",
    "     \n",
    "   - Ensure `recent_election_comments.csv` is properly formatted.\n",
    "\n",
    "   \n",
    "\n",
    "2. **Load Data**:  \n",
    "     \n",
    "   - In BigQuery, create a new table under your dataset.  \n",
    "   - Use **Create Table From**: Upload.  \n",
    "   - Select your CSV file.  \n",
    "   - Let BigQuery auto-detect schema.\n",
    "\n",
    "   \n",
    "\n",
    "3. **Verify Data**:  \n",
    "     \n",
    "   - Run a simple `SELECT` query to ensure data integrity.\n",
    "\n",
    "## **Exercise 8.1 Solution**\n",
    "\n",
    "**Query**: Find mentions of companies in Reddit comments.\n",
    "\n",
    "```\n",
    "SELECT\n",
    "  c.company_name,\n",
    "  COUNT(r.comment_id) as mention_count\n",
    "FROM `your_project.dnb_data.companies` c\n",
    "JOIN `your_project.your_dataset.reddit_comments` r\n",
    "ON REGEXP_CONTAINS(r.body, CONCAT('(?i)', c.company_name))\n",
    "GROUP BY c.company_name\n",
    "ORDER BY mention_count DESC;\n",
    "```\n",
    "\n",
    "**Note**:\n",
    "\n",
    "- Ensure that both datasets are properly loaded and accessible.  \n",
    "- This query can be resource-intensive due to regular expression matching on large text fields.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e63f26db-50e5-4549-9303-78e26072bf36",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}