Perguntas práticas intermediárias sobre SQL

Pronto para levar suas habilidades em SQL para o próximo nível? Essas perguntas intermediárias o ajudarão a praticar cenários do mundo real usando GROUP BY, CASE WHEN, subconsultas, CTEs e muito mais.

Se você já domina os conceitos básicos de SQL e se sente à vontade para escrever consultas simples, chegou a hora de subir de nível. As habilidades em SQL intermediário são essenciais para lidar com relatórios do mundo real, análises mais profundas e resolver tarefas de dados mais complexas que vão além das instruções básicas do SELECT.

Neste artigo, você praticará técnicas essenciais como GROUP BY, CASE WHEN, subqueries e Common Table Expressions (CTEs). Esses exercícios são baseados em cenários realistas, o que os torna ideais para aspirantes a analistas de dados, desenvolvedores ou qualquer pessoa que esteja se preparando para entrevistas técnicas. Tente resolver cada desafio antes de verificar a solução para aprimorar suas habilidades de resolução de problemas.

Se você deseja desenvolver esses conceitos de forma mais estruturada, confira o Como Criar Relatórios Básicos em SQL em LearnSQL.com.br. É um curso prático e voltado para iniciantes, com foco na transformação de dados brutos em relatórios significativos - um próximo passo perfeito depois de concluir este conjunto de perguntas práticas.

Banco de dados para exercícios

Para resolver os exercícios deste artigo, você usará três tabelas: cliente, produto e compra.

• A tabela de clientes inclui: customer_id, first_name, last_name, email, signup_date, city e country.
• A tabela de produtos contém: product_id, name, category, price, e launch_date.
• A tabela purchase registra as transações e inclui: purchase_id, customer_id, product_id, quantity, total_amount, e purchase_date.
  

Exercício 1: Resumo da receita mensal

Exercício:
Divida os dados de compra por ano e mês e mostre o número de pedidos (orders) e a receita total (revenue) para cada período.

Solução:

SELECT
	EXTRACT(YEAR FROM purchase_date) AS purchase_year,
	EXTRACT(MONTH FROM purchase_date) AS purchase_month,
	COUNT(*) AS orders,
	SUM(total_amount) AS revenue
FROM purchase
GROUP BY
	EXTRACT(YEAR FROM purchase_date),
	EXTRACT(MONTH FROM purchase_date)
ORDER BY
	purchase_year,
	purchase_month;

Explicação:

Para resolver esse problema, precisamos agrupar as compras por ano e mês para que cada período (por exemplo, janeiro de 2023, fevereiro de 2023) seja tratado separadamente. Começamos extraindo o ano e o mês do site purchase_date usando a função EXTRACT, que faz parte do padrão SQL e tem amplo suporte. Alguns bancos de dados podem ter suas próprias alternativas, como DATEPART ou TO_CHAR. O agrupamento por purchase_year e purchase_month evita que todas as compras de janeiro de anos diferentes sejam combinadas em um único grupo. Em seguida, contamos o número de pedidos e somamos a receita total de cada período.

Exercício 2: Localizar pedidos recentes

Exercício:

Localize todas as compras feitas nos últimos 30 dias. Exiba todas as colunas da tabela de compras.

Solução:

SELECT *
FROM purchase
WHERE purchase_date >= CURRENT_DATE - INTERVAL 30 DAY;

Explicação:

Para localizar as compras dos últimos 30 dias, filtramos a tabela purchase usando uma condição de data. Comparamos purchase_date com a data atual menos um intervalo de 30 dias. A palavra-chave CURRENT_DATE fornece a data de hoje e INTERVAL 30 DAY é uma forma padrão de subtrair dias no ANSI SQL. Isso garante que retornaremos apenas as linhas em que a compra ocorreu nos últimos 30 dias. Alguns bancos de dados podem usar uma sintaxe ligeiramente diferente para intervalos de datas, mas a lógica permanece a mesma.

Exercício 3: Produtos com preço acima da média de sua categoria

Exercício:

Retorne todos os produtos que são mais caros do que o preço médio em sua própria categoria. Mostre todas as colunas da tabela de compras.

Solução:

SELECT *
FROM product p
WHERE price >
  	(SELECT AVG(price)
   	FROM product
   	WHERE category = p.category);

Explicação:

Para resolver esse problema, comparamos o preço de cada produto com o preço médio dos produtos da mesma categoria. Usamos uma subconsulta que calcula a média price para um determinado category e, em seguida, verificamos se o preço do produto atual é maior que essa média. Essa é uma subconsulta correlacionada - ela é executada uma vez para cada linha na consulta externa, usando p.category para fazer a correspondência entre as categorias. Usamos o site SELECT * para retornar todas as colunas da tabela de produtos que atendem à condição.

Exercício 4: Segmentos de preço: Econômico, Médio, Premium

Exercício:

Para cada produto, atribua-o a um segmento de preço: "budget" para menos de US$ 20, "middle" para US$ 20 - 99,99 e "premium" para US$ 100 e acima.

Solução:

SELECT
	product_id,
	name,
	price,
	CASE
    		WHEN price < 20 THEN 'budget'
    		WHEN price BETWEEN 20 AND 99.99 THEN 'middle'
    		ELSE 'premium'
	END AS price_segment
FROM product;

Explicação:

Nessa consulta, usamos a expressão CASE para atribuir cada produto a um segmento de preço com base em seu price. A sintaxe CASE funciona como uma estrutura if-else: ela verifica as condições em ordem e retorna o valor correspondente. Aqui, os produtos abaixo de US$ 20 são rotulados como 'budget', aqueles entre US$ 20 e US$ 99,99 são 'middle', e qualquer produto de US$ 100 ou mais é 'premium'. O resultado inclui os detalhes originais do produto junto com uma nova coluna price_segment.

Exercício 5: Agrupamento de clientes por região

Exercício:
Agrupe os clientes em regiões com base em seu país. Suponha que países como 'USA', 'Canada' e 'Mexico' pertençam a 'North America', enquanto todos os outros se enquadram em 'Other'. Para cada região, conte quantos clientes estão atribuídos a ela. O resultado deve incluir region e customer_count.

Solução:

SELECT
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END AS region,
	COUNT(*) AS customer_count
FROM customer
GROUP BY
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END;

Explicação:

Essa consulta usa a expressão CASE para agrupar países em regiões personalizadas - 'North America' para 'USA', 'Canada' e 'Mexico', e 'Other' para o restante. O CASE é usado com frequência para criar suas próprias categorias com base nos valores das colunas. Depois de definirmos essas regiões personalizadas, podemos agrupar por elas para contar quantos clientes se enquadram em cada uma. Esse é um padrão comum quando se trabalha com dados geográficos, níveis de preços ou qualquer lógica de classificação personalizada.

Exercício 6: Gastos do cliente por categoria

Exercício:

Para cada cliente, calcule quanto ele gastou em produtos das categorias "Eletrônicos", "Vestuário" e "Casa". O resultado deve incluir customer_id, electronics_spend, clothing_spend e home_spend como nomes de coluna.

Solução:

SELECT
	customer_id,
	SUM(CASE 
WHEN category = 'Electronics' 
THEN total_amount ELSE 0 END) AS electronics_spend,
	SUM(CASE 
WHEN category = 'Clothing'	
THEN total_amount ELSE 0 END) AS clothing_spend,
	SUM(CASE 
WHEN category = 'Home'    	
THEN total_amount ELSE 0 END) AS home_spend
FROM purchase  p
JOIN product pr 
ON p.product_id = pr.product_id
GROUP BY customer_id;

Explicação:

Nessa consulta, calculamos quanto cada cliente gastou em produtos de categorias específicas usando CASE WHEN dentro de funções agregadas. CASE nos permite verificar a categoria de cada produto e retornar o total_amount somente se ele corresponder à categoria na qual estamos interessados - caso contrário, ele retorna 0. Repetimos esse padrão para 'Electronics', 'Clothing' e 'Home' e envolvemos cada um deles em um SUM() para obter o gasto total por categoria. O resultado inclui uma linha por customer_id com colunas separadas para os gastos em cada categoria. Essa técnica é útil para criar resumos do tipo pivô diretamente no SQL.

Exercício 7: Gasto médio por cliente

Exercício:
Calcule o valor total gasto por cada cliente e, em seguida, retorne a média desses totais de clientes. O resultado final deve incluir uma única coluna: avg_customer_spend.

Solução:

WITH customer_spend AS (
	SELECT customer_id, SUM(total_amount) AS spend
	FROM purchase
	GROUP BY customer_id
)
SELECT AVG(spend) AS avg_customer_spend
FROM customer_spend;

Explicação:

Para resolver isso, primeiro calculamos quanto cada cliente gastou no total agrupando os dados purchase por customer_id e somando os total_amount. Fazemos isso em uma expressão de tabela comum (CTE) chamada customer_spend. Em seguida, na consulta principal, calculamos a média desses totais usando AVG(spend) e a retornamos como avg_customer_spend. Essa abordagem ajuda a separar os cálculos intermediários e mantém a consulta limpa e legível.

Exercício 8: Clientes acima da média

Exercício:

Encontre o valor total gasto por cada cliente e a média de gastos de todos os clientes. Retorne apenas os clientes cujo total de gastos esteja acima da média. O resultado deve incluir as colunas customer_id e total_spend.

Solução:

WITH customer_spend AS ( -- total spend per customer
	SELECT
    	customer_id,
    		SUM(total_amount) AS total_spend
	FROM purchase
	GROUP BY customer_id
), avg_spend AS ( -- 2) average of those totals
	SELECT
    	AVG(total_spend) AS avg_total_spend
	FROM customer_spend
)
SELECT
	cs.customer_id,
	cs.total_spend
FROM customer_spend cs, avg_spend a -- join to include the average in each row
WHERE cs.total_spend > a.avg_total_spend
ORDER BY cs.total_spend DESC;

Explicação:

Começamos calculando o total de despesas por cliente usando um CTE chamado customer_spend. Em seguida, em um segundo CTE chamado avg_spend, calculamos a média desses totais. Na consulta principal, comparamos o total de despesas de cada cliente com a média unindo os dois CTEs. Os clientes cujo total_spend é maior que a média são retornados, juntamente com seus customer_id. O uso de dois CTEs torna a lógica mais fácil de seguir e mantém a consulta limpa.

Exercício 9: Vendas de produtos como porcentagem do total

Exercício:

Calcule a receita total de cada produto. Em seguida, para cada produto, calcule a porcentagem com que ele contribui para a receita total. O resultado deve incluir product_id, revenue e pct_of_total.

Solução:

WITH product_sales AS (
	SELECT product_id, SUM(total_amount) AS revenue
	FROM purchase
	GROUP BY product_id
), total_revenue AS (
	SELECT SUM(revenue) AS total
	FROM product_sales
)
SELECT
	ps.product_id,
	ps.revenue,
	ROUND(100.0 * ps.revenue / tr.total, 2) AS pct_of_total
FROM product_sales ps
CROSS JOIN total_revenue tr
ORDER BY pct_of_total DESC;

Explicação:

Começamos calculando a receita total por produto usando uma expressão de tabela comum (CTE) chamada product_sales. Em seguida, criamos outro CTE chamado total_revenue para calcular a receita geral de todos os produtos. Na consulta final, juntamos os dois CTEs usando CROSS JOIN para que cada linha de produto tenha acesso ao valor da receita total. Em seguida, calculamos a contribuição percentual de cada produto dividindo sua receita pelo total e multiplicando por 100. O resultado inclui product_id, revenue e pct_of_total, ordenados da maior para a menor porcentagem.

Exercício 10: Ativo, mas não em dezembro

Exercício:

Localize os clientes que fizeram mais de um pedido, mas não efetuaram nenhuma compra em dezembro. O resultado deve incluir customer_id, first_name e last_name.

Solução:

WITH multi_order AS (
	SELECT customer_id
	FROM purchase
	GROUP BY customer_id
	HAVING COUNT(*) > 1
), december_buyers AS (
	SELECT DISTINCT customer_id
	FROM purchase
	WHERE EXTRACT(MONTH FROM purchase_date) = 12
)
SELECT
	c.customer_id,
	c.first_name,
	c.last_name
FROM customer c
JOIN multi_order mo 
ON c.customer_id = mo.customer_id
LEFT JOIN december_buyers d 
ON c.customer_id = d.customer_id
WHERE d.customer_id IS NULL;

Explicação:

Essa consulta encontra clientes que fizeram mais de um pedido, mas nenhum em dezembro. Primeiro, o CTE multi_order identifica os clientes com mais de uma compra agrupando por customer_id e usando HAVING COUNT(*) > 1. O CTE december_buyers seleciona todos os clientes que fizeram pelo menos uma compra em dezembro extraindo o mês de purchase_date. Na consulta final, juntamos a tabela customer com multi_order para obter os clientes relevantes e, em seguida, usamos um LEFT JOIN com december_buyers para verificar quem não fez uma compra em dezembro. Filtramos aqueles que fizeram a compra verificando WHERE d.customer_id IS NULL. O resultado inclui apenas customer_id, first_name e last_name.

Mais Trilha de Práticas em SQL

Praticar SQL regularmente é uma das maneiras mais eficazes de passar da compreensão da teoria para a confiança em cenários do mundo real. Os exercícios deste artigo abordaram os principais tópicos intermediários, como GROUP BY, CASE WHEN, subqueries e CTEs - todas as habilidades que aparecem com frequência em análises de dados, relatórios e entrevistas técnicas. Se você chegou até aqui, está no caminho certo para se tornar proficiente em SQL.

Mas não pare por aqui. A consistência é fundamental ao aprender SQL, e a melhor maneira de continuar melhorando é resolver problemas mais práticos. Se estiver procurando uma prática estruturada e prática com feedback instantâneo, recomendamos a trilha Trilha de Práticas em SQL em LearnSQL.com.br. Ele foi criado para ajudá-lo a fortalecer suas habilidades por meio de desafios reais de consulta que se baseiam uns nos outros - sem enrolação, apenas prática que importa.

Se o seu objetivo é conseguir um emprego na área de dados, automatizar relatórios ou trabalhar de forma mais eficiente com bancos de dados, o curso Trilha de Práticas em SQL é o próximo passo que vale a pena.