---
# System prepended metadata

title: Arquitetura de dados

---

# Arquitetura de dados

**Projeto 1: Modelagem relacional - Queries no Carloca**

**grupo:**
- Felipe Neri RM341293
- Gabriel Roger do Nascimento RM340399
- Orlando Gomes Mariano RM341455


**Objetivo:**

Analisar **Carloca** para entender qual o tipo de marketing comercial a empresa pode fazer na internet.

---

**Quadro de funcionários:**
É interessante saber que a Carloca tem um quadro de funcionários com várias funções distintas que no fim ajudam o cliente final, mas primeiro de tudo vai precisar de um profissional que consiga trabalhar com internet, growth hacking e publicidade.
Hoje a Carloca tem esses cargos, com tais médias salariais:

```sql=
SELECT 
    CARGO, 
    AVG(VL_SALARIO) AS MEDIA_SALARIAL 
FROM LOC_FUNCIONARIO 
GROUP BY
    CARGO
ORDER BY MEDIA_SALARIAL DESC
```

| Cargo | Media Salarial |
| ----- | -------------- |
| Presidente | 23567,98  |
| Filho do Dono | 22541  |
| Diretora Administrativa| 23567,98|
| Cientista de Dados | 8500  |
| Analista de BI | 7500  |
| Diretora Tecnica | 6985,63  |
| Analista de ETL | 6225  |
| Analista de Visualizacao | 4500  |
| Tecnico de Atendimento | 4145,748  |
|Analista de Atendimento|	3145,74|
|Programador|	2545,592|
|Gerente Financeiro|	2145,74|
|Supervisor de Linha|	2145,74|
|Gerente Comercial|	2145,74|
|Gerente de TI|	2145,74|
|Analista de Sistemas|	2145,74|
|Gerente de Fabrica|	2145,74|

A mediana salarial é de **4322,87**, é injusto realizar a média arítmética por alguns cargos que constam como "Presidente", "Filho do Dono" e "Diretora Administrativa".

```sql=
SELECT 
    MEDIAN(VL_SALARIO) AS MEDIA_SALARIAL 
FROM LOC_FUNCIONARIO 
```

Para melhor compreender como é o nosso atendimento com o público, temos uma seleção da média de atendimento e descobrimos que existem atendimentos que foram feitos por funcionários que não tem como objetivo de carreira atender. Algumas médias de atendimento são baixas justamente pelo fato do cargo do profissional não ser focado em atendimento.

```sql=
CREATE VIEW ATENDIMENTO AS
SELECT
        P.CD_FUNC,
        (SELECT 
            AVG(F.NR_ESTRELAS)
        FROM
            LOC_FUNCIONARIO F
        WHERE
                F.CD_FUNC = P.CD_FUNC) AS MEDIA_ATENDIMENTO
    FROM
        LOC_PEDIDO_LOCACAO P
    GROUP BY
        P.CD_FUNC
        
SELECT 
    F.NM_FUNC, 
    F.CARGO,
    A.MEDIA_ATENDIMENTO
FROM 
    LOC_FUNCIONARIO F
INNER JOIN 
    ATENDIMENTO A
ON 
    A.CD_FUNC = F.CD_FUNC
```

Os funcionários que tem maior número de pedido e vendas foi analisado. Entendemos que isso é um fator interessante para manter um nível de competição interno, e os top 5 podem ser porta voz nos meios de comunicação na mídia e os últimos no quadro analisado podem ter apoio dos que mais possuem pedidos.

```sql=
SELECT 
      F.NM_FUNC AS FUNCIONARIO,
      COUNT(PL.NR_PEDIDO) AS PEDIDOS,
      SUM(VL_TOTAL) AS "VALOR TOTAL"
FROM LOC_PEDIDO_LOCACAO PL
INNER JOIN LOC_FUNCIONARIO F
ON PL.CD_FUNC = F.CD_FUNC
GROUP BY F.NM_FUNC
ORDER BY COUNT(PL.NR_PEDIDO) DESC

```


| Nome do funcionário | número de pedido | valor total de vendas 
| -------- | -------- | -------- |
|Adriana Doretto|	541|	840878|
|Carlo Noronha|	422|	50961|
|Geraldo Reis Lima|	421|	159414|
|Ricardo Genaro|	413|	36399|
|Marcos da Cunha|	411|	102482|
|Rogerio Reis Lima|	403|	106310|
|Daiane dos Santos|	399|	192862|
|Larissa Almeira|	397|	14315|
|Diogo Sanguino|	397|	141032|
|MariaLinda Sauro|	386|	5256|
|Eduardo Satoru Sakai|	386|	125970|
|Cristina Murakami|	386|	130438|
|Aline de Oliveira|	383|	199015|
|Simone Batista|	382|	108400|
|Helio Gomes|	381|	77038|
|Ana Raquel Fernandes|	381|	151065|
|Jozivaldo Abreu|	381|	56864|
|Gustavo Lopes|	373|	124744|
|Paulo Norberto|	370|	218417|
|Jose Carlos da Silva|	370|	136975|
|Dayse Soares|	364|	40987|
|Marinaldo de Souza|	363|	146312|
|Beatriz Oliveira|	363|	25363|
|Eleonora Sintra|	36|	77769|
|Salvino Lopes|	352|	72962|
|Danielle da Silva|	204|	33711|
|Maria Oliveira|	189|	45034|


---
**Veículos mais alugados:**

```sql=
SELECT  
    MODELO,
    COUNT(NR_PLACA) Total
FROM LOC_VEICULO
GROUP BY MODELO
ORDER BY Total DESC
```

---

Agora iremos seguir esse documento com análises de locações para gerar insights do que poderemos usar para campanhas.

TOP 5 de veículos mais alugados:


| Modelo   | Total | 
| -------- | ----| 
|BRAVA     | 4   |
|SIENA     | 4   |
|PALIO     | 3   |
|CORSA     | 3   |
|MASERATI  | 2   |

Baseado nesse dado, sugerimos utilizar os carros top 5 para comerciais e imagens/vídeos de propagandas.

Para uma campanha em regiões descobrimos que Carloca trabalha apenas com MG e SP.

```sql=
SELECT 
    EC.CD_ESTADO,
    COUNT(EC.CD_CLIENTE)
FROM 
    LOC_ENDERECO_CLIENTE EC
GROUP BY
    EC.CD_ESTADO
    
ORDER BY COUNT(EC.CD_CLIENTE) DESC

```

Sendo SP 873 e MG 132. Sendo assim, entendemos que precisamos reforçar as campanhas em MG e criar algum anúncio específico em SP para os próprios clientes recomendarem o serviço para seus amigos, por exemplo.

---

Baseado no modelo e cor do veículo, temos uma base do que mais são vendidos.


TOP 5 dos mais vendidos:

| Modelo | Cor | Venda |
| -------- | -------- | -------- |
|CORSA WIND 1.0|	AZUL|	29563|
PORSCHE 928|  	AZUL	|59126|
FOCUS SEDAN	|CINZA	|29563|
SIENA	|CINZA BARI	|29563|
CELTA 1.0	|CINZA STIL|	29563|

```sql=
SELECT 
    V.MODELO,
    V.COR,
    SUM(IL.VL_TOTAL) Venda
FROM LOC_ITEM_LOCACAO IL
INNER JOIN LOC_VEICULO V
ON IL.NR_PLACA = V.NR_PLACA
GROUP BY 
    V.MODELO,
    V.COR,
    IL.VL_TOTAL
ORDER BY IL.VL_TOTAL DESC
```

---

Observando o comportamento dos cliente podemos fazer campanhas para alugar ainda mais veículos.
Sabemos que tem clientes que ultrapassam dos 200 pedidos.

```sql=
SELECT 
 C.CD_CLIENTE Cliente,
 C.NR_ESTRELAS,
 COUNT(PL.NR_PEDIDO) Pedidos
FROM
LOC_PEDIDO_LOCACAO PL
INNER JOIN LOC_CLIENTE C
ON C.CD_CLIENTE = PL.CD_CLIENTE
--WHERE C.NR_ESTRELAS >= 4 AND C.CD_CLIENTE = 5
GROUP BY
     C.CD_CLIENTE,
     C.NR_ESTRELAS
ORDER BY COUNT(PL.NR_PEDIDO) DESC

```

Sabemos que nem todos os clientes pagam com cartão de crédito, logo fizemos a análise das possíveis formas de pagamento.

```sql=
SELECT PL.TP_PAGTO AS "TIPO PAGAMENTO", COUNT(PL.TP_PAGTO) AS QTD
FROM LOC_PEDIDO_LOCACAO PL
GROUP BY PL.TP_PAGTO
ORDER BY COUNT(PL.TP_PAGTO) DESC
```


| Tipo | QTD |
| -------- | -------- |
|DI	|2655|
|NP	|1683|
|CC	|1674|
|DC	|1665|
|CH|	1661|
|BB|	840|


Nas propagandas enfatizar os meios é interessante.

---

Portanto, esse documento será de extrema importância para decidir como iremos escolher os alvos para possíveis campanhas de marketing na nossa querida empresa Carloca.
