Introdução

O projeto tem como objetivo a manipulação e análise de dados. Serão abordados 5 tarefas que envolvem a análise de dois banco de dados.

Primeiramente, vamos carregar os pacotes que serão necessários para o desenvolvimento do projeto.

# Carregar pacotes
library(tidyverse)
library(kableExtra)
library(dbplyr)
library(sqldf)
library(RH2)
library(plotly)

Em seguida, iremos ler os datasets.

# Ler datasets
consumo <- readxl::read_xlsx('consumo.xlsx')
conteudo <- readxl::read_xlsx('conteudo.xlsx')

Durante o teste será necessario a manipulaçao do dataset consumo de acordo com o mês e com os minutos consumidos. Portanto, vamos criar essas variáveis auxiliares.

Inicialmente, a primeira variável auxiliar mês será inserida no dataet consumo. Além disso, vamos adicionar uma variável auxiliar para a data.

# Criar dataset mes
mes <- sqldf("select month(data) as valor_mes  from consumo")

# Inserir variável mes
consumo$mes <- mes$valor_mes

# Inserir variável data_aux
consumo$data_aux <- as.character(consumo$data)

# Reordenar o dataset
consumo <- consumo[c(1:3,6,5,4)]

Agora, os datasets serão transformados em databases para que a manipulação dos dados possa ser desenvolvida em SQL.

Após a transformação, a segunda variável auxiliar minutos_consumidos será inserida no database consumo.

# Transformar datasets em databases
consumo <- memdb_frame(consumo, .name = 'consumo')
conteudo <- memdb_frame(conteudo, .name = 'conteudo')

# Inserir variável minuto
consumo <-  consumo %>% 
            mutate('minutos_consumidos' = horas_consumidas * 60)

# Imprimir query
consumo %>% show_query()
## <SQL>
## SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `horas_consumidas` * 60.0 AS `minutos_consumidos`
## FROM `consumo`

Para concluir a introdução, vamos verificar como ficaram os dois databases que serão utilizados neste teste. Para isso, as primeiras linhas dos dois databases serão impressas.

# Imprimir cabeçalhos dos databases 

# Consumo
consumo %>% 
  select(-c(data)) %>% 
  head() %>% 
  kable() %>% 
  kable_styling(bootstrap_options = c('striped','hover','condensed','responsive'), full_width = F)
id_user id_conteudo data_aux mes horas_consumidas minutos_consumidos
150 10406 2019-07-07 7 0.2725694 16.35416
139 10352 2019-11-24 11 0.5881346 35.28808
182 10206 2019-07-26 7 0.8201295 49.20777
199 10835 2019-11-10 11 0.2426171 14.55702
185 10406 2019-11-19 11 0.9750948 58.50569
144 10777 2019-11-09 11 0.5271769 31.63061 
# Conteudo
conteudo %>% 
  head() %>% 
  kable() %>% 
  kable_styling(bootstrap_options = c('striped','hover','condensed','responsive'), full_width = F)
id_conteudo conteudo categoria
10406 A novela
10352 B serie
10206 C novela
10835 D serie

Após essas manipulação inicial dos datasets, as 6 tarefas propostas pelo teste podem ser realizadas.

Tarefa 1

Quantidade de horas consumidas e plays por categoria.

total_horas_e_play <- consumo %>%
                      left_join(conteudo, by = 'id_conteudo') %>%
                      group_by(categoria) %>%
                      summarise('horas_consumidas' = sum(horas_consumidas, na.rm = TRUE), 
                                'play' = n()) %>% 
                      filter(!is.na(categoria)) %>% 
                      ungroup()

# Imprimir query
total_horas_e_play %>% show_query()
## <SQL>
## SELECT *
## FROM (SELECT `categoria`, SUM(`horas_consumidas`) AS `horas_consumidas`, COUNT(*) AS `play`
## FROM (SELECT `id_user`, `LHS`.`id_conteudo` AS `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `minutos_consumidos`, `conteudo`, `categoria`
## FROM (SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `horas_consumidas` * 60.0 AS `minutos_consumidos`
## FROM `consumo`) AS `LHS`
## LEFT JOIN `conteudo` AS `RHS`
## ON (`LHS`.`id_conteudo` = `RHS`.`id_conteudo`)
## )
## GROUP BY `categoria`)
## WHERE (NOT(((`categoria`) IS NULL)))

Vamos checar o resultado da Tarefa 1.

# Imprimir tabela
total_horas_e_play %>% 
  kable() %>% 
  kable_styling(bootstrap_options = c('striped','hover','condensed','responsive'), full_width = F)
categoria horas_consumidas play
novela 3.698361 9
serie 7.275524 13

Tarefa 2

Ranking de novelas com mais horas consumidas por mês.

# Gerar query
ranking_novela <- consumo %>%
                  left_join(conteudo, by = 'id_conteudo') %>%
                  group_by(mes, id_conteudo) %>%
                  summarise('horas_consumidas' = sum(horas_consumidas, na.rm = TRUE)) %>% 
                  arrange(desc(mes), desc(horas_consumidas)) %>% 
                  ungroup() 
# Imprimir query
ranking_novela %>% show_query()
## <SQL>
## SELECT `mes`, `id_conteudo`, SUM(`horas_consumidas`) AS `horas_consumidas`
## FROM (SELECT `id_user`, `LHS`.`id_conteudo` AS `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `minutos_consumidos`, `conteudo`, `categoria`
## FROM (SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `horas_consumidas` * 60.0 AS `minutos_consumidos`
## FROM `consumo`) AS `LHS`
## LEFT JOIN `conteudo` AS `RHS`
## ON (`LHS`.`id_conteudo` = `RHS`.`id_conteudo`)
## )
## GROUP BY `mes`, `id_conteudo`
## ORDER BY `mes` DESC, `horas_consumidas` DESC

Vamos checar o resultado da Tarefa 2.

# Imprimir tabela
ranking_novela %>% 
  kable() %>% 
  kable_styling(bootstrap_options = c('striped','hover','condensed','responsive'), full_width = F)
mes id_conteudo horas_consumidas
11 10352 3.3546381
11 10406 0.9750948
11 10835 0.6962910
11 10777 0.5271769
10 10406 0.5778600
10 10352 0.1633538
9 10352 0.5413416
9 10777 0.4206381
8 10777 0.8182963
8 10835 0.7963525
8 10352 0.6050598
7 10406 1.1302770
7 10835 1.1184877
7 10206 1.0151295

Tarefa 3

Conteúdo de primeiro play do usuário.

# Gerar query
primeiro_play <- consumo %>%
                 left_join(conteudo, by = 'id_conteudo') %>% 
                 group_by(id_user) %>% 
                 filter(data == min(data, na.rm = TRUE)) %>% 
                 ungroup()

# Imprimir query
primeiro_play %>% show_query()
## <SQL>
## SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `minutos_consumidos`, `conteudo`, `categoria`
## FROM (SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `minutos_consumidos`, `conteudo`, `categoria`, MIN(`data`) OVER (PARTITION BY `id_user`) AS `q01`
## FROM (SELECT `id_user`, `LHS`.`id_conteudo` AS `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `minutos_consumidos`, `conteudo`, `categoria`
## FROM (SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `horas_consumidas` * 60.0 AS `minutos_consumidos`
## FROM `consumo`) AS `LHS`
## LEFT JOIN `conteudo` AS `RHS`
## ON (`LHS`.`id_conteudo` = `RHS`.`id_conteudo`)
## ))
## WHERE (`data` = `q01`)

Vamos checar o resultado da Tarefa 3.

# Imprimir tabela
primeiro_play %>% 
  select(-c(id_conteudo, data, mes, horas_consumidas, minutos_consumidos)) %>% 
  kable() %>% 
  kable_styling(bootstrap_options = c('striped','hover','condensed','responsive'), full_width = F)
id_user data_aux conteudo categoria
136 2019-07-18 C novela
139 2019-11-24 B serie
144 2019-07-03 A novela
150 2019-07-07 A novela
182 2019-07-26 C novela
185 2019-07-21 D serie
199 2019-07-24 A novela

Tarefa 4

Minutos por play para cada usuário.

# Gerar query
play_minutos <- consumo %>% 
                group_by(id_user) %>% 
                summarise('minutos_consumidos' = sum(minutos_consumidos, na.rm = TRUE),
                          'play' = n()) %>% 
                mutate('minutos_por_play' = minutos_consumidos/play) %>% 
                ungroup()

# Imprimir query
play_minutos %>% show_query()
## <SQL>
## SELECT `id_user`, `minutos_consumidos`, `play`, `minutos_consumidos` / `play` AS `minutos_por_play`
## FROM (SELECT `id_user`, SUM(`minutos_consumidos`) AS `minutos_consumidos`, COUNT(*) AS `play`
## FROM (SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `horas_consumidas` * 60.0 AS `minutos_consumidos`
## FROM `consumo`)
## GROUP BY `id_user`)

Vamos checar o resultado da Tarefa 4.

# Imprimir tabela
play_minutos %>% 
  kable() %>% 
  kable_styling(bootstrap_options = c('striped','hover','condensed','responsive'), full_width = F)
id_user minutos_consumidos play minutos_por_play
136 52.33049 3 17.44350
139 35.28808 1 35.28808
144 178.71787 6 29.78631
150 60.23956 3 20.07985
182 142.07717 3 47.35906
185 132.41731 4 33.10433
199 163.32934 5 32.66587

Tarefa 5

Qual a categoria mais consumida para cada usuário.

total_categoria <- consumo %>% 
                   left_join(conteudo, by = 'id_conteudo') %>%
                   count(id_user, categoria) %>% 
                   filter(!is.na(categoria))

# Imprimir query
total_categoria %>% show_query()
## <SQL>
## SELECT *
## FROM (SELECT `id_user`, `categoria`, COUNT(*) AS `n`
## FROM (SELECT `id_user`, `LHS`.`id_conteudo` AS `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `minutos_consumidos`, `conteudo`, `categoria`
## FROM (SELECT `id_user`, `id_conteudo`, `data`, `data_aux`, `mes`, `horas_consumidas`, `horas_consumidas` * 60.0 AS `minutos_consumidos`
## FROM `consumo`) AS `LHS`
## LEFT JOIN `conteudo` AS `RHS`
## ON (`LHS`.`id_conteudo` = `RHS`.`id_conteudo`)
## )
## GROUP BY `id_user`, `categoria`)
## WHERE (NOT(((`categoria`) IS NULL)))

Vamos checar o resultado da Tarefa 5.

# Imprimir tabela
total_categoria %>% 
  kable() %>% 
  kable_styling(bootstrap_options = c('striped','hover','condensed','responsive'), full_width = F)
id_user categoria n
136 novela 1
136 serie 2
139 serie 1
144 novela 3
144 serie 2
150 novela 2
150 serie 1
182 novela 1
182 serie 2
185 novela 1
185 serie 3
199 novela 1
199 serie 2

Tarefa 6

Conte uma história com os dados! Não precisa ser nada complexo. O objetivo é entendermos como você lida com informações e as analisa.

plot <- ggplot(consumo %>% 
                 left_join(conteudo, by = 'id_conteudo') %>% 
                 filter(!is.na(categoria)),
               aes(x = as.Date(data_aux, format = '%Y-%m-%d'), 
                   y = minutos_consumidos, 
                   col = as.factor(categoria))) +
              geom_line()+
              geom_point(aes(text = paste('Minutos Consumidos:', round(minutos_consumidos,2),
                                          '<br>Categoria:', categoria,
                                          '<br>Data:', data_aux)),
                         size = 0.5) +
              xlab('Data') +
              ylab('Minutos Consumidos') +
              labs(col = 'Categoria') +
              theme_minimal()

ggplotly(plot, tooltip = 'text')

O gráfico criado mostra que no período entre os meses de Agosto e Outubro de 2019 houve uma grande diferença entre a os minutos consumidos das categorias serie e novela.