## Facebook database

Use the SQLite database with Facebook posts data we created and used in week 9 to complete the questions below.

```{r}
library(DBI)
library(tidyverse)
library(microbenchmark)
```

## Subqueries

In SQL, you can run a nested query where you use a result from the first query as a condition for the second query. Here, we will find the top 3 most frequent posters in congress and then get all messages from them. 

```{r}
db <- dbConnect(RSQLite::SQLite(), "data/facebook-db.sqlite")
```

```{r}
# Congress table
dbListFields(db, "congress")

# Posts table
dbListFields(db, "posts")
```

1. What are the screen names of the top 3 posters?

```{r}
dbGetQuery(db, 
  "SELECT screen_name, COUNT(*) AS post_count
  FROM posts
  GROUP BY screen_name
  ORDER BY post_count DESC
  LIMIT 3
")

dbGetQuery(db, 
  "SELECT screen_name
  FROM posts
  GROUP BY screen_name
  ORDER BY COUNT(*) DESC
  LIMIT 3
")
```

2. (Without subquery) how can you get the posts from these congress members?

```{r}
dbGetQuery(db, 
  "SELECT *
  FROM posts
  WHERE screen_name IN ('CongressmanNadler', 'HurdOnTheHill', 'RepMikeThompson')")
```

3. Now, instead of hardcoding the screen_names in the conditional statement, use the query results in #1 as the subquery.

```{r}
dbGetQuery(db, 
  "SELECT *
  FROM posts
  WHERE screen_name IN (SELECT screen_name
      FROM posts
      GROUP BY screen_name
      ORDER BY COUNT(*) DESC
      LIMIT 3)
")

# Another common form of subquery is
# SELECT *
# FROM ( <subquery> ) as sometable
# ...
# 
```

4. Using a subquery, get the average number of likes per post for all women Democrats.

```{r}
dbGetQuery(db, 
  "SELECT AVG(likes_count)
  FROM posts
  WHERE screen_name IN (SELECT screen_name
      FROM congress
      WHERE party = 'Democrat' AND gender = 'F')
")
```

5. Now use a join to do the same as in #4.

```{r}
dbGetQuery(db, 
  "SELECT AVG(likes_count)
  FROM posts AS p
  JOIN congress AS c 
  ON p.screen_name = c.screen_name
  WHERE c.party = 'Democrat' AND c.gender = 'F'
")
```

6. Which solution is faster: the one in #4 or #5 (try to use `microbenchmark`)?

```{r}
library(microbenchmark)
microbenchmark(
  subquery = dbGetQuery(db, 
    "SELECT AVG(likes_count)
    FROM posts
    WHERE screen_name IN (SELECT screen_name
        FROM congress
        WHERE party = 'Democrat' AND gender = 'F')
  "),
  join = dbGetQuery(db, 
    "SELECT AVG(likes_count)
    FROM posts AS p
    JOIN congress AS c 
    ON p.screen_name = c.screen_name
    WHERE c.party = 'Democrat' AND c.gender = 'F'
  "), 
  times = 10
)
```

*In this particular case, the subquery is faster than the join. Notice, however, that this is not necessarily always the case. It is possible that a SQL server may optimize a join better than a subquery. Arguably, subqueries are easier to read and in addition to efficiency, legibility should also be considered when writing code.*
