library(tidyverse)

Table 1: Customers

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”) )

Table 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) ) customers %>% inner_join(orders, by = “customer_id”) customers %>% left_join(orders, by = “customer_id”) customers %>% right_join(orders, by = “customer_id”) customers %>% full_join(orders, by = “customer_id”) customers %>% semi_join(orders, by = “customer_id”) customers %>% anti_join(orders, by = “customer_id”)

# Table 1: Customers
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")
)

# Table 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)
)
customers %>%
  left_join(orders, by = "customer_id") %>%
  group_by(customer_id, name, city) %>%
  summarise(
    total_orders = sum(!is.na(order_id)),
    total_spent = sum(amount, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  rename(customer_name = name) %>%
  select(customer_id, customer_name, city, total_orders, total_spent)
## # A tibble: 5 × 5
##   customer_id customer_name city        total_orders total_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

We used left_join() because management required a report showing spending for every customer in the customers table, including those who have not placed any orders.Handling absent customer IDs: Orders from customer IDs absent from the customers table (IDs 6 and 7) do not appear in this report because a left_join() only retains matching records from the right table and drops unmatched right-table rows.