**Attribution statement:** _The following teaching materials have been iteratively developed by current and former instructors, including: Pablo Barberá, Daniel de Kadt and Ryan Hübert._

```{r}
library(__)
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. Before doing so, do a little directory management by indicating the path where you stored your data (and the database), naming it `ddir`. 

**Note**: If you have not created the database yet, you need to complete the exercises in `01-sql-intro.Rmd`!

```{r}
# Where is the facebook data stored on your computer?
ddir <- "________" 
db <- dbConnect(RSQLite::SQLite(), paste0(________, ________))
```

Recall the columns of the two tables:

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

# 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, 
            ...
           ")
```

2. What about the post with the most comments?

```{r}

```

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

```{r}

```


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

```{r}

```


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

```{r}

```


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

```{r}

```


# 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(), "_______________")

dbGetQuery(db, 
           "...")
```

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}
result <- dbGetQuery(db, 
           "...")

ggplot(result, aes(x = hour_of_day, y = avg_likes_count, group = 1)) +
  geom____() +
  theme_minimal()
```

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

```{r}

```

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

```{r}

```

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}

```


# 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 under "Extra Resources" on Moodle. Place this file inside `ddir`.

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}
db <- dbConnect(...
                
tweets <- read_csv(_________)
dbWriteTable(...  

dbListFields(...
dbGetQuery(__, '_____________________')                
```

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

```{r}
dbGetQuery(__, 
  "SELECT ...")
```

3. What percent of tweets are classified as impolite?

```{r}

```

4. What percent of tweets mention Brexit?

```{r}

```

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

```{r}

```

