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

# 1. Querying a SQLite database

Let us practice writing SQL queries by answering the following questions. We will use the database created in the lecture (you might need to adjust the path to the database on your computer).

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

Recall the columns of the two tables:

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

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

1. Who wrote the post that was shared the most times during this period? What does it say?

```{r}
dbGetQuery(db, 
           "SELECT screen_name, message, shares_count, id
           FROM posts
           ORDER BY shares_count DESC
           LIMIT 1")
```

2. What about the post with the most comments?

```{r}
dbGetQuery(db, 
           "SELECT screen_name, message, comments_count, id
           FROM posts
           ORDER BY comments_count DESC
           LIMIT 1")
```

3. Which was the post that received the most likes in January of 2017?

```{r}
dbGetQuery(db, 
           "SELECT screen_name, message, likes_count, id
           FROM posts
           WHERE date LIKE '2017-01-__'
           ORDER BY likes_count DESC
           LIMIT 1")
```

4. What was the photo posted by a Member of Congress that received the most "angry" reactions?

```{r}
dbGetQuery(db, 
           "SELECT screen_name, message, angry_count, id, type
           FROM posts
           WHERE type = 'photo'
           ORDER BY angry_count DESC
           LIMIT 1")
```

5. What was the post by `senatorsanders` that received the most "angry" reactions? And the most "love" reactions?

```{r}
dbGetQuery(db, 
           "SELECT screen_name, message, angry_count, id, type
           FROM posts
           WHERE screen_name = 'senatorsanders'
           ORDER BY angry_count DESC
           LIMIT 1")
dbGetQuery(db, 
           "SELECT screen_name, message, love_count, id, type
           FROM posts
           WHERE screen_name = 'senatorsanders'
           ORDER BY love_count DESC
           LIMIT 1")
```

6. How many posts were published each month of the year?

```{r}
dbGetQuery(db, 
           "SELECT SUBSTR(date, 6, 2) as month, 
              COUNT(*) as post_count
           FROM posts
           GROUP BY month")
```


# 2. Practicing working with multiple SQL tables

Next, let us study some queries with **JOIN** commands to answer the questions below. We will use the same database.

1. What is the average number of likes for members of the House and the Senate? Which group is more popular on average?

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

dbGetQuery(db, 
           "SELECT congress.type, AVG(posts.likes_count)
           FROM posts JOIN congress
            ON posts.screen_name = congress.screen_name
           GROUP BY congress.type")
```

2. What is the average number of likes that posts receive based on the time of the day in which they were published (note: times are in the GMT time zone)?

```{r}
res <- dbGetQuery(db,
           "SELECT SUBSTR(datetime, 12, 2) AS hour_of_day, 
              AVG(likes_count) AS avg_likes_count
            FROM posts
            GROUP BY hour_of_day")

ggplot(res, aes(x = hour_of_day, y = avg_likes_count, group = 1)) +
  geom_line() +
  theme_minimal()
```

3. Do male and female politicians receive different amounts of comments on average?

```{r}
dbGetQuery(db,
           "SELECT congress.gender AS gender,
              AVG(posts.comments_count) AS avg_comment_count 
           FROM posts JOIN congress
            ON posts.screen_name = congress.screen_name
           GROUP BY gender")
```

4. For each type of reaction (haha, love, angry...) compute the average count by party. Are there any systematic differences?

```{r}
dbGetQuery(db, 
           "SELECT congress.party, AVG(posts.love_count)
           FROM posts JOIN congress
            ON posts.screen_name = congress.screen_name
           GROUP BY congress.party")
dbGetQuery(db, 
           "SELECT congress.party, AVG(posts.haha_count)
           FROM posts JOIN congress
            ON posts.screen_name = congress.screen_name
           GROUP BY congress.party")
dbGetQuery(db, 
           "SELECT congress.party, AVG(posts.wow_count)
           FROM posts JOIN congress
            ON posts.screen_name = congress.screen_name
           GROUP BY congress.party")
dbGetQuery(db, 
           "SELECT congress.party, AVG(posts.sad_count)
           FROM posts JOIN congress
            ON posts.screen_name = congress.screen_name
           GROUP BY congress.party")
dbGetQuery(db, 
           "SELECT congress.party, AVG(posts.angry_count)
           FROM posts JOIN congress
            ON posts.screen_name = congress.screen_name
           GROUP BY congress.party")
```

5. Compute the total number of "sad" reactions for each domain shared by Republicans. Display the top 10 domains that receive the most "sad" reactions. Then repeat the same for Democrats. What do you find?

```{r}
dbGetQuery(db, 
  "SELECT congress.party, domain, COUNT(*) AS domain_count,
      SUM(posts.sad_count) AS sad_total
  FROM posts JOIN Congress
    ON congress.screen_name = posts.screen_name
  WHERE congress.party = 'Republican'
  GROUP BY domain
  ORDER BY sad_total DESC
  LIMIT 10")

dbGetQuery(db, 
  "SELECT congress.party, domain, COUNT(*) AS domain_count,
      SUM(posts.sad_count) AS sad_total
  FROM posts JOIN Congress
    ON congress.screen_name = posts.screen_name
  WHERE congress.party = 'Democrat'
  GROUP BY domain
  ORDER BY sad_total DESC
  LIMIT 10")
```


# 3. Creating and querying a simple new SQLite database

Lastly, the task is to create a simple new SQLite database with one table and query it. The data will be a random sample of 10,000 tweets related to the 2014 EP elections in the UK which can be found in `UK-tweets.csv`, available on Moodle.

1. Create a SQLite database with a table called `tweets` containing the data from the file. Test the database by checking the first 5 entries in it.

```{r}
dbtweets <- dbConnect(RSQLite::SQLite(), "data/tweet-db.sqlite")
tweets <- read.csv("data/UK-tweets.csv")
dbWriteTable(dbtweets, "tweets", tweets)

dbGetQuery(dbtweets, 'SELECT * FROM tweets LIMIT 5')
dbListFields(dbtweets, "tweets")        
```

2. How many tweets are classified as engaging? (HINT: Use CASE WHEN)

```{r}
dbGetQuery(dbtweets, 
  "SELECT SUM(CASE WHEN communication='engaging' THEN 1 ELSE 0 END)  
    AS number_engaging FROM tweets")
```

3. What percent of tweets are classified as impolite?

```{r}
# We need to multiply by a float (100.0) because otherwise 
# SQL will return only the integer part
dbGetQuery(dbtweets, 
  "SELECT (100.0 * SUM(CASE WHEN polite='impolite' THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0)) 
    AS percent_impolite FROM tweets")
```

4. What percent of tweets mention Brexit?

```{r}
dbGetQuery(dbtweets, 
  "SELECT (100.0 * SUM(CASE WHEN text LIKE '%brexit%' THEN 1 ELSE 0 END) / NULLIF(COUNT(*),0)) 
    AS percent_brexit FROM tweets")
```

5. Compute the average number of followers for tweets that are impolite and polite.

```{r}
# Again, multiply by 1.0 before dividing if you'd like to get decimal points
dbGetQuery(dbtweets, 
  "SELECT polite, (1.0 * SUM(followers_count) / NULLIF(COUNT(*),0)) 
    AS avg_followers 
    FROM tweets
    GROUP BY polite")
```

