---
title: "Проект SQL: Анализ оттока клиентов банка в сегменте 40+"
output: 
  flexdashboard::flex_dashboard:
    theme: flatly
    source_code: embed
    orientation: rows
---

```{r setup, include=FALSE}
library(flexdashboard)
library(dplyr)
library(ggplot2)
library(plotly)
library(crosstalk)
library(stringr)

library(DBI)
library(RMariaDB)
con <- dbConnect(RMariaDB::MariaDB(), 
                 user='studentminor', 
                 password='DataMinorHSE!2020', 
                 dbname='bank', 
                 host='34.88.193.134',
                 port = 3306)

churn <- dbGetQuery(con, "SELECT Exited, COUNT(*) AS num
                  FROM portfolio
                  GROUP BY Exited")

df <- dbGetQuery(con, "SELECT EstimatedSalary, CreditScore, Tenure, Balance, NumOfProducts, Exited, HasCrCard,                             IsActiveMember, Country
                       FROM portfolio
                       INNER JOIN profile USING(CustomerId)
                       INNER JOIN country USING(CountryId)
                       WHERE Age >= 40")

df$IsActiveMember <- str_replace_all(df$IsActiveMember, "1", "Да")
df$IsActiveMember <- str_replace_all(df$IsActiveMember, "0", "Нет")
df$Exited <- str_replace_all(df$Exited, "1", "Да")
df$Exited <- str_replace_all(df$Exited, "0", "Нет")

df$Exited <- as.factor(df$Exited)
df$Exited <- relevel(df$Exited, "Да")
df$HasCrCard <- as.factor(df$HasCrCard)
df$IsActiveMember <- as.factor(df$IsActiveMember)
df$Country <- as.factor(df$Country)

df.share <- SharedData$new(df)

dbDisconnect(con)
```

Inputs {.sidebar}
-------------------------------------

```{r}
filter_slider("Balance", "Баланс", df.share, ~Balance)
filter_select("Country", "Страна", df.share, ~Country)
filter_select("NumOfProducts", "Количество продуктов", df.share, ~NumOfProducts)
filter_checkbox("IsActiveMember", "Клиент является активным", df.share, ~IsActiveMember)
```

-------------------------------------

 - Больше ушедших среди людей с большим балансом
 
 - Наибольшая доля ушедших в Германии
 
 - Больше ушедших с большим количеством продуктов (3-4 шт.)
 
 - Большая доля ушедших среди неактивных клиентов

Row {data-height=200}
-------------------------------------

### Доля ушедших от общего числа клиентов

```{r}
value1 <- paste0(round(churn$num[1] / sum(churn$num) * 100, 1), "%")
valueBox(value1, color = "primary")
```

### Доля ушедших клиентов в сегменте 40+

```{r}
value2 <- df %>% group_by(Exited) %>% summarise(num = n())
value2 <- paste0(round(value2$num[1] / sum(value2$num) * 100, 1), "%")
valueBox(value2, color = "primary")
```


Row {data-height=1000}
-------------------------------------

### Количество ушедших клиентов в сегменте


```{r warning=F}
df.share %>% 
  plot_ly(x = ~Exited, color = ~Exited, 
  colors = c("#d64747", "#83B44B"),
  type = 'histogram',
  hoverinfo = 'y') %>% 
  layout(title = " ",
  xaxis = list(title = "Ушли"),
  yaxis = list(title = "Количество клиентов")) %>% 
  hide_legend()
```