library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
customers <- tibble(
  customer_id = c(1, 2, 3, 4, 5),
  name = c("Alice", "Bob", "Charlie", "David", "Eve"),
  city = c("New York", "Los Angeles", "Chicago", "Houston", "Phoenix")
)

# Dataset 2: Orders
orders <- tibble(
  order_id = c(101, 102, 103, 104, 105, 106),
  customer_id = c(1, 2, 3, 2, 6, 7),
  product = c("Laptop", "Phone", "Tablet", "Desktop", "Camera", "Printer"),
  amount = c(1200, 800, 300, 1500, 600, 150)
)

1. Inner Join (3 points) Perform an inner join between the customers and orders datasets.

q1 <- inner_join(customers , orders)
## Joining with `by = join_by(customer_id)`
  1. How many rows are in the result?
    4 rows
  2. Why are some customers or orders not included in the result?
    The customers or orders not included in the result are those where the customer_id does not have a match in both datasets.
  3. Display the result
print(q1)
## # A tibble: 4 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300

2. Left Join (3 points) Perform a left join with customers as the left table and orders as the right table.

q2 <- left_join(customers, orders, by = "customer_id")
  1. How many rows are in the result?
    6 rows
  2. Explain why this number differs from the inner join result.
    In an inner join, only rows with matching customer_id values in both tables are included. In contrast, a left join includes all rows from the left table (customers), regardless of whether there is a match in the right table (orders).
  3. Display the result.

``` r
print(q2)
## # A tibble: 6 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           4 David   Houston           NA <NA>        NA
## 6           5 Eve     Phoenix           NA <NA>        NA

3. Right Join (3 points) Perform a right join with customers as the left table and orders as the right table.

q3 <- right_join(customers, orders, by = "customer_id")
  1. How many rows are in the result?
    6 rows
  2. Which customer_ids in the result have NA for customer name and city? Explain why.
    Customer IDs 6 and 7 have NA for the name and city columns because there are no matching customer_ids in the customers table for these values. These customer_ids exist in the orders table, but since a right join includes all rows from the right table (orders), unmatched rows still appear with NA in the corresponding columns from the left table (customers).
  3. Display the result
print(q3)
## # A tibble: 6 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           6 <NA>    <NA>             105 Camera     600
## 6           7 <NA>    <NA>             106 Printer    150

4. Full Join (3 points) Perform a full join between customers and orders.

How many rows are in the result?

q4 <- full_join(customers, orders, by = "customer_id")
  1. How many rows are in the result?
    8 rows
  2. Identify any rows where there’s information from only one table. Explain these results.
    Rows where there’s information from only the customers table will have NA for the order_id, product, and amount columns. These are the customers who have no corresponding entries in the orders table. Customer IDs 4 (David) and 5 (Eve) have no matching orders. Rows where there’s information from only the orders table will have NA for the name and city columns. These are the orders with no corresponding customers in the customers table. Customer IDs 6 and 7 are found only in the orders table and do not exist in the customers table.
  3. Display the result.
print(q4)
## # A tibble: 8 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           4 David   Houston           NA <NA>        NA
## 6           5 Eve     Phoenix           NA <NA>        NA
## 7           6 <NA>    <NA>             105 Camera     600
## 8           7 <NA>    <NA>             106 Printer    150

5. Semi Join (3 points) Perform a semi join with customers as the left table and orders as the right table.

q5 <- semi_join(customers, orders, by = "customer_id")
  1. How many rows are in the result?
    3 rows
  2. How does this result differ from the inner join result?
    The inner join returns all rows where there is a match, but it includes columns from both the customers and orders tables. The semi join, on the other hand, only returns rows from the customers table (i.e., it returns customers who have placed at least one order), but it does not include any columns from the orders table. It’s essentially a filtered version of the customers table, without the extra information from orders.
  3. Display the result
print(q5)
## # A tibble: 3 × 3
##   customer_id name    city       
##         <dbl> <chr>   <chr>      
## 1           1 Alice   New York   
## 2           2 Bob     Los Angeles
## 3           3 Charlie Chicago

6. Anti Join (3 points) Perform an anti join with customers as the left table and orders as the right table.

q6 <- anti_join(customers, orders, by = "customer_id")
  1. Which customers are in the result?
    The customers who appear in the result are those who have no corresponding orders. Based on the datasets, customer IDs 4 (David) and 5 (Eve) do not have any matching entries in the orders table, so they will be included in the result.
  2. Explain what this result tells you about these customers.
    This result tells you that customers David and Eve have not placed any orders. The anti join filters out the customers who have made purchases and returns only those who have not.
  3. Display the result
print(q6)
## # A tibble: 2 × 3
##   customer_id name  city   
##         <dbl> <chr> <chr>  
## 1           4 David Houston
## 2           5 Eve   Phoenix

7. Practical Application (4 points) Imagine you’re analyzing customer behavior.

  1. Which join would you use to find all customers, including those who haven’t placed any orders? Why?
    A full join returns all records from both tables. If there’s no match, it will fill the missing values with NA. This will help capture customers who have not placed any orders as well as any orders that do not have a matching customer (perhaps due to missing customer information).
  2. Which join would you use to find only the customers who have placed orders? Why?
    A right join ensures that all customers who are in the orders table are returned, along with any matching data from the customers table. This means it will only return customers who have placed orders, because the orders table only contains customer IDs that are associated with an order.
  3. Write the R code for both scenarios.
  4. Display the result
all_customers <- full_join(customers, orders, by = "customer_id")
print(all_customers)
## # A tibble: 8 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           4 David   Houston           NA <NA>        NA
## 6           5 Eve     Phoenix           NA <NA>        NA
## 7           6 <NA>    <NA>             105 Camera     600
## 8           7 <NA>    <NA>             106 Printer    150
customers_with_orders <- right_join(customers, orders, by = "customer_id")
print(customers_with_orders)
## # A tibble: 6 × 6
##   customer_id name    city        order_id product amount
##         <dbl> <chr>   <chr>          <dbl> <chr>    <dbl>
## 1           1 Alice   New York         101 Laptop    1200
## 2           2 Bob     Los Angeles      102 Phone      800
## 3           2 Bob     Los Angeles      104 Desktop   1500
## 4           3 Charlie Chicago          103 Tablet     300
## 5           6 <NA>    <NA>             105 Camera     600
## 6           7 <NA>    <NA>             106 Printer    150

8. Challenge Question (3 points) Create a summary that shows each customer’s name, city, total number of orders, and total amount spent. Include all customers, even those without orders. Hint: You’ll need to use a combination of joins and group_by/summarize operations.

library(tidyr)
customer_orders <- full_join(customers, orders, by = "customer_id")

summary_data <- customer_orders %>%
  group_by(customer_id, name, city) %>%
  summarize(
    total_orders = n_distinct(order_id, na.rm = TRUE), 
    total_amount_spent = sum(amount, na.rm = TRUE)     
  ) %>%
  replace_na(list(total_orders = 0, total_amount_spent = 0)) 
## `summarise()` has grouped output by 'customer_id', 'name'. You can override
## using the `.groups` argument.
print(summary_data)
## # A tibble: 7 × 5
## # Groups:   customer_id, name [7]
##   customer_id name    city        total_orders total_amount_spent
##         <dbl> <chr>   <chr>              <int>              <dbl>
## 1           1 Alice   New York               1               1200
## 2           2 Bob     Los Angeles            2               2300
## 3           3 Charlie Chicago                1                300
## 4           4 David   Houston                0                  0
## 5           5 Eve     Phoenix                0                  0
## 6           6 <NA>    <NA>                   1                600
## 7           7 <NA>    <NA>                   1                150