Projeto SQL para portfólio: Loja Northwind

Os projetos de portfólio SQL são uma parte importante da formação de um analista de dados. Como você inicia um projeto e para onde vai com os insights que descobre? Usaremos o banco de dados da loja Northwind para responder a essa pergunta.

Realizar um projeto SQL sólido é um elemento essencial do processo de aprendizado de qualquer analista de dados. Um projeto de SQL é uma ótima ferramenta de aprendizado, pois faz com que você use o SQL em um conjunto de dados do mundo real. Isso é especialmente importante se você for um iniciante que não tem oportunidades de trabalhar com conjuntos de dados do mundo real. Ao trabalhar com vários conjuntos de dados do mundo real, você aprende os problemas que pode encontrar no mundo real. Como bônus, a realização de vários projetos de SQL para o seu portfólio é sempre uma coisa boa de se ver em um currículo.

A questão é: como preparar um portfólio de projetos SQL? Quando você encontra um conjunto de dados on-line gratuito que deseja analisar, o que fazer em seguida? Responderemos a essas perguntas usando o banco de dados do Northwind Store.

Você pode encontrar esse conjunto de dados no curso Bancos de dados SQL para prática. Ele é um dos seis conjuntos de dados que incluem dados de uma universidade, tráfego de blogs, resultados esportivos, uma loja de música e o Museu de Arte Moderna (MoMA). Esse curso faz parte da trilhaTrilha de Práticas em SQL , onde você pode praticar agregação, JOINs, subconsultas, CTEs, CASE WHEN e outros tópicos importantes de SQL. Se você precisar atualizar seus conhecimentos sobre algumas dessas áreas importantes para a geração de relatórios, experimente nosso Como Criar Relatórios Básicos em SQL curso.

Vamos agora pegar o conjunto de dados da loja Northwind e usá-lo em um projeto SQL para um portfólio de analistas de dados. Farei esse projeto no PostgreSQL, mas tudo o que fizer poderá ser transferido para outros bancos de dados com pequenos ajustes de sintaxe.

Durante o processo, fique à vontade para consultar nossa Folha de consulta gratuita sobre SQL para análise de dados.

O conjunto de dados da Northwind Store

A única informação que lhe darei sobre esse banco de dados é que ele consiste em seis tabelas:

1. categories - Uma lista de categorias de produtos.
2. channels - Uma lista de fontes por meio das quais a loja adquire clientes.
3. customers - Uma lista dos clientes da loja.
4. order_items - Uma lista dos produtos incluídos em cada pedido.
5. orders - Uma lista de pedidos feitos pelos clientes.
6. products - Uma lista dos produtos que a loja oferece.

Reuniremos o restante das informações ao realizar nosso projeto. Na verdade, essa inspeção do banco de dados deve ser o estágio inicial de todo projeto, antes de começar a fazer a análise.

1. Inspeção do banco de dados

Essa etapa da preparação de um projeto SQL envolve conhecer seus dados, como nomes de tabelas e colunas, chaves primárias e estrangeiras, as relações entre as tabelas e os tipos de dados em cada tabela.

Inspeção de nomes de tabelas

Uma forma primitiva de aprender sobre as tabelas no conjunto de dados é encontrá-las no navegador do RDBMS para o qual você importou o conjunto de dados, por exemplo, PostgreSQL, SQL Server ou MySQL.

No PostgreSQL, você pode escrever esta consulta para obter uma lista de todas as tabelas em um banco de dados:

SELECT table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog') AND table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

Ela procura dados na tabela information_schemaonde você pode encontrar metadados do banco de dados. Como eu quero o nome de cada tabela, uso a visualização tables depois de fazer referência ao esquema e colocar table_name no SELECT.

A primeira condição em WHERE filtra os esquemas do sistema e deixa apenas as tabelas definidas pelo usuário. A segunda condição garante que somente as tabelas básicas sejam listadas, sem exibições e outras tabelas.

Aqui está a lista de tabelas no banco de dados Northwind:

table_name
categories
channels
customers
order_items
orders
products

Inspeção das informações da coluna

Agora queremos entender melhor os detalhes de cada tabela. Conhecer suas colunas é um bom começo.

Podemos consultar novamente information_schema para obter informações importantes sobre as colunas:

SELECT table_name,
       column_name,
       data_type,
       is_nullable,
       column_default
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_name, ordinal_position;

Podemos encontrar nomes de tabelas, nomes de colunas e o tipo de dados de cada coluna listando table_name, column_name e data_type no SELECT.

A coluna is_nullable nos fornecerá informações sobre se a coluna aceita valores NULL.

table_name	column_name	data_type	is_nullable
categories	category_id	integer	NO
categories	category_name	character varying	NO
categories	description	text	YES
channels	id	integer	NO
channels	channel_name	character varying	NO
customers	customer_id	integer	NO
customers	email	character varying	NO
customers	full_name	character varying	NO
customers	address	character varying	YES
customers	city	character varying	YES
customers	region	character varying	YES
customers	postal_code	character varying	YES
customers	country	character varying	YES
customers	phone	character varying	YES
customers	registration_date	timestamp without time zone	NO
customers	channel_id	integer	NO
customers	first_order_id	integer	YES
customers	first_order_date	timestamp without time zone	YES
customers	last_order_id	integer	YES
customers	last_order_date	timestamp without time zone	YES
order_items	order_id	integer	NO
order_items	product_id	integer	NO
order_items	unit_price	numeric	NO
order_items	quantity	smallint	NO
order_items	discount	numeric	NO
orders	order_id	integer	NO
orders	customer_id	integer	NO
orders	order_date	timestamp without time zone	YES
orders	total_amount	numeric	NO
orders	ship_name	character varying	YES
orders	ship_address	character varying	YES
orders	ship_city	character varying	YES
orders	ship_region	character varying	YES
orders	ship_postalcode	character varying	YES
orders	ship_country	character varying	YES
orders	shipped_date	timestamp without time zone	YES
products	product_id	integer	NO
products	product_name	character varying	NO
products	category_id	integer	NO
products	unit_price	numeric	YES
products	discontinued	boolean	NO

Dessa forma, temos todas as informações em um só lugar, o que facilita a investigação.

Primeiro, podemos entender quais dados cada tabela mostra vendo os nomes das colunas. Todos os tipos de dados parecem lógicos. Por exemplo, não há IDs definidos como character varying. Portanto, não haverá necessidade de converter os dados em um formato adequado nesta etapa.

Com relação a NULLs, obtemos algumas informações valiosas sobre as tabelas:

• A tabela categories permite que a descrição da categoria seja NULL.
• A tabela channels não permite nenhum NULLs.
• A tabela customers permite NULLs em muitas colunas, inclusive no país. Se quisermos criar uma segmentação geográfica de clientes, esses NULLs poderão nos causar problemas.
• A saída acima mostra que nenhuma coluna da tabela order_items são anuláveis.
• Para a tabela products é interessante observar que o preço unitário pode ser NULL.
• Parece um pouco estranho que a tabela orders tenha tantas colunas anuláveis. Basicamente, isso possibilita ter um ID de pedido e nenhuma outra informação sobre o pedido. Temos que ter isso em mente ao analisar essa tabela.

Como encontrar chaves primárias e estrangeiras

A próxima etapa da inspeção de dados é entender como as tabelas funcionam juntas. Para fazer isso, primeiro precisamos encontrar suas chaves primárias (PKs) e chaves estrangeiras (FKs).

As PKs nos mostrarão quais colunas uma tabela usa para identificar os dados de forma exclusiva. Ao fazer isso, você entenderá melhor a estrutura da tabela.

As FKs nos mostrarão qual coluna está vinculada a uma chave primária de outra tabela. Essa é a base para entender as relações entre as tabelas.

Você pode listar PKs e FKs usando a consulta abaixo:

SELECT kcu.table_name,
       kcu.column_name,
       tc.constraint_type
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
ON kcu.constraint_name = tc.constraint_name
AND kcu.constraint_schema = tc.constraint_schema
WHERE tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY kcu.table_name ASC, tc.constraint_type DESC;

Desta vez, precisamos usar duas exibições da tabela information_schemaou seja table_constraints e key_column_usage.

A visualização table_constraints contém informações sobre as restrições de cada tabela. A visualização key_column_usage serve para obter informações sobre as colunas com as restrições de chave.

Essas duas exibições são unidas por duas colunas comuns: constraint_name (o nome da restrição) e constraint_schema (o nome do esquema que contém a restrição).

Uso a cláusula WHERE para gerar apenas as restrições de chave primária e chave estrangeira.

Como etapa final, ordeno a saída em ordem alfabética pelo nome da tabela e, em seguida, inverto a ordem alfabética pelo tipo de restrição.

O código gera essa tabela:

table_name	column_name	constraint_type
categories	category_id	PRIMARY KEY
channels	id	PRIMARY KEY
customers	customer_id	PRIMARY KEY
customers	last_order_id	FOREIGN KEY
customers	first_order_id	FOREIGN KEY
customers	channel_id	FOREIGN KEY
order_items	order_id	PRIMARY KEY
order_items	product_id	PRIMARY KEY
order_items	order_id	FOREIGN KEY
order_items	product_id	FOREIGN KEY
orders	order_id	PRIMARY KEY
orders	customer_id	FOREIGN KEY
products	product_id	PRIMARY KEY
products	category_id	FOREIGN KEY

As tabelas categories e channels são as únicas tabelas que têm PK, mas não têm FK.

Todas as outras tabelas têm uma PK e pelo menos uma FK. A única exceção é a tabela order_itemsque tem a restrição PK em duas colunas: order_id e product_id. Em outras palavras, a tabela tem uma chave primária composta, o que significa que o identificador exclusivo dos dados é a combinação exclusiva de duas colunas. Nesse caso, o objetivo é mostrar apenas um ID de produto por pedido individual.

Uma análise mais detalhada revela que as mesmas colunas também são uma chave estrangeira para a mesma tabela. Isso significa que as chaves primárias também são estrangeiras, pois vêm de outras tabelas.

Entendendo os relacionamentos entre as tabelas

A listagem de PKs e FKs já nos dá uma ideia dos relacionamentos entre as tabelas. No entanto, podemos entendê-los melhor mostrando as tabelas e colunas pai e filho das chaves estrangeiras.

Por que faríamos isso? Uma chave estrangeira é sempre a chave primária de outra tabela. A listagem de todas as tabelas e colunas pai e filho é uma atualização da consulta anterior porque veremos facilmente as colunas compartilhadas entre as tabelas.

Essas informações são úteis para entender as relações entre as tabelas, saber quais colunas você pode usar para unir as tabelas e se elas podem ser unidas diretamente ou por meio de outra tabela.

Aqui está o código:

SELECT ccu.table_name AS parent_table,
       ccu.column_name AS parent_column,
       kcu.table_name AS child_table,
       kcu.column_name AS child_column      
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY kcu.table_name;

Na consulta, usamos informações das colunas table_constraints, key_column_usage, e constraint_column_usage exibições.

A visualização constraint_column_usage mostra as colunas usadas por uma restrição. Nós a usaremos para mostrar a tabela e a coluna pai da FK, ou seja, a tabela e a coluna em que essa FK é de fato uma PK.

Os dados de key_column_usage mostrarão a tabela e a coluna de uma FK.

parent_table	parent_column	child_table	child_column
orders	order_id	customers	last_order_id
orders	order_id	customers	first_order_id
channels	id	customers	channel_id
orders	order_id	order_items	order_id
products	product_id	order_items	product_id
customers	customer_id	orders	customer_id
categories	category_id	products	category_id

Na saída acima, vemos que a tabela orders está diretamente conectada à tabela customers por meio das colunas order_id (PK) e customer_id (FK). Ela está conectada à tabela order_items por meio de order_id (PK).

A tabela channels está diretamente conectada apenas com a tabela customers.

Já estabelecemos que as chaves primárias da tabela order_items também são chaves estrangeiras. Na tabela acima, podemos ver que elas vêm das tabelas orders e products.

A tabela categories está conectada somente com a tabela products.

2. Exploração de dados

Quando você começa a trabalhar com um novo banco de dados, a primeira etapa deve ser a exploração dos dados para obter algumas informações básicas sobre cada tabela em um banco de dados. É uma boa prática observar o seguinte:

• Contagem de linhas.
• Encontrar valores mínimos e máximos (especialmente para datas).
• Listar categorias distintas.
• Contagem de valores distintos em cada categoria.
• Contagem de linhas por valor de categoria.
• Somas e médias de valores numéricos importantes.

Essa não é uma lista exaustiva e pode mudar de acordo com a tabela que você está explorando. Entretanto, é um mínimo e deve funcionar na maioria das tabelas.

Em geral, essa etapa se resume a uma agregação e um agrupamento de dados fundamentais. Mostrarei como isso funciona em uma tabela do nosso banco de dados e, em seguida, você poderá explorar outras tabelas da mesma forma.

Explorando a tabela de pedidos

Contagem de linhas

Como esperado, usamos a função de agregação COUNT() aqui:

SELECT COUNT(*) AS number_of_rows
FROM orders;

Eu uso a função COUNT() com um asterisco; isso contará todas as linhas, inclusive NULLs.

number_of_rows
11,618

A tabela orders tem 11.618 linhas. Em outras palavras, há 11.618 pedidos.

Valores mínimos e máximos

Vamos ver quais colunas são adequadas para uso com as funções MIN() e MAX(). Perfect (Perfeito) - podemos encontrar a data mais antiga e a mais recente em que um pedido foi feito e enviado:

SELECT MIN(order_date) AS oldest_order_date, 
	 MAX(order_date) AS latest_order_date,
	 MIN(shipped_date) AS oldest_shipped_date,
	 MAX(shipped_date) AS latest_shipped_date
FROM orders;

Na saída, podemos ver que o primeiro pedido foi feito em 2017-01-01 e o último em 2024-06-24. Quanto às datas de envio, a primeira está em 2017-01-01 e a mais recente em 2024-06-23.

oldest_order_date	latest_order_date	oldest_shipped_date	latest_shipped_date
2017-01-01 0:00:00	2024-06-24 0:00:00	2017-01-06 0:00:00	2024-06-23 0:00:00

Listagem de categorias

Não precisamos de nenhuma função de agregação para fazer isso:

SELECT DISTINCT ship_country
FROM orders
ORDER BY ship_country;

Na consulta, usamos a cláusula DISTINCT para mostrar cada país apenas uma vez.

Aqui está uma lista dos países nessa tabela:

ship_country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland
Italy
Mexico
Norway
Poland
Portugal
Spain
Sweden
Switzerland
UK
USA
Venezuela

Também será útil ver uma lista de cidades:

SELECT DISTINCT ship_city
FROM orders
ORDER BY ship_city;

Como há muitas cidades, aqui está uma lista parcial:

ship_city
Aachen
Abilene
Achille
Adelphi
Adrian
Akron
Albany
Alberton
Albuquerque
…
Yucca Valley

Contagem de valores distintos em cada categoria

Vamos ver agora o número de países para os quais a Northwind entregou remessas:

SELECT COUNT(DISTINCT ship_country) AS number_of_countries
FROM orders;

Usamos novamente COUNT(), mas desta vez adicionamos DISTINCT e fazemos referência à coluna que queremos contar.

O resultado mostra que há 21 países diferentes onde a empresa faz entregas:

number_of_countries
21

Poderíamos fazer o mesmo com as cidades:

SELECT COUNT(DISTINCT ship_city) AS number_of_cities
FROM orders;

Há 494 cidades exclusivas:

number_of_cities
494

Mesmo que não as tenhamos listado como uma categoria separada, saber quantos clientes fizeram pedidos será útil:

SELECT COUNT(DISTINCT customer_id) AS number_of_customers_with_orders
FROM orders;

Usamos o site COUNT() para contar os IDs dos clientes. É claro que precisamos de DISTINCT, pois é de se esperar que alguns clientes apareçam muitas vezes, ou seja, toda vez que fizerem um pedido.

Há 761 clientes exclusivos que fizeram pelo menos um pedido à empresa.

number_of_customers_with_orders
761

Contagem de linhas por valor de categoria

Para isso, queremos listar cada valor de categoria (fizemos isso anteriormente) e contar o número de linhas para cada valor.

Por exemplo, isso retornará o número de pedidos por país:

SELECT ship_country,
	 COUNT(*) AS number_of_orders
FROM orders
GROUP BY ship_country
ORDER BY number_of_orders DESC;

Seleciono a coluna ship_country e, em seguida, uso COUNT(*) para contar todas as linhas. Para que a contagem seja feita por cada país, preciso introduzir a cláusula GROUP BY ship_country.

O que obtemos com isso é o número de pedidos por país. O resultado mostra que os EUA são o maior mercado, com 9.789 pedidos:

ship_country	number_of_orders
USA	9,789
France	236
Brazil	233
Germany	230
UK	144
Spain	123
Mexico	118
Venezuela	103
Argentina	87
Canada	82
Italy	64
Austria	58
Portugal	58
Belgium	56
Denmark	56
Finland	47
Norway	30
Sweden	28
Poland	27
Ireland	26
Switzerland	23

Soma e valores médios

Se seus dados forem adequados, você deverá encontrar médias e totais de valores numéricos. Podemos fazer isso para a coluna total_amount, que é o valor de um pedido:

SELECT SUM(total_amount) AS orders_total_value,
       AVG(total_amount) AS average_order_value
FROM orders;

Para mostrar esses dois cálculos, use as funções de agregação SUM() e AVG().

Podemos ver que a receita total da loja é de pouco mais de 19 milhões. O valor médio do pedido é de 1.636,15:

orders_total_value	average_order_value
19,008,819.69	1,636.15

Exploração de dados de bônus

Como mencionei, as agregações anteriores devem ser o mínimo que você deve fazer para cada tabela no banco de dados.

Essa exploração de dados vai um pouco além (mas não muito longe) das agregações simples e GROUP BY. Embora essa ainda seja a base, você também pode empregar outros conceitos, como filtragem de dados (usando WHERE e/ou HAVING), extração de partes da data ou da hora, uso de CASE WHEN para rotular dados e assim por diante.

Vamos examinar vários exemplos.

Número de produtos

Podemos empregar a função COUNT() para encontrar o número total de produtos que a loja vende.

SELECT COUNT(*) AS number_of_active_products
FROM products
WHERE discontinued IS FALSE; 

Há uma condição na cláusula WHERE para mostrar somente os produtos que não foram descontinuados, ou seja, esses são os produtos que a loja está vendendo no momento.

Na seção anterior, sabemos que a coluna discontinued é do tipo booleano. Portanto, temos de usar o operador IS FALSE para incluir na contagem apenas os produtos não descontinuados.

A contagem é 69:

number_of_active_products
69

Receita por ano

Um relatório simples que mostra a receita por ano pode ser criado usando SUM():

SELECT EXTRACT(YEAR FROM order_date) AS revenue_year,
       SUM(total_amount) AS revenue  
FROM orders
GROUP BY revenue_year;

Usamos a função EXTRACT() para obter apenas os anos das datas dos pedidos. Em seguida, somamos os valores totais de todos os pedidos e agrupamos por ano para mostrar os valores de cada ano separadamente.

Podemos ver nos resultados que o ano de pico da empresa foi 2018. O ano com a menor receita é 2024, mas isso pode ser devido ao fato de o ano ainda não ter terminado (no momento da análise).

Outra coisa interessante é que não há dados de receita para os anos de 2019 a 2022. Isso deve ser verificado para saber se os dados estão faltando por algum motivo ou se isso é um erro.

revenue_year	revenue
2017	3,088,759.84
2018	9,368,330.91
2023	4,646,048.11
2024	1,905,680.83

Segmentação de clientes por país

Temos dados sobre os países dos clientes, portanto, uma visão geral do número de clientes em cada país seria informativa.

Além disso, não estamos interessados em clientes que não tenham feito um pedido, pois isso pode aumentar artificialmente o número de clientes. Não, queremos apenas clientes que fazem pedidos conosco. Aqui está a consulta:

SELECT country,
	 COUNT(*) AS number_of_customers
FROM customers
WHERE first_order_id IS NOT NULL
GROUP BY country
ORDER BY number_of_customers DESC;

Selecionamos e agrupamos por país e usamos a função de agregação COUNT() para encontrar o número de clientes. Se os dados da coluna first_order não forem nulos, então esse cliente fez pelo menos um pedido; essa é a condição que temos de usar em WHERE.

Os dados são ordenados do maior para o menor número de clientes.

O resultado mostra que o maior mercado da Northwind em termos de clientes são os EUA. Também é seguro concluir que esse é o maior mercado em termos de receita.

country	number_of_customers
USA	697
Germany	8
France	8
Brazil	8
UK	5
Venezuela	4
Spain	4
Mexico	4
Argentina	3
Canada	3
Belgium	2
Denmark	2
Portugal	2
Finland	2
Italy	2
Austria	2
Sweden	1
Poland	1
Ireland	1
Switzerland	1
Norway	1

3. Análise avançada de dados e percepções práticas

O que fizemos até agora é um bom começo. No entanto, a análise de dados em um projeto SQL para um portfólio não deve se limitar à simples agregação e exploração de cada tabela.

Agora, iremos além disso e escreveremos consultas mais complexas que nos permitirão agir e melhorar os negócios da Northwind. Por exemplo, talvez queiramos ver como as vendas mudam dependendo do canal. Vamos ver o que conseguimos e depois decidir quais serão nossas próximas etapas.

Vendas por canal

Para cada canal, queremos mostrar o total de vendas, o valor médio do pedido, o número total de pedidos e o número de clientes exclusivos. Também queremos classificar os canais por total de vendas. Aqui está a consulta:

SELECT ch.channel_name,
       SUM(o.total_amount) AS total_sales,
       AVG(o.total_amount) AS average_order_value,
       COUNT(o.order_id) AS total_orders,
       COUNT(DISTINCT o.customer_id) AS unique_customers,
       RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS sales_rank
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name
ORDER BY sales_rank;

Essa consulta usa várias agregações: SUM() para calcular o total de vendas, AVG() para o valor médio do pedido e COUNT() para o número total de pedidos e (com DISTINCT) para clientes exclusivos.

Em seguida, usamos a função de janela RANK() para classificar os canais pelo total de vendas. Como as funções de janela são executadas antes da agregação, não podemos simplesmente usar a coluna total_sales na função de janela. Em vez disso, preciso replicar todo o cálculo - uma tarefa simples que pode ser concluída copiando e colando.

Usamos os dados das tabelas channels e orders. No entanto, não posso unir diretamente essas duas tabelas, pois elas não têm uma coluna compartilhada. Em vez disso, temos que uni-las por meio da tabela customers.

Aqui está o resultado:

channel_name	total_sales	average_order_value	total_orders	unique_customers	sales_rank
Organic Search	14,003,046.95	1,603.10	8,735	565	1
Direct	2,436,649.06	1,799.59	1,354	91	2
Referral	1,034,734.45	1,645.05	629	46	3
Social	837,378.77	1,824.35	459	32	4
Paid Search	483,824.24	1,645.66	294	19	5
Email	213,186.22	1,450.25	147	8	6

Vemos que a maior parte das vendas da Northwind vem da pesquisa orgânica. Não há contradições nos dados - a classificação por vendas totais também reflete o número de pedidos e de clientes únicos.

É interessante observar que o canal de pesquisa orgânica não tem o valor médio de pedido mais alto. Isso exige algumas melhorias!

Vamos imaginar uma estratégia de aumento de receita que se concentre em aumentar o valor médio dos pedidos de todos os canais em vez de novas aquisições.

Provavelmente não conseguiremos adotar uma abordagem uniforme baseada apenas no canal de marketing. Em cada segmento, pode haver clientes com hábitos de gastos opostos. Podemos supor que isso se aplica especialmente à Pesquisa orgânica, que é um canal tão grande.

Portanto, precisamos saber mais sobre a distribuição do valor do pedido para cada canal.

Distribuição do valor do pedido

Vamos calcular o valor médio do pedido, a mediana, o quartil superior, o quartil inferior e o desvio padrão de cada canal:

SELECT ch.channel_name,
	 AVG(total_amount) AS average_order_value,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_order_value,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS upper_quartile_order_value,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS lower_quartile_order_value,
       STDDEV(total_amount) AS order_value_stddev
FROM channels ch
LEFT JOIN customers c
ON ch.id = c.channel_id
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY ch.channel_name;

O valor médio do pedido é calculado por AVG().

A mediana (ou percentil 50), o quartil superior e o quartil inferior são calculados usando a função de agregação de conjunto ordenado PERCENTILE_CONT(), com o percentil especificado entre parênteses.

Para o cálculo do percentil, os dados precisam ser classificados em ordem crescente; fazemos isso usando a cláusula WITHIN GROUP.

Após o cálculo do percentil, STDDEV() é usado para calcular o desvio padrão.

Aqui está o resultado:

channel_name	average_order_value	median_order_value	upper_quartile_order_value	lower_quartile_order_value	order_value_stddev
Direct	1,799.59	1,005.90	2,166.80	517.40	2,323.63
Email	1,450.25	960.00	1,970.41	492.50	1,655.97
Organic Search	1,603.10	1,007.70	2,018.20	480.00	1,916.39
Paid Search	1,645.66	1,083.00	2,104.40	486.55	1,813.22
Referral	1,645.05	1,064.00	2,034.50	482.90	1,969.01
Social	1,824.35	1,122.80	2,123.20	559.90	2,319.10

Vamos analisar as métricas do primeiro canal.

O desvio padrão do canal Direct é 2.323,63, que é maior do que a média ou o valor médio. Isso indica alta variabilidade, ou seja, provavelmente há outliers ou uma ampla gama de valores de pedidos.

Os quartis inferior e superior mostram que 50% dos pedidos estão entre 517,40 e 2.166,80. Entretanto, um alto desvio padrão significa que muitas ordens estão fora desse intervalo.

A mediana é significativamente menor que a média, o que indica que a distribuição é inclinada para a direita, ou seja, um número baixo de valores de pedidos altos que aumentam a média.

Podemos analisar cada canal da mesma forma.

Isso nos dá outra ideia. Para criar estratégias personalizadas para aumentar a receita, podemos segmentar ainda mais os clientes por quartil superior e inferior. Consideraremos todos os clientes acima do quartil superior como gastadores altos; os gastadores baixos serão os clientes abaixo do quartil inferior.

Dessa forma, podemos adaptar nossa estratégia a cada canal e aos hábitos de consumo de cada grupo.

Clientes de alto e baixo valor por canal

Farei esse cálculo para apenas um canal de marketing. Você pode fazer isso para todos os outros, pois o código permanecerá o mesmo; você só mudará o canal em WHERE.

Os clientes no quartil superior são clientes de alto valor e queremos colocá-los em uma lista separada:

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
           ch.channel_name
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN channels ch ON c.channel_id = ch.id
    WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

upper_quartile_value AS (
    SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_order_value) AS upper_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot
JOIN upper_quartile_value uqv 
ON cot.total_order_value >= uqv.upper_quartile
ORDER BY cot.total_order_value DESC;

O primeiro CTE calcula o valor total do pedido de cada cliente usando SUM() e unindo as tabelas customers e pedidos. Em WHERE, filtramos todos os canais, exceto o Direct.

O segundo CTE calcula o quartil superior de maneira familiar.

O terceiro SELECT então CROSS JOINs os dois CTEs para encontrar todos os clientes cujo valor do pedido está acima do quartil superior.

Aqui estão as primeiras dez linhas do resultado:

customer_id	full_name	total_order_value	channel_name
134	Barry Michael	79,371.50	Direct
152	Carolann Williams	64,365.21	Direct
7	Frédérique Citeaux	61,865.74	Direct
17	Sven Ottlieb	57,251.14	Direct
64	Sergio Gutiérrez	55,140.75	Direct
490	Alice Blevins	54,736.24	Direct
8	Martín Sommer	54,499.55	Direct
303	Gregory Mack	52,554.20	Direct
316	Jeff Heard	51,976.31	Direct
129	Stephan Bufford	50,868.70	Direct

No total, há 23 clientes no resultado. Podemos usar essas informações para atingir esses clientes de alto valor. Por exemplo, podemos criar programas de fidelidade em que esses clientes possam obter descontos especiais, serviço personalizado, pontos resgatáveis para compras, programas de associação VIP e assim por diante.

Da mesma forma, podemos listar os clientes cujos pedidos estão abaixo do quartil inferior:

WITH customer_order_totals AS (
    SELECT c.customer_id,
           c.full_name,
           SUM(o.total_amount) AS total_order_value,
	       ch.channel_name
    FROM customers c
    JOIN orders o 
    ON c.customer_id = o.customer_id
    JOIN channels ch
    ON c.channel_id = ch.id
	WHERE ch.channel_name = 'Direct'
    GROUP BY c.customer_id, c.full_name, ch.channel_name
),

lower_quartile_value AS (
    SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_order_value) AS lower_quartile
    FROM customer_order_totals
)

SELECT cot.customer_id,
       cot.full_name,
       cot.total_order_value,
       cot.channel_name
FROM customer_order_totals cot, lower_quartile_value lqv
WHERE cot.total_order_value <= lqv.lower_quartile
ORDER BY cot.total_order_value ASC;

Essa consulta é quase igual à anterior, só que desta vez calculamos o quartil inferior. Além disso, pesquisamos os clientes com pedidos abaixo desse valor.

Mais uma vez, a consulta retorna 191 clientes. Aqui estão as dez primeiras linhas:

customer_id	full_name	total_order_value	channel_name
939	Shannon Aguilar	98.40	Direct
997	Barbra Armstrong	251.50	Direct
687	Andrew Scott	452.90	Direct
787	Dennis Myer	912.00	Direct
917	Les Allen	991.40	Direct
921	Shelby Turgeon	1,162.25	Direct
560	Nancy Wiggins	1,425.80	Direct
678	Tracey Thomas	2,555.20	Direct
756	Dora Rowlands	2,713.50	Direct
715	George Scott	2,906.50	Direct

Esses clientes precisam ser abordados de forma diferente, pois precisam ser motivados a gastar mais. As estratégias para aumentar a receita com eles podem incluir pacotes de produtos personalizados, frete grátis acima de um determinado valor, aumento dos descontos para limites de pedidos mais altos ou oferta de um brinde acima de um determinado valor de pedido.

Agora, faça a mesma análise para todos os outros canais e pense em quais táticas você poderia usar para aumentar os valores dos pedidos desse canal.

Pronto para seu próprio projeto de portfólio SQL?

Neste artigo, mostramos como preparar um projeto de portfólio SQL usando o banco de dados da loja Northwind. É claro que, se você for criativo o suficiente, também poderá criar seu próprio conjunto de dados.

A conclusão de um projeto SQL para seu portfólio é uma parte importante da preparação para o processo de contratação. Os portfólios são uma excelente demonstração de suas habilidades práticas de análise de dados em SQL. O projeto acima é apenas um exemplo. Há muitas outras ideias de análise que você pode experimentar em nosso curso Bancos de dados SQL para a prática, que é altamente recomendável!