# 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.
Sign in with Wallet
Connect another wallet