sql_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Assessment 1  (20 marks)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing queries [12 marks]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adapt this code to connect to your newly created database\n",
    "import pymysql\n",
    "import credentials\n",
    "\n",
    "password = credentials.MYSQL_PASSWORD\n",
    "\n",
    "# Connect to the database\n",
    "connection = pymysql.connect(host=credentials.HOST_NAME,\n",
    "                             user=credentials.USERNAME,\n",
    "                             password=password,\n",
    "                             db=credentials.DB_NAME,\n",
    "                             charset='utf8mb4',\n",
    "                             cursorclass=pymysql.cursors.DictCursor)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Questions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1 - Users with highest scores over time** [0.5 marks]\n",
    "- Implement a query that returns the users with the highest aggregate scores (over all their posts) for the whole dataset. You should restrict your results to only those whose aggregated score is above 10,000 points, in descending order. Your query should return two columns: `username` and `aggr_scores`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def users_with_best_scores():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT user_name AS username, SUM(score) AS aggr_scores \n",
    "        FROM posts INNER JOIN users ON posts.author_ID=users.user_ID \n",
    "        GROUP BY author_ID \n",
    "        HAVING aggr_scores>10000 \n",
    "        ORDER BY aggr_scores DESC;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'username': 'DaFunkJunkie', 'aggr_scores': Decimal('250375')},\n",
       " {'username': 'None', 'aggr_scores': Decimal('218846')},\n",
       " {'username': 'SUPERGUESSOUS', 'aggr_scores': Decimal('211611')},\n",
       " {'username': 'jigsawmap', 'aggr_scores': Decimal('210824')},\n",
       " {'username': 'chrisdh79', 'aggr_scores': Decimal('143538')},\n",
       " {'username': 'hildebrand_rarity', 'aggr_scores': Decimal('122464')},\n",
       " {'username': 'iSlingShlong', 'aggr_scores': Decimal('118595')},\n",
       " {'username': 'hilltopye', 'aggr_scores': Decimal('81245')},\n",
       " {'username': 'tefunka', 'aggr_scores': Decimal('79560')},\n",
       " {'username': 'OldFashionedJizz', 'aggr_scores': Decimal('64398')},\n",
       " {'username': 'JLBesq1981', 'aggr_scores': Decimal('58235')},\n",
       " {'username': 'rspix000', 'aggr_scores': Decimal('57107')},\n",
       " {'username': 'Wagamaga', 'aggr_scores': Decimal('47989')},\n",
       " {'username': 'stem12345679', 'aggr_scores': Decimal('47455')},\n",
       " {'username': 'TheJeck', 'aggr_scores': Decimal('26058')},\n",
       " {'username': 'TheGamerDanYT', 'aggr_scores': Decimal('25357')},\n",
       " {'username': 'TrumpSharted', 'aggr_scores': Decimal('21154')},\n",
       " {'username': 'NotsoPG', 'aggr_scores': Decimal('18518')},\n",
       " {'username': 'SonictheManhog', 'aggr_scores': Decimal('18116')},\n",
       " {'username': 'BlanketMage', 'aggr_scores': Decimal('13677')},\n",
       " {'username': 'NewAltWhoThis', 'aggr_scores': Decimal('12771')},\n",
       " {'username': 'kevinmrr', 'aggr_scores': Decimal('11900')},\n",
       " {'username': 'Dajakesta0624', 'aggr_scores': Decimal('11613')},\n",
       " {'username': 'apocalypticalley', 'aggr_scores': Decimal('10382')}]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_with_best_scores()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**2 - Favourited subreddits with numbers _but not 19_** [0.5 marks]\n",
    "- Implement a query that returns the set of subreddit names who have been favorited at least once and that contain any number in their name, but you should exclude those with the digit '19', as we want to filter out COVID-19 subreddit names. Your query should only return one column: `subreddit`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def faved_subreddits_with_numbers_in_name():\n",
    "    # Adapted from https://www.tutorialspoint.com/check-if-a-string-contains-numbers-in-mysql\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT subr_name AS subreddit \n",
    "        FROM subreddits INNER JOIN favourites ON subreddits.subr_ID=favourites.subr_ID \n",
    "        GROUP BY subreddit \n",
    "        HAVING subreddit REGEXP '[0-9]' AND subreddit NOT LIKE '%19%' \n",
    "        ORDER BY COUNT(*) DESC;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    \n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'subreddit': 'l4d2'}, {'subreddit': '40kLore'}, {'subreddit': 'Fusion360'}]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "faved_subreddits_with_numbers_in_name()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3 - Most active users who add subreddits to their favorites.** [0.5 marks]\n",
    "- Implement a query that returns the top 20 users in terms of the number of subreddits they have favourited. Since several users have favourited the same number of subreddits, you need to order your results, first, by number of favourites per user, and secondly, alphabetically by user name. The alphabetical order should be, first any number, then A-Z (irrespective of case). Your query should return two columns: `username` and `numb_favs`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def users_that_favourite_the_most():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT user_name AS username, COUNT(*) AS numb_favs \n",
    "        FROM users INNER JOIN favourites ON users.user_ID=favourites.user_ID \n",
    "        GROUP BY username \n",
    "        ORDER BY numb_favs DESC, username \n",
    "        LIMIT 20;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'username': 'magnusthered15', 'numb_favs': 7},\n",
       " {'username': 'Flippy-Fish', 'numb_favs': 6},\n",
       " {'username': 'FriendlyVegetable420', 'numb_favs': 6},\n",
       " {'username': 'hmhmhm2', 'numb_favs': 6},\n",
       " {'username': 'KarmaFury', 'numb_favs': 6},\n",
       " {'username': 'OmniusQubus', 'numb_favs': 6},\n",
       " {'username': 'ry_ta506', 'numb_favs': 6},\n",
       " {'username': 'SaneFive', 'numb_favs': 6},\n",
       " {'username': 'SimonWolfson', 'numb_favs': 6},\n",
       " {'username': '26Point2', 'numb_favs': 5},\n",
       " {'username': 'Altruistic_Astronaut', 'numb_favs': 5},\n",
       " {'username': 'backpackwayne', 'numb_favs': 5},\n",
       " {'username': 'BanditisaDorito', 'numb_favs': 5},\n",
       " {'username': 'blue4029', 'numb_favs': 5},\n",
       " {'username': 'brad4711', 'numb_favs': 5},\n",
       " {'username': 'CuteBananaMuffin', 'numb_favs': 5},\n",
       " {'username': 'DickStealer69', 'numb_favs': 5},\n",
       " {'username': 'Dio_ships_RenMari', 'numb_favs': 5},\n",
       " {'username': 'FCK12_13', 'numb_favs': 5},\n",
       " {'username': 'GangstaRIB', 'numb_favs': 5}]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_that_favourite_the_most()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**4 - Awarded posts** [0.5 marks]\n",
    "- Implement a query that returns the number of posts who have received at least one award. Your query should return only one value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def awarded_posts():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT COUNT(*) AS number_of_awarded_posts \n",
    "        FROM posts \n",
    "        WHERE total_awards>0;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'number_of_awarded_posts': 119}]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "awarded_posts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5 - Find Covid subreddits in name and description.** [1 mark]\n",
    "- Implement a query that retrieves the name and description of all subreddits where the name starts with _covid_ or _corona_ and the description contains _covid_ anywhere. The returned table should have two columns: `name` and `description`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def covid_subreddits():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT subr_name AS name, subr_description AS description \n",
    "        FROM subreddits \n",
    "        WHERE (subr_name LIKE 'covid%' OR subr_name LIKE 'corona%') AND subr_description LIKE '%covid%';\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'Coronavirus',\n",
       "  'description': 'Place to discuss all things COVID-related'},\n",
       " {'name': 'CoronavirusCA',\n",
       "  'description': 'Tracking the Coronavirus/Covid-19 outbreak in California'},\n",
       " {'name': 'CoronavirusUS',\n",
       "  'description': 'USA/Canada specific information on the coronavirus (SARS-CoV-2) that causes coronavirus disease 2019 (COVID-19)'},\n",
       " {'name': 'COVID19',\n",
       "  'description': 'In December 2019, SARS-CoV-2, the virus causing the disease COVID-19, emerged in the city of Wuhan, China. This subreddit seeks to facilitate scientific discussion of this global public health threat.'},\n",
       " {'name': 'COVID', 'description': 'COVID-19 News, Etc.'},\n",
       " {'name': 'CoronavirusDownunder',\n",
       "  'description': 'This subreddit is a place to share news, information, resources, and support that relate to the novel coronavirus SARS-CoV-2 and the disease it causes called COVID-19. The primary focus of this sub is to actively monitor the situation in Australia, but all posts on international news and other virus-related topics are welcome, to the extent they are beneficial in keeping those in Australia informed.'}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "covid_subreddits()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**6 - Find users in haystack** [1 mark]\n",
    "- Implement a query that retrieves _only the names_ of those users who have at least 3 posts with the same score as their number of comments, and their username contains the string _meme_ anywhere. Your returned table should contain only one column: `username`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def haystack():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT user_name AS username \n",
    "        FROM users INNER JOIN posts ON users.user_ID=posts.author_ID \n",
    "        WHERE num_comments=score AND user_name LIKE '%meme%' \n",
    "        GROUP BY username \n",
    "        HAVING COUNT(*)>2 \n",
    "        ORDER BY username;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'username': 'MemeWarriors'}, {'username': 'PublicMemeResource'}]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "haystack()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**7 - Subreddits with the highest average upvote ratio** [1 mark]\n",
    "- Implement a query that shows the 10 top subreddits in terms of the average upvote ratio of the users that posted in them. Your query should return two columns: `subr_name` and `avg_upv_ratio`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def avg_upvote_ratio_per_subreddit():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "\n",
    "        q=\"\"\"SELECT subr_name, AVG(user_upvote_ratio) AS avg_upv_ratio FROM (\n",
    "        SELECT DISTINCT subr_name, user_name, user_upvote_ratio \n",
    "        FROM users INNER JOIN posts ON users.user_ID=posts.author_ID \n",
    "            INNER JOIN subreddits ON posts.subreddit_ID=subreddits.subr_ID\n",
    "        ) AS ratios \n",
    "        GROUP BY subr_name\n",
    "        ORDER BY avg_upv_ratio DESC\n",
    "        LIMIT 10\"\"\" \n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'subr_name': 'opensource', 'avg_upv_ratio': 0.2762725353240967},\n",
       " {'subr_name': 'razer', 'avg_upv_ratio': 0.22541162371635437},\n",
       " {'subr_name': 'virginvschad', 'avg_upv_ratio': 0.18763145385310054},\n",
       " {'subr_name': 'wicked_edge', 'avg_upv_ratio': 0.17087983884490454},\n",
       " {'subr_name': 'sportsbook', 'avg_upv_ratio': 0.1502002626657486},\n",
       " {'subr_name': 'COVID', 'avg_upv_ratio': 0.1456541850098542},\n",
       " {'subr_name': 'NoNewNormal', 'avg_upv_ratio': 0.14300167109937437},\n",
       " {'subr_name': 'MensLib', 'avg_upv_ratio': 0.13606921335061392},\n",
       " {'subr_name': 'FigureSkating', 'avg_upv_ratio': 0.1114363431930542},\n",
       " {'subr_name': 'CoronavirusUS', 'avg_upv_ratio': 0.10128294451135224}]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg_upvote_ratio_per_subreddit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**8 - What are the chances** [1 mark]\n",
    "- Implement a query that finds those posts whose length (in number of characters) is exactly the same as the length of the description of the subreddit in which they were posted on. You should retrieve the following columns: `subreddit_name`, `posting_user`, `user_registered_at`, `post_full_text`, `post_description` and `dif` (which should show the difference in characters between the subreddit description and the post."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def what_are_the_chances():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT subr_name AS subreddit_name, user_name AS posting_user, DATE_FORMAT(user_registered_at, '%Y-%m-%d') AS user_registered_at, CONCAT(title, selftext) AS post_full_text, subr_description AS post_description, CHAR_LENGTH(subr_description)-CHAR_LENGTH(selftext)-CHAR_LENGTH(title) AS dif \n",
    "        FROM users INNER JOIN posts ON users.user_ID=posts.author_ID \n",
    "            INNER JOIN subreddits ON posts.subreddit_ID=subreddits.subr_ID \n",
    "        WHERE CHAR_LENGTH(subr_description)=CHAR_LENGTH(selftext) + CHAR_LENGTH(title)\n",
    "            AND selftext NOT LIKE '%[***]%' AND title NOT LIKE '%[***]%' AND subr_description NOT LIKE '%[***]%' \n",
    "            AND selftext NOT LIKE '%&amp;%' AND title NOT LIKE '%&amp;%' AND subr_description NOT LIKE '%&amp;%';\"\"\"\n",
    "\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results\n",
    "\n",
    "# Note that the presence of &amp in the text skews the length of the posts.\n",
    "# Therefore, all posts with &amp and [***] in the selftext or description were excluded, \n",
    "# since the length of any redacted segment could not be determined."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'subreddit_name': 'donaldtrump',\n",
       "  'posting_user': 'M_i_c_K',\n",
       "  'user_registered_at': '2010-08-06',\n",
       "  'post_full_text': 'NSFW- You Have Been Warned !',\n",
       "  'post_description': 'Subreddit about Donald Trump',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'donaldtrump',\n",
       "  'posting_user': 'TranqToTheJug',\n",
       "  'user_registered_at': '2011-10-14',\n",
       "  'post_full_text': 'In Plain Sight - A.F. Branco',\n",
       "  'post_description': 'Subreddit about Donald Trump',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'donaldtrump',\n",
       "  'posting_user': 'soldio101',\n",
       "  'user_registered_at': '2011-04-20',\n",
       "  'post_full_text': 'Trump Approval Index History',\n",
       "  'post_description': 'Subreddit about Donald Trump',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'Canuknucklehead',\n",
       "  'user_registered_at': '2011-12-06',\n",
       "  'post_full_text': 'When will a coronavirus vaccine be ready?',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'CaptainSkull2030',\n",
       "  'user_registered_at': '2013-12-04',\n",
       "  'post_full_text': 'READ: $2 Trillion Coronavirus Relief Bill',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'Exastiken',\n",
       "  'user_registered_at': '2011-03-10',\n",
       "  'post_full_text': 'Taiwan Inches Closer To COVID-19 Test Kit',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'Frocharocha',\n",
       "  'user_registered_at': '2012-10-10',\n",
       "  'post_full_text': 'All of next week’s UEFA matches postponed',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'GlobalConcentrate7',\n",
       "  'user_registered_at': '2012-03-25',\n",
       "  'post_full_text': 'Coronavirus deaths are rising in hotspots',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'PaulDB2019',\n",
       "  'user_registered_at': '2011-04-19',\n",
       "  'post_full_text': 'Michigan backers honk at stay-home orders',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'Underachlever',\n",
       "  'user_registered_at': '2011-10-10',\n",
       "  'post_full_text': 'New York City in the Coronavirus Pandemic',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'bustead',\n",
       "  'user_registered_at': '2014-10-04',\n",
       "  'post_full_text': 'Victoria reports nine new COVID-19 deaths',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'casualphilosopher1',\n",
       "  'user_registered_at': '2013-05-31',\n",
       "  'post_full_text': 'COVID-19: How Europe treated its refugees',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'coronaobserver',\n",
       "  'user_registered_at': '2010-07-25',\n",
       "  'post_full_text': 'Information doesn’t like to be contained.',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'kirby__000',\n",
       "  'user_registered_at': '2010-06-25',\n",
       "  'post_full_text': 'Coronavirus will radically alter the U.S.',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'maxxamus15',\n",
       "  'user_registered_at': '2011-11-23',\n",
       "  'post_full_text': 'Fed chairman on what congress needs to do',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'mchamst3r',\n",
       "  'user_registered_at': '2011-08-01',\n",
       "  'post_full_text': 'CDC warned of a coming pandemic two years',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'mohamedmordy',\n",
       "  'user_registered_at': '2010-08-06',\n",
       "  'post_full_text': \"Voir comment manger de l'ail 06 mars 2020\",\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'paulthemankind',\n",
       "  'user_registered_at': '2012-02-28',\n",
       "  'post_full_text': 'Wisconsin crosses 25000 coronavirus cases',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'shikhar_reddit123',\n",
       "  'user_registered_at': '2010-11-26',\n",
       "  'post_full_text': 'China Winning Propaganda Race Against USA',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'Coronavirus',\n",
       "  'posting_user': 'whatareyoutalkingman',\n",
       "  'user_registered_at': '2010-07-16',\n",
       "  'post_full_text': 'Swiss coronavirus death toll rises to 831',\n",
       "  'post_description': 'Place to discuss all things COVID-related',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'China_Flu',\n",
       "  'posting_user': 'BillyloveCali',\n",
       "  'user_registered_at': '2013-11-19',\n",
       "  'post_full_text': 'Wuhan doctor talks about the coronavirus he knows.',\n",
       "  'post_description': 'COVID-19 (2019-nCoV) Wuhan Coronavirus Information',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'China_Flu',\n",
       "  'posting_user': 'PapayaSF',\n",
       "  'user_registered_at': '2010-09-10',\n",
       "  'post_full_text': 'How to Test Every American for COVID-19, Every Day',\n",
       "  'post_description': 'COVID-19 (2019-nCoV) Wuhan Coronavirus Information',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'China_Flu',\n",
       "  'posting_user': 'bradipaurbana',\n",
       "  'user_registered_at': '2014-06-16',\n",
       "  'post_full_text': 'Three of 4 cases in Ethiopia are Japanese tourists',\n",
       "  'post_description': 'COVID-19 (2019-nCoV) Wuhan Coronavirus Information',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'CoronavirusCA',\n",
       "  'posting_user': 'Zorgi23',\n",
       "  'user_registered_at': '2010-05-20',\n",
       "  'post_full_text': 'COVID Update - July 20, 2020: COVID has a personal envoy',\n",
       "  'post_description': 'Tracking the Coronavirus/Covid-19 outbreak in California',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'CoronavirusCA',\n",
       "  'posting_user': 'a_real_live_alien',\n",
       "  'user_registered_at': '2011-03-13',\n",
       "  'post_full_text': \"Essential Politics: Trump and Newsom's quiet cooperation\",\n",
       "  'post_description': 'Tracking the Coronavirus/Covid-19 outbreak in California',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'CoronavirusCA',\n",
       "  'posting_user': 'joyousjoyness',\n",
       "  'user_registered_at': '2011-11-27',\n",
       "  'post_full_text': 'Teacher Shortage Compounds Covid Crisis in Schools - WSJ',\n",
       "  'post_description': 'Tracking the Coronavirus/Covid-19 outbreak in California',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'PublicFreakout',\n",
       "  'posting_user': 'Al-Andalusia',\n",
       "  'user_registered_at': '2011-02-04',\n",
       "  'post_full_text': \"Counter-protestors ('defending' statues) in London clashed with police on Bridge street next to Big Ben.\",\n",
       "  'post_description': 'A subreddit dedicated to people freaking out, melting down, losing their cool, or being weird in public.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'PublicFreakout',\n",
       "  'posting_user': 'Ragnarokcometh',\n",
       "  'user_registered_at': '2014-05-09',\n",
       "  'post_full_text': 'Body Cam Footage Shows Corrections Officer Punching 19-Year-Old Inmate In The Face While handcuffed NSFW',\n",
       "  'post_description': 'A subreddit dedicated to people freaking out, melting down, losing their cool, or being weird in public.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'PublicFreakout',\n",
       "  'posting_user': 'Ramy_',\n",
       "  'user_registered_at': '2014-11-05',\n",
       "  'post_full_text': 'BLM vs counter protester. Cops arrest the BLM supporter and a crowd gathers \"what he being detained for\"',\n",
       "  'post_description': 'A subreddit dedicated to people freaking out, melting down, losing their cool, or being weird in public.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'CoronavirusUK',\n",
       "  'posting_user': 'ElectronicFudge5',\n",
       "  'user_registered_at': '2010-11-30',\n",
       "  'post_full_text': \"Coronavirus: Christmas socialising poses 'substantial risks' - scientist\",\n",
       "  'post_description': 'Spreading news, advice and media following the UK’s spread of the virus.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'CoronavirusUK',\n",
       "  'posting_user': 'casualphilosopher1',\n",
       "  'user_registered_at': '2013-05-31',\n",
       "  'post_full_text': 'More than one million fall through cracks of coronavirus support schemes',\n",
       "  'post_description': 'Spreading news, advice and media following the UK’s spread of the virus.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'CoronavirusUK',\n",
       "  'posting_user': 'leeyuiwah',\n",
       "  'user_registered_at': '2010-07-03',\n",
       "  'post_full_text': 'Manchester airports say travellers must wear gloves and masks from today',\n",
       "  'post_description': 'Spreading news, advice and media following the UK’s spread of the virus.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'NoNewNormal',\n",
       "  'posting_user': 'thriftyqueen',\n",
       "  'user_registered_at': '2013-04-22',\n",
       "  'post_full_text': '‘Not a conspiracy theory’: Alberta Premier Kenney issues dire warning about impending ‘Great Reset’ (VIDEO)',\n",
       "  'post_description': 'Discussion about skepticism regarding the \"new normal\" that stems from the coronavirus (COVID-19) pandemic.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'politics',\n",
       "  'posting_user': 'Lost_Distribution546',\n",
       "  'user_registered_at': '2011-08-29',\n",
       "  'post_full_text': \"'Do as I say': Anger as some politicians ignore virus rules\",\n",
       "  'post_description': '/r/Politics is for news and discussion about U.S. politics.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'LivestreamFail',\n",
       "  'posting_user': 'ufgman',\n",
       "  'user_registered_at': '2011-04-08',\n",
       "  'post_full_text': \"berry0314 babysits the HAchubby and YuggieTV streams her way while they're gone\",\n",
       "  'post_description': 'Welcome to /r/LivestreamFail: the place for almost anything livestream related.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'PapiLapi',\n",
       "  'user_registered_at': '2010-08-27',\n",
       "  'post_full_text': 'Carti on Autopsy reminds me of Tony MontanaThe verse is short but is so hard',\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'Ssjdemi',\n",
       "  'user_registered_at': '2013-03-19',\n",
       "  'post_full_text': 'Iggy killed the vibe callin carti ab gilbert for no reason now carti gone 😒😐',\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'TSPYBM',\n",
       "  'user_registered_at': '2014-09-25',\n",
       "  'post_full_text': 'Money and Drugsinstrumental for money and drugs is literally anti depressant',\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'ViceViceVic',\n",
       "  'user_registered_at': '2010-03-23',\n",
       "  'post_full_text': 'Rhonie is a Yes \"Men\"(woman)I feel like she agrees on everything carti does.',\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'aliiiiiiiiiiiiiiiii1',\n",
       "  'user_registered_at': '2011-04-08',\n",
       "  'post_full_text': \"according to u/iffywolf dms might be fake cause pm isn't capalized who knows\",\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'king-vamp',\n",
       "  'user_registered_at': '2011-11-14',\n",
       "  'post_full_text': 'wtf happened to u/eternallatake ⁉️⁉️someone explain, i thought we loved him?',\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'pierrebourne69',\n",
       "  'user_registered_at': '2014-03-25',\n",
       "  'post_full_text': 'Last time uzi was really active on Twitter was for Monday and @ meh[removed]',\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'playboicarti',\n",
       "  'posting_user': 'waldek59',\n",
       "  'user_registered_at': '2014-12-06',\n",
       "  'post_full_text': 'rip yams best leaki got Prada on my ash\\n\\nI got shlimes they pull up fasht\\n\\n💯',\n",
       "  'post_description': 'A subreddit dedicated to the discussion of hip-hop/trap artist Playboi Carti',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'xqcow',\n",
       "  'posting_user': 'DroppingPing',\n",
       "  'user_registered_at': '2012-11-11',\n",
       "  'post_full_text': 'Day 21 of posting widepeepoHappy until XQC comments widepeepoHappy',\n",
       "  'post_description': 'A Reddit community for all things xQc. https://www.twitch.tv/xqcow',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'xqcow',\n",
       "  'posting_user': 'DroppingPing',\n",
       "  'user_registered_at': '2012-11-11',\n",
       "  'post_full_text': 'Day 20 of posting widepeepoHappy until XQC comments widepeepoHappy',\n",
       "  'post_description': 'A Reddit community for all things xQc. https://www.twitch.tv/xqcow',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'xqcow',\n",
       "  'posting_user': 'ThineSaiyan',\n",
       "  'user_registered_at': '2011-12-06',\n",
       "  'post_full_text': 'Back to Tarkov and indie horror games now that DBZ Kakarot is over',\n",
       "  'post_description': 'A Reddit community for all things xQc. https://www.twitch.tv/xqcow',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'xqcow',\n",
       "  'posting_user': 'emil-caspiani',\n",
       "  'user_registered_at': '2013-03-17',\n",
       "  'post_full_text': 'Hikaru Nakamura appreciation post for helping our Pepega in chess.',\n",
       "  'post_description': 'A Reddit community for all things xQc. https://www.twitch.tv/xqcow',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'BanGDream',\n",
       "  'posting_user': 'AruLyrics',\n",
       "  'user_registered_at': '2012-06-09',\n",
       "  'post_full_text': 'Roselia - Paradisus Paradoxum( KAN-ROM-EN Lyrics!)',\n",
       "  'post_description': 'A subreddit for the multimedia series, BanG Dream!',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'BanGDream',\n",
       "  'posting_user': 'pikachiu24',\n",
       "  'user_registered_at': '2014-07-13',\n",
       "  'post_full_text': \"Toyama Kasumi's hairstyle is... [Actually Shigure]\",\n",
       "  'post_description': 'A subreddit for the multimedia series, BanG Dream!',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'bleach',\n",
       "  'posting_user': 'Splashazard',\n",
       "  'user_registered_at': '2014-08-10',\n",
       "  'post_full_text': \"Idk what Cavelero's something is. Never heard of it\",\n",
       "  'post_description': 'A reddit for the Bleach manga & anime by Tite Kubo.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'SandersForPresident',\n",
       "  'posting_user': 'Crawl-Walk-Run',\n",
       "  'user_registered_at': '2011-10-29',\n",
       "  'post_full_text': 'John Oliver Endorses Medicare For All',\n",
       "  'post_description': 'Bernie Sanders for President, dammit!',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'SandersForPresident',\n",
       "  'posting_user': 'Harvickfan4Life',\n",
       "  'user_registered_at': '2014-04-06',\n",
       "  'post_full_text': 'Bernie Sanders on Kobe Bryant’s death',\n",
       "  'post_description': 'Bernie Sanders for President, dammit!',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'SandersForPresident',\n",
       "  'posting_user': 'VeganSamura1',\n",
       "  'user_registered_at': '2010-09-07',\n",
       "  'post_full_text': 'Everybody should be asking questions!',\n",
       "  'post_description': 'Bernie Sanders for President, dammit!',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'BrandNewSentence',\n",
       "  'posting_user': 'Ford456fgfd',\n",
       "  'user_registered_at': '2014-10-31',\n",
       "  'post_full_text': \"But instead of dying her hair she's invaded Luxembourg\",\n",
       "  'post_description': 'For sentences never before written, found in the wild.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'BrandNewSentence',\n",
       "  'posting_user': 'discovid19',\n",
       "  'user_registered_at': '2012-08-12',\n",
       "  'post_full_text': 'My brain cells kiss and i experience critical thinking',\n",
       "  'post_description': 'For sentences never before written, found in the wild.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'PaymoneyWubby',\n",
       "  'posting_user': 'Zenny_Glide',\n",
       "  'user_registered_at': '2012-11-12',\n",
       "  'post_full_text': 'That dude did not appreciate the raid',\n",
       "  'post_description': 'The Official PaymoneyWubby subreddit!',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'gundeals',\n",
       "  'posting_user': 'KimJongMakeEmSayUn',\n",
       "  'user_registered_at': '2013-03-19',\n",
       "  'post_full_text': '[Acc] CAA Micro Conversion Kit - Gen 2 (MCK2.0) - $195.49 shipped ($190.49 w/ code FACEBOOK)',\n",
       "  'post_description': '/r/GunDeals is a community dedicated to the collection and sharing of firearm related deals.',\n",
       "  'dif': 0},\n",
       " {'subreddit_name': 'gundeals',\n",
       "  'posting_user': 'whk1992',\n",
       "  'user_registered_at': '2013-09-04',\n",
       "  'post_full_text': '[Rifle] Savage A22 47200 - $207.97 S/H Incl. w/ Code GUNSNGEAR - $50 Savage Rebate = $157.97',\n",
       "  'post_description': '/r/GunDeals is a community dedicated to the collection and sharing of firearm related deals.',\n",
       "  'dif': 0}]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "what_are_the_chances()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**9 - Most active December 2020 days.** [1 mark]\n",
    "- Write a query that retrieves _only_ a ranked list of the most prolific days in December 2020, prolific measured in number of posts per day. Your query should return those days in a single-column table (column name `post_day`) in the format `YYYY-MM-DD`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def most_prolific_days():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT DATE_FORMAT(posted_at, '%Y-%m-%d') AS post_day \n",
    "        FROM posts \n",
    "        WHERE MONTH(posted_at)=12 AND YEAR(posted_at)=2020 \n",
    "        GROUP BY posted_at \n",
    "        ORDER BY COUNT(*) DESC;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'post_day': '2020-12-25'},\n",
       " {'post_day': '2020-12-09'},\n",
       " {'post_day': '2020-12-27'},\n",
       " {'post_day': '2020-12-22'},\n",
       " {'post_day': '2020-12-11'},\n",
       " {'post_day': '2020-12-01'},\n",
       " {'post_day': '2020-12-26'},\n",
       " {'post_day': '2020-12-23'},\n",
       " {'post_day': '2020-12-10'},\n",
       " {'post_day': '2020-12-24'},\n",
       " {'post_day': '2020-12-28'},\n",
       " {'post_day': '2020-12-14'},\n",
       " {'post_day': '2020-12-08'},\n",
       " {'post_day': '2020-12-19'},\n",
       " {'post_day': '2020-12-12'},\n",
       " {'post_day': '2020-12-04'},\n",
       " {'post_day': '2020-12-17'},\n",
       " {'post_day': '2020-12-29'},\n",
       " {'post_day': '2020-12-02'},\n",
       " {'post_day': '2020-12-21'},\n",
       " {'post_day': '2020-12-13'},\n",
       " {'post_day': '2020-12-31'},\n",
       " {'post_day': '2020-12-18'},\n",
       " {'post_day': '2020-12-20'},\n",
       " {'post_day': '2020-12-16'},\n",
       " {'post_day': '2020-12-05'},\n",
       " {'post_day': '2020-12-06'},\n",
       " {'post_day': '2020-12-15'},\n",
       " {'post_day': '2020-12-07'},\n",
       " {'post_day': '2020-12-03'},\n",
       " {'post_day': '2020-12-30'}]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "most_prolific_days()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**10 - Top 'covid'-mentioning users.** [1 mark]\n",
    "- Retrieve the top 5 users in terms of how often they have mentioned the term 'covid' in their posts. Your query should return two columns: `username` and `total_count`. You will consider an occurrence of the word 'covid' only when it appears before and after a whitespace (i.e., `<space>covid<space>`) and irrespective of case (both `<space>Covid<space>` and `<space>covid<space>` would be valid hits)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def count_covid():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT user_name AS username, COUNT(*) AS total_count \n",
    "        FROM users INNER JOIN posts ON users.user_ID=posts.author_ID \n",
    "        WHERE title LIKE '% covid %' OR selftext LIKE '% covid %' \n",
    "        GROUP BY username \n",
    "        ORDER BY total_count DESC \n",
    "        LIMIT 5;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'username': 'Kalepa', 'total_count': 7},\n",
       " {'username': 'Pessimist2020', 'total_count': 5},\n",
       " {'username': 'Skullzrulerz', 'total_count': 5},\n",
       " {'username': '_nutri_', 'total_count': 4},\n",
       " {'username': 'Hundsheimer_Berge', 'total_count': 3}]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_covid()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**11 - Top 10 users whose posts reached the most users only in their fav subreddits.** [2 marks]\n",
    "- Write a query to retrieve a list of **10** users sorted in descending order by the number of users their messages reached, considering only the subset of users belonging to their favourited subreddits. Your query must return only one column: `username`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def best_users_per_faved_subr_outreach():\n",
    "    # your code here\n",
    "\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT username FROM (\n",
    "        SELECT DISTINCT user_name AS username, subr_name, subr_numb_members AS members\n",
    "        FROM users INNER JOIN posts ON users.user_ID=posts.author_ID \n",
    "            INNER JOIN subreddits ON posts.subreddit_ID=subreddits.subr_ID\n",
    "            INNER JOIN favourites ON users.user_ID=favourites.user_ID AND subreddits.subr_ID=favourites.subr_ID\n",
    "        ORDER BY username\n",
    "        ) AS summands \n",
    "        GROUP BY username \n",
    "        ORDER BY SUM(members) DESC \n",
    "        LIMIT 10;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results\n",
    "\n",
    "# There are in fact not any users who posted in multiple favourited subreddits, but if there were there would be \n",
    "# a problem with overcounting. There is a problem with double (or even more) counting, since there is a high \n",
    "# probability of overlap in membership of these subreddits. But there is no way around this without the names of \n",
    "# the members of the subreddits or if you knew the number of overlaps for each subset of the subreddits and then \n",
    "# you can use the inclusion-exclusion principle to work out how many users there are in each union. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'username': 'darkoms666'},\n",
       " {'username': 'Humble_Award_4873'},\n",
       " {'username': 'Vajra-pani'},\n",
       " {'username': 'Jkid'},\n",
       " {'username': 'XDitto'},\n",
       " {'username': 'Majnum'},\n",
       " {'username': 'Azziboy6969'},\n",
       " {'username': 'DarkDismissal'},\n",
       " {'username': 'thonioand'},\n",
       " {'username': 'Exploded_Nut'}]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "best_users_per_faved_subr_outreach()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**12 - Users with mean high score for their posts.** [2 marks]\n",
    "- Retrieve the number of users (ignoring the username 'None') with an average score for their posts which is higher than the average score for the posts in our dataset. Your query should return only one result, under the column `result`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def users_score_above_mean():\n",
    "    # your code here\n",
    "    with connection.cursor() as cur:\n",
    "        q=\"\"\"SELECT COUNT(*) AS result FROM (\n",
    "        SELECT author_ID, SUM(score)/COUNT(*) AS average_score \n",
    "        FROM posts INNER JOIN users ON users.user_ID=posts.author_ID \n",
    "        WHERE user_name NOT LIKE 'None' \n",
    "        GROUP BY author_ID \n",
    "        HAVING average_score>(SELECT SUM(score)/COUNT(*) FROM posts) \n",
    "        ORDER BY average_score DESC \n",
    "        ) AS average_scores;\"\"\"\n",
    "        cur.execute(q)\n",
    "        results=cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'result': 71}]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_score_above_mean()"
   ]
  }
 ],
 "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.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}