---
title: "HW08-P2 Databases"
format:
html:
embed-resources: true
---
## Instructions
**Requirements**
Ex 1
- Correct SQL to get total number of badges
Ex 2
- Correct SQL to get total number of badges per name
- Correct SQL to ordered correctly
Ex 3
- Correct SQL to get display name and date for a chosen type of badge
Ex 4
- Correct SQL to get display name or id of users who have received the most badges
Ex 5
- Correct SQL to get display name, user id, reputation, and creationdate, ordered by id
Ex 6
- Correct SQL to get display name, user id and total number of badges ordered by id
Ex 6
- Correct SQL to get user id, total number of posts, total upvotes, ordered by id
Ex 7
- Correct SQL to get user id, total number of comments, total upvotes on comments, ordered by id
Ex 8
- An appropriate visual of comments and posts.
## Goal
Analyze Stack Overflow [badges](https://stackoverflow.com/help/badges) and [posts](https://meta.stackexchange.com/questions/2677/database-schema-documentation-for-the-public-data-dump-and-sede) data using SQL queries.
Start at . For each exercise, **record the query you used** in the qmd file; you run the query on to ensure you get the desired result.
## Ex 1
Count the number of total badges that have been given out. Hint: count the number of rows in the relevant table.
> ANSWER:
```{sql eval=FALSE}
```
## Ex 2
Find how many times each badge has been awarded, sorted from most awarded to least awarded.
> ANSWER:
```{sql eval=FALSE}
```
## Ex 3
Find a badge that looks interesting to you. Find all the user **DisplayNames** that have received the badge, along with the date at which they received it.
> ANSWER:
```{sql eval=FALSE}
```
## Ex 4
Show the users who have received the most badges, along with how many they have received.
> ANSWER:
```{sql eval=FALSE}
```
The next few activities analyze user activity. These activities mimic the common workflow of creating datasets in SQL that you analyze in `R`.
## Ex 5
Export a CSV file containing information about each user: `DisplayName, Id, Reputation, and CreationDate`. Because any queries that return more than 50,000 results return a random subset of 50,000 results, include a sort on the user ID variable to ensure that the first 50,000 users are retrieved. Name your file `users.csv`.
> ANSWER:
```{sql eval=FALSE}
```
## Ex 6
Make a table that has each user's total number of badges, and export this file as a CSV named `badges.csv`. Continue to include a sort on the user ID variable so that this data has information on the same first 50,000 users. *Hint: Start with the badges table, join information from users, and perform some grouped summaries.*
> ANSWER:
```{sql eval=FALSE}
```
## Ex 7
Make a table that has each user's total number of posts and total number of upvotes the user has cast, and export this file as a CSV named `posts.csv`. Continue to include a sort on the user ID variable so that this data has information on the same first 50,000 users. *Hint: Start with the posts table, join information from users, and perform some grouped summaries.*
> ANSWER:
```{sql eval=FALSE}
```
## Ex 8
Calculate the number of comments made per user, and the total number of upvotes across all comments made per user (this is the sum of the `Score` variable under the Comments table) and export this as a CSV file named `comments.csv`. Continue to include a sort on the user ID variable so that this data has information on the same first 50,000 users.
> ANSWER:
```{sql eval=FALSE}
```
## Ex 9
Import these four datasets into `R`. Visualize the relationship between the four datasets. Include at least two visualizations.
Your goal should be to weave together a compelling story about how users on Stackoverflow earn badges through their activity on the site.
> ANSWER:
```{r}
```
## Resources Reflection (required)
List the resources you used to help with this assignment then write 3-5 sentence reflection on which resources were most helpful in finishing this task.
**Resources:**
-
-
-
**Reflection:**