Projeto SQL para iniciantes: Painel de vendas do AdventureWorks

Criar um projeto SQL por conta própria é uma ótima maneira de aprimorar suas habilidades e ganhar experiência prática. Este exemplo de projeto SQL para iniciantes mostrará a você como criar um painel de vendas com SQL e o banco de dados de amostra AdventureWorks.

Se você é um iniciante em SQL e deseja aprimorar suas habilidades para além dos exercícios simples de consulta, é uma boa ideia realizar um projeto de análise de dados SQL. Ele não apenas o desafiará a aplicar o que aprendeu, mas também lhe dará uma compreensão mais profunda de como o SQL funciona em cenários do mundo real. Essa é a sua chance de passar da prática básica para a criação de algo tangível que mostre seu crescente conhecimento!

Neste artigo, vamos orientá-lo nas etapas de criação de um projeto SQL usando o banco de dados AdventureWorks. Cobriremos tudo, desde a ideia do projeto até a redação das consultas finais. Você aprenderá como abordar cada etapa do processo, e os conceitos que usaremos podem ser transferidos para qualquer projeto SQL.

Quando terminar, dê uma olhada no nosso curso SQL Databases for Practice (Bancos de dados SQL para prática), que tem 6 bancos de dados diferentes para você praticar a criação de relatórios. Como alternativa, você pode ler sobre como encontrar conjuntos de dados gratuitos para o seu próprio projeto de SQL e seguir com o seu próprio banco de dados!

Agora, vamos dar uma olhada no banco de dados que usaremos neste projeto de exemplo de SQL.

Explorando o banco de dados AdventureWorks

O banco de dados AdventureWorks é um banco de dados de amostra criado pela Microsoft para o SQL Server. Ele foi portado para muitos bancos de dados, inclusive para o PostgreSQL, que usaremos neste artigo. O banco de dados inclui 68 tabelas que descrevem um fabricante fictício de bicicletas e contém dados sobre diferentes tipos de transações que ocorrem durante as operações comerciais. Como o AdventureWorks é muito grande, é um conjunto de dados perfeito para iniciantes que desejam praticar SQL em um ambiente real.

O banco de dados é dividido em 5 esquemas, cada um representando um campo de operações diferente: Production, Purchasing, Sales, HR, e Person. Neste projeto, a maioria dos nossos dados virá do Sales schema. Também usaremos outros esquemas para obter algumas informações adicionais.

Vamos dar uma olhada nas tabelas que mais usaremos:

• SalesOrderHeader: Essa é a maior tabela do banco de dados. Ela armazena todas as informações relacionadas a um pedido como um todo. Será o ponto de partida mais comum para consultas relacionadas a vendas.
• Product: Armazena informações abrangentes sobre os produtos oferecidos pela empresa.
• SalesOrderDetail: Essa tabela conecta as tabelas SalesOrderHeader e Product armazenando informações sobre os produtos individuais que compõem cada pedido.
• ProductReview: Armazena as avaliações dos clientes sobre produtos específicos.
• Store: Essa tabela armazena informações básicas sobre cada loja. A maioria dos dados é armazenada na coluna Demographics em formato XML; não a usaremos neste projeto.
• SalesTerritory e CountryRegion: Usaremos essas duas tabelas juntas para obter o nome do país ao qual o pedido está associado.

Há muitas outras tabelas no banco de dados, mas não se preocupe; nós as apresentaremos e suas colunas importantes conforme necessário.

Definição do escopo do seu projeto SQL

Neste projeto, queremos criar um painel de vendas para a AdventureWorks. Mas como começar um projeto como esse? Um ótimo ponto de partida é determinar o escopo do projeto. Em geral, isso significa criar uma lista de perguntas que você deseja responder com os dados. Nossa lista inicial de perguntas é:

• Qual é o total de vendas mensais?
• Quais são as vendas mensais por país?
• Quais produtos são os mais vendidos?
• Quais lojas têm o melhor desempenho?
• Qual é o tamanho médio de cada pedido?
• Qual é o valor médio do tempo de vida do cliente em cada país?

À medida que nos aprofundamos nos dados e escrevemos nossas consultas, podemos refinar essas perguntas e ajustar nossas consultas de acordo com elas.

É assim que você deve começar todos os seus projetos de SQL: escreva uma lista das perguntas que você tem sobre os dados e, em seguida, escreva as consultas que lhe darão as respostas. À medida que você trabalha com as consultas e os dados, surgirão novas perguntas. Tente respondê-las também com suas consultas.

Agora estamos prontos para começar a escrever as consultas para nosso painel de vendas! Mantenha nossa Folha de consultaSQL para Iniciantes à mão caso precise de uma rápida atualização da sintaxe.

O que incluiremos em nosso painel de projeto SQL?

Relatório 1: Vendas mensais

Pergunta: Qual é a receita da empresa em cada mês?

O primeiro relatório em nosso painel mostra a receita de todos os meses anteriores (em todos os anos). Essa consulta pode ser usada para traçar linhas de tendência de receita ou pode ser analisada como está. Queremos visualizar os dados em ordem cronológica, começando com as datas mais recentes.

Abordagem

Primeiro, escolhemos a tabela na qual nossa consulta se baseará. Gostaríamos de mostrar três colunas: o ano e o mês da venda e a receita total para aquele mês e ano. Precisamos do valor total do pedido e da data da venda, portanto, a tabela SalesOrderHeader da tabela Sales é uma escolha óbvia.

Para exibir o ano e o mês, usaremos a função EXTRACT:

EXTRACT(<MONTH / YEAR> FROM OrderDate)

Usaremos essas colunas ao agrupar e ordenar o resultado.

Para obter a receita total desse mês específico, podemos usar SUM(TotalDue) juntamente com o agrupamento pelas duas colunas anteriores. Isso nos levará a ter resultados separados em SUM() para cada par ano-mês exclusivo.

Aqui está a consulta final:

SELECT
  EXTRACT(YEAR FROM OrderDate) AS OrderYear,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  ROUND(SUM(TotalDue), 2) AS TotalRevenue
FROM Sales.SalesOrderHeader
GROUP BY 
  EXTRACT(YEAR FROM OrderDate),
  EXTRACT(MONTH FROM OrderDate)
ORDER BY 
  OrderYear DESC,
  OrderMonth DESC;

Relatório 2: Receita mensal por país

Pergunta: Qual é a receita mensal de cada país?

Gostaríamos de ver a receita total de cada mês (de cada ano) para cada país no banco de dados. O resultado deve ser classificado em ordem cronológica, com as datas mais recentes aparecendo primeiro. Podemos usar esses dados para traçar linhas de tendência para cada país ou analisar os dados como estão.

Abordagem

Essa consulta é semelhante à consulta anterior - só precisamos adicionar informações sobre o país para cada compra. Como podemos fazer isso?

A tabela SalesOrderHeader tem uma coluna chamada TerritoryId, que se refere à tabela SalesTerritory tabela. Essa tabela nos dá acesso à coluna CountryRegionCode.

Os nomes dos países são armazenados na tabela CountryRegion do Person esquema. Podemos unir essa tabela à tabela SalesTerritory usando o código da região. Isso nos deixa com uma maneira clara de expandir nossa consulta.

Unir as tabelas da seguinte forma SalesOrderHeader -> SalesTerritory -> CountryRegion nos dá acesso à coluna CountryRegion.Name. Podemos adicioná-la como a primeira coluna na instrução SELECT e adicioná-la ao final das instruções GROUP BY e ORDER BY.

Dê uma olhada na consulta final:

SELECT
  cr.Name AS Country,
  EXTRACT(YEAR FROM OrderDate) AS OrderYear,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  ROUND(SUM(TotalDue), 2) AS TotalRevenue
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesTerritory st
  ON soh.TerritoryId = st.TerritoryId
JOIN Person.CountryRegion cr
  ON cr.CountryRegionCode = st.CountryRegionCode
GROUP BY 
  cr.Name,
  EXTRACT(YEAR FROM OrderDate),
  EXTRACT(MONTH FROM OrderDate)
ORDER BY
  OrderYear DESC,
  OrderMonth DESC,
  Country;

Aqui está uma parte do resultado:

Country	OrderYear	OrderMonth	TotalRevenue
Australia	2014	6	9958.82
Canada	2014	6	11471.62
France	2014	6	3660.88
Germany	2014	6	3818.44

Relatório 3: Produtos mais vendidos

Pergunta: Quais são os nossos produtos mais vendidos?

Gostaríamos de adicionar uma lista dos produtos mais vendidos, juntamente com algumas informações relevantes, como vendas vitalícias e avaliações de clientes. O relatório deve mostrar dados razoavelmente recentes.

Abordagem

A tabela SalesOrderDetail que contém a quantidade de cada item vendido, será nosso ponto de partida para essa consulta. Nós a construiremos de forma incremental.

A principal métrica dessa consulta é a quantidade total vendida para cada produto, que podemos obter usando a combinação de SUM(OrderQty) e GROUP BY ProductId.

No entanto, o resultado da consulta seria bastante difícil de ler, pois os produtos só podem ser identificados pelo endereço Id. Os nomes dos produtos são armazenados na tabela Product da tabela Production schema. Unindo as duas tabelas usando ProductId e adicionando ProductName, obtemos essa consulta:

SELECT
  p.ProductId,
  p.Name AS ProductName,
  SUM(od.OrderQty) AS TotalUnitsSold
FROM Sales.SalesOrderDetail od
JOIN Production.Product p
  ON od.ProductID = p.ProductID
GROUP BY
  p.Name,
  p.ProductId
ORDER BY TotalUnitsSold DESC
LIMIT 10;

ProductId	ProductName	TotalUnitsSold
712	AWC Logo Cap	8311
870	Water Bottle - 30 oz.	6815

Essa consulta é um elemento de painel viável, mas pode ser aprimorada.

Já estabelecemos a base para trabalhar com produtos. Agora, queremos adicionar as classificações aos nossos produtos. Vamos criá-la como uma consulta separada, que depois juntaremos à consulta principal.

As avaliações de produtos são armazenadas na tabela ProductReview da tabela Production schema. Nós nos preocupamos principalmente com o ProductId e o Rating deixado pelo cliente, portanto, faremos a média das pontuações de classificação e agruparemos os dados por ProductId.

Agora, temos um mapeamento simples de um para um entre o produto e suas classificações. Vamos arredondar o resultado para uma casa decimal, como de costume nas avaliações:

SELECT
  ProductId,
  ROUND(AVG(Rating), 1) AS ProductRating
FROM Production.ProductReview
GROUP BY ProductId;

ProductId	ProductRating
937	3.0
798	5.0
709	5.0

Agora, podemos agrupar essa consulta em uma expressão de tabela comum (CTE) para usá-la junto com nossa consulta principal. As CTEs são uma forma de criar um conjunto de resultados temporários, como uma tabela virtual que só existe no contexto da consulta. Se você quiser uma explicação completa de como as CTEs funcionam, consulte nosso guia completo sobre CTEs no SQL.

Agora, aqui está um possível problema: talvez não haja avaliações para todos os produtos. Portanto, quando juntamos Product da consulta principal com o CTE, certifique-se de usar um LEFT JOIN para não perder nenhum dado.

Pode ser vantajoso examinar apenas os dados mais recentes. Podemos filtrar as vendas antigas na consulta principal usando a coluna OrderDate e subtrair dois meses da data atual usando INTERVAL ‘2 MONTHS’. Em seguida, podemos filtrar os dados para mostrar apenas os pedidos feitos após essa data.

Se você decidir alterar o intervalo de datas, modificar a parte INTERVAL é muito fácil. Para saber mais sobre a sintaxe INTERVAL, dê uma olhada em nosso cursoFunções Comuns em SQL .

Esta é a consulta completa:

WITH ProductRating AS (
  SELECT
    ProductId,
    ROUND(AVG(Rating), 1) AS ProductRating
  FROM Production.ProductReview
  GROUP BY ProductId
)
SELECT
  p.ProductId,
  p.Name AS ProductName,
  SUM(od.OrderQty) AS TotalUnitsSold,
  pr.ProductRating AS ProductRating
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
  ON od.SalesOrderId = oh.SalesOrderId
JOIN Production.Product p
  ON od.ProductID = p.ProductID
LEFT JOIN ProductRating pr
  ON pr.ProductId = p.ProductId
WHERE OrderDate > CURRENT_DATE - INTERVAL '2 MONTH'
GROUP BY
  p.Name,
  p.ProductId,
  pr.ProductRating
ORDER BY TotalUnitsSold DESC
LIMIT 10;

ProductId	ProductName	TotalUnitsSold	ProductRating
921	Mountain Tire Tube	187	N/A
873	Patch Kit / 8 Patches	181	N/A
870	Water Bottle - 30 oz.	168	N/A
922	Road Tire Tube	160	N/A
878	Fender Set - Mountain	107	N/A
711	Sport-100 Helmet, Blue	96	N/A
712	AWC Logo Cap	95	N/A
708	Sport-100 Helmet, Black	93	N/A
923	Touring Tire Tube	91	N/A
871	Mountain Bottle Cage	84	N/A

Essa é uma lista digna de ser exibida no painel de um representante! A compreensão das tendências de vendas é algo a que todos os funcionários devem ter acesso, e agora temos uma consulta que oferece isso.

Nosso painel está crescendo; vamos adicionar mais algumas métricas!

Relatório 4: Lojas com melhor desempenho

Pergunta: Quais são as 10 principais lojas por vendas nos últimos dois meses?

Gostaríamos de mostrar as dez principais lojas - incluindo lojas on-line - e seus respectivos ganhos de receita nos últimos dois meses. Uma "loja principal" é definida como a que teve a maior receita nos últimos dois meses. Trate todas as lojas on-line como uma única entidade.

Abordagem

Como de costume, começaremos com a tabela SalesOrderHeader tabela. Para descobrir qual loja é responsável por qual venda, vamos dar uma olhada na tabela Store na tabela Sales schema.

Cada loja pode ser vinculada a uma venda por meio da coluna SalesPersonId. Todas as vendas em que SalesPersonId é NULL também têm sua OnlineOrderFlag definida como verdadeira. Isso facilitará o trabalho de agrupamento e exibição das vendas on-line, pois podemos tratar qualquer venda em que SalesPersonId seja NULL como uma venda on-line.

Para não perder dados ao unir as tabelas em SalesPersonId (que às vezes é NULL), use um LEFT JOIN entre SalesOrderHeader e Store. Na instrução SELECT, nos preocupamos com duas coisas: o nome da loja e o total de vendas feitas nessa loja. Não se esqueça de envolver a coluna Store.Name com COALESCE(..., ‘Online’) para garantir que todos os pedidos on-line tenham um rótulo bem legível.

Para filtrar os dados antigos, use a mesma condição WHERE do último relatório:

WHERE OrderDate > CURRENT_DATE - INTERVAL ‘2 MONTHS’.

Ordene os resultados pelo valor total de vendas (mostrando os valores maiores primeiro) e use LIMIT 10 para retornar apenas as 10 principais lojas por receita no período selecionado.

Aqui está a consulta:

SELECT
  COALESCE(s.Name, 'Online') AS StoreName,
  ROUND(SUM(so.TotalDue), 2) AS TotalSalesAmount
FROM Sales.SalesOrderHeader so
LEFT JOIN Sales.Store s
  ON so.SalesPersonId = s.SalesPersonId
WHERE OrderDate > CURRENT_DATE - INTERVAL '2 MONTHS'
GROUP BY s.Name
ORDER BY TotalSalesAmount DESC
LIMIT 10;

StoreName	TotalSalesAmount
Online	2419079.86
Friendly Bike Shop	577136.64
Exceptional Cycles Services	495918.62
Number One Bike Co.	495918.62

Relatório 5: Fontes de receita

Questão: Como a receita on-line se compara à receita off-line?

Para essa métrica do painel, gostaríamos de incluir dados sobre quantos pedidos foram feitos on-line em comparação com os pedidos feitos em uma loja. Os resultados finais devem ser divididos por mês e ano.

Abordagem

Mais uma vez, criaremos essa consulta de forma iterativa. Para dividir as vendas em grupos on-line e off-line, podemos usar o endereço OnlineOrderFlag da tabela Sales.SalesOrderHeader tabela. Essa tabela também inclui, convenientemente, o preço total do pedido, de modo que essa métrica pode ser calculada em uma única tabela.

Entretanto, o OnlineOrderFlag é do tipo de dados BOOLEAN; usá-lo na cláusula GROUP BY nos deixará com os grupos true e false. Para tornar os dados mais legíveis, podemos usar uma expressão CASE WHEN para substituir todas as verdades por "Online" e todas as falsidades por "Store". E como a expressão OnlineOrderFlag já é avaliada como BOOLEAN, não precisamos modificá-la para usá-la como condição. Para obter uma explicação mais detalhada sobre como estamos usando CASE WHEN neste exemplo, leia nosso guia sobre como usar CASE WHEN com GROUP BY.

Agora que temos nomes de grupos adequados, podemos selecionar as outras métricas que comparam as fontes de receita: o número total de negócios fechados e a receita total desses negócios. Você pode encontrar o primeiro simplesmente contando todos os SalesOrderIds e o segundo somando todos os valores de TotalDue. O agrupamento pode ser feito usando a expressão OnlineOrderFlag e não a expressão CASE WHEN; nesse uso, estamos simplesmente mapeando valores.

Esta é a aparência da consulta atual:

SELECT
  CASE WHEN OnlineOrderFlag THEN 'Online' ELSE 'Store' END AS OrderOrigin,
  COUNT(SalesOrderId) AS TotalSales,
  SUM(TotalDue) AS TotalRevenue
FROM Sales.SalesOrderHeader
GROUP BY OnlineOrderFlag
ORDER BY TotalRevenue DESC;

OrderOrigin	TotalSales	TotalRevenue
Online	27659	3244139
Store	3806	90775446

Podemos ver claramente uma conclusão: As lojas físicas fazem quase 10 vezes menos vendas, mas produzem 3 vezes mais receita do que a loja on-line.

Podemos tornar esses dados mais significativos filtrando-os por ano e mês para que possamos ver as tendências nos fluxos de receita. A tabela Sales.SalesOrderHeader inclui a coluna OrderDate, que podemos usar para obter apenas as partes YEAR e MONTH, como fizemos no primeiro relatório. Se adicionarmos isso como colunas extras e as usarmos em GROUP BY, veremos que os grupos “Store” e “Online” foram divididos em anos e meses diferentes. Além disso, podemos filtrar as datas, como fizemos anteriormente com CURRENT_DATE e INTERVAL.

Aqui está a consulta completa e uma amostra dos resultados:

SELECT
  CASE WHEN OnlineOrderFlag THEN 'Online' ELSE 'Store' END AS OrderOrigin,
  EXTRACT(YEAR FROM OrderDate) AS OrderYear,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  COUNT(SalesOrderId) AS TotalSales,
  SUM(TotalDue) AS totalRevenue
FROM Sales.SalesOrderHeader
WHERE OrderDate > CURRENT_DATE - INTERVAL '2 MONTHS'
GROUP BY
  EXTRACT(YEAR FROM OrderDate),
  EXTRACT(MONTH FROM OrderDate),
  OnlineOrderFlag
ORDER BY
  OrderOrigin,
  OrderYear DESC,
  OrderMonth DESC;

OrderOrigin	OrderYear	OrderMonth	TotalSales	TotalRevenue
Online	2014	6	939	54151,48
Store	2014	5	12584	3850111,69
Online	2014	5	406	2156071,52
Store	2014	4	2	1428,61

Relatório 6: Tamanho médio do pedido por país

Pergunta: Qual é o tamanho médio do pedido?

Gostaríamos de exibir cada país e o tamanho médio dos pedidos de todos os pedidos provenientes desse país. Os países com tamanhos médios de pedidos maiores devem ser mostrados primeiro.

Abordagem

Já tratamos da obtenção do nome do país para cada venda em um dos relatórios anteriores, portanto, podemos nos concentrar nos tamanhos dos pedidos aqui. É importante observar que teremos que agregar duas vezes nessa consulta: Primeiro, para obter o tamanho do pedido de cada pedido e, depois, para obter a média do país. Começaremos obtendo os tamanhos dos pedidos.

Cada pedido pode ser identificado por uma entrada na tabela SalesOrderHeader tabela. Ele terá várias entradas respectivas na tabela SalesOrderDetail tabela correspondentes a cada produto do pedido. Ao somar o OrderQtys da tabela SalesOrderDetail para cada SalesOrderId nos deixa com pares de order_number-order_size bem definidos.

Usaremos um par de JOINs para obter os nomes dos países, formando um CTE como este:

WITH OrderSizes AS (
  SELECT
    sod.SalesOrderId,
    SUM(OrderQty) AS ProductCount,
    cr.Name AS Country
  FROM Sales.SalesOrderHeader soh
  JOIN Sales.SalesOrderDetail sod
    ON sod.SalesOrderId = soh.SalesOrderId
  JOIN Sales.SalesTerritory st
    ON soh.TerritoryId = st.TerritoryId
  JOIN Person.CountryRegion cr
    ON cr.CountryRegionCode = st.CountryRegionCode
  GROUP BY
    sod.SalesOrderId,
    cr.Name
)
SELECT * 
FROM OrderSizes;

Agora é só uma questão de obter o tamanho médio do pedido do CTE e agrupar o resultado por país. Não se esqueça de ordenar os dados para mostrar primeiro os maiores tamanhos médios de pedidos.

Dê uma olhada na consulta completa e em seu resultado parcial:

WITH OrderSizes AS (
  SELECT
    sod.SalesOrderId,
    SUM(OrderQty) AS ProductCount,
    cr.Name AS Country
  FROM Sales.SalesOrderHeader soh
  JOIN Sales.SalesOrderDetail sod
    ON sod.SalesOrderId = soh.SalesOrderId
  JOIN Sales.SalesTerritory st
    ON soh.TerritoryId = st.TerritoryId
  JOIN Person.CountryRegion cr
    ON cr.CountryRegionCode = st.CountryRegionCode
  GROUP BY
    sod.SalesOrderId,
    cr.Name
)
SELECT
  Country,
  ROUND(AVG(ProductCount), 2) AS AverageOrderSize
FROM OrderSizes
GROUP BY Country
ORDER BY AverageOrderSize DESC;

Country	AverageOrderSize
United States	12.80
Canada	12.14
France	7.45
United Kingdom	6.24
Germany	5.01
Australia	2.67

Relatório 6: Valor médio vitalício do cliente por região

Pergunta: Qual é o valor médio da vida útil do cliente em cada região?

O Customer Lifetime Value (CLV) é uma métrica importante que mostra quanto a empresa pode esperar que um cliente recém-adquirido gaste durante todo o seu relacionamento com a marca. O CLV é calculado pela soma da receita de todas as compras do cliente. Essa métrica é mais útil ao calcular o orçamento para campanhas de marketing; é melhor mostrá-la agrupada por região de operações.

Abordagem

Vamos primeiro criar a consulta que calcula o CLV médio para cada cliente individualmente. Já sabemos como o CLV é calculado: somando todos os valores TotalDue de cada cliente da tabela SalesOrderHeader da tabela. Isso nos dará um resultado intermediário. Queremos agrupá-lo posteriormente por país, portanto, é bom incluir alguns desses dados aqui também.

A tabela Customer no mesmo esquema tem uma coluna TerritoryId, que podemos usar mais tarde para obter mais informações. Por enquanto, vamos apenas adicionar TerritoryId à consulta CLV como uma coluna adicional. Isso requer uma junção simples e uma expansão da instrução GROUP BY.

Aqui está a aparência da consulta até o momento, juntamente com alguns exemplos de resultados:

SELECT
  cs.CustomerId,
  cs.TerritoryId,
  SUM(TotalDue) AS LifetimeRevenues
FROM sales.Customer cs
JOIN sales.SalesOrderheader ord
  ON cstm.CustomerId = ord.CustomerId
GROUP BY
  cs.CustomerId,
  cs.TerritoryId;

CustomerId	TerritoryId	LifetimeRevenue
26264	4	34.56
30052	4	21863.90
24416	1	106.16
26745	9	2135.37

Metade do problema foi resolvido. O que resta é agrupar isso em nível de país e tornar o resultado mais legível. Vamos usar um CTE para salvar os resultados dessa consulta.

Podemos agrupar o CTE por TerritoryId e mostrar o valor médio da vida útil dos clientes por território. Isso fornece um resultado tecnicamente correto, embora os territórios não sejam identificáveis. Os nomes reais dos países são armazenados na tabela CountryRegion da tabela Person schema. Ela é identificável pela chave CountryRegionCode. No esquema Sales esquema, podemos encontrar essa chave na tabela SalesTerritory tabela. Portanto, juntaremos o resultado do CTE, por meio da tabela SalesTerritory com a tabela CountryRegion tabela. Agora podemos mostrar o nome do país em vez de seu número Id. Não se esqueça de editar o GROUP BY adequadamente.

Dê uma olhada na consulta final e em alguns de seus resultados:

WITH CustomerLifetimeRevenue AS (
  SELECT
    cstm.CustomerId,
    ord.TerritoryId,
    SUM(TotalDue) AS LifetimeRevenue
  FROM Sales.Customer cstm
  JOIN Sales.SalesOrderHeader ord
    ON cstm.CustomerId = ord.CustomerId
  GROUP BY
    cstm.CustomerId,
    ord.TerritoryId
)
SELECT
  cr.Name AS Country,
  ROUND(AVG(clr.LifetimeRevenue),2) AS AvgLifetimeCustomerValue
FROM CustomerLifetimeRevenue clr
JOIN Sales.SalesTerritory tr
  ON clr.TerritoryId = tr.TerritoryId
JOIN Person.CountryRegion cr
  ON cr.CountryRegionCode = tr.CountryRegionCode
GROUP BY cr.Name
ORDER BY
  AvgLifetimeCustomerValue DESC,
  cr.Name;

Country	AvgLifetimeCustomerValue
Canada	10971.34
United States	8627.27
France	4403.33
United Kingdom	4394.69
Australia	3259.14
Germany	3024.18

Crie seu próprio projeto SQL para iniciantes!

Criamos com sucesso consultas que podem alimentar um painel de vendas informativo. No entanto, este artigo tem o objetivo de inspirá-lo a iniciar seu próprio projeto SQL. Vamos recapitular como criar um projeto SQL:

• Encontre um conjunto de dados interessante.
• Crie um problema comercial para esse conjunto de dados, como fizemos com esse painel de vendas.
• Escreva perguntas relacionadas ao problema que o projeto ajudará a responder.
• Crie consultas SQL para as perguntas que você escreveu.

Se você precisar de um pouco mais de estrutura, recomendamos nosso minicurso SQL Databases for Practice (Bancos de dados SQL para prática). Ele contém 6 bancos de dados diferentes que podem ser usados em seu próprio projeto de SQL, além de algumas perguntas e sugestões para você começar.

Se quiser ver mais exemplos de projetos SQL para iniciantes, confira nosso artigo sobre como criar um projeto Northwind Store para o seu portfólio.

Quer mais prática geral? Confira nossa enorme trilhaTrilha de Práticas em SQL . Bom aprendizado!