Prática de SQL: 10 exercícios práticos de GROUP BY com soluções detalhadas

Precisa praticar suas habilidades em SQL? Estes 10 exercícios práticos de GROUP BY - com explicações e soluções - são um ótimo começo!

GROUP BY GROUP BY é uma cláusula SQL poderosa que permite criar grupos de registros e, em seguida, calcular métricas resumidas (como médias) para esses grupos. No entanto, GROUP BY costuma ser um desafio para os alunos de SQL dominarem. No entanto, praticar o GROUP BY é muito importante se você planeja usar o SQL.

Neste artigo, reunimos vários exercícios práticos de GROUP BY para ajudá-lo a solidificar sua compreensão de como essa cláusula funciona.

Índice

• Introdução
• Revisão da cláusula GROUP BY
• Exercícios
  • Exercício 1: Receita total de cada empresa
  • Exercício 2: Jogos produzidos por ano com receita e custo médios
  • Exercício 3: Número de jogos lucrativos de cada tipo de jogo
  • Exercício 4: Receita total por tipo de jogo nos sistemas PS2 e PS3
  • Exercício 5: Lucro bruto total por empresa
  • Exercício 6: Lucro bruto anual por empresa
  • Exercício 7: Diferença entre os lucros brutos anuais
  • Exercício 8: Empresas que produzem mais de um jogo
  • Exercício 9: Empresas que produzem "bons" jogos com receita superior a 4 milhões
  • Exercício 10: Empresas líderes por tipo de jogo
  • Exercício 11: Continue a praticar suas habilidades em SQL

Prática de GROUP BY

Este artigo contém 10 exercícios do site GROUP BY para ajudá-lo a praticar. Aprender SQL por meio de exercícios é uma das melhores maneiras de aprimorar suas habilidades.

Os exercícios deste artigo são provenientes de nosso Curso de Práticas em SQL. Ele contém mais de 80 exercícios interativos de SQL que abrangem tópicos como consultas simples, JOINs, subconsultas e, é claro, GROUP BY. Também oferecemos outros conjuntos de exercícios, incluindo:

• Basic Trilha de Práticas em SQL: A Store
• Básico Trilha de Práticas em SQL: Universidade
• Básico Trilha de Práticas em SQL: Blog e dados de tráfego

Agora que você já sabe quais recursos práticos de SQL estão disponíveis, vamos nos aprofundar na cláusula GROUP BY. Se você não sabe nada sobre GROUP BY, comece lendo GROUP BY em SQL Explained e 5 Exemplos de GROUP BY.

A cláusula GROUP BY

Nestes exercícios práticos do site GROUP BY, usaremos uma tabela chamada games que armazena informações sobre videogames. Precisamos enfatizar o fato de que, embora os nomes dos jogos sejam reais, os outros campos da tabela contêm dados totalmente inventados. Abaixo está uma visão parcial da tabela:

Vamos revisar brevemente a cláusula GROUP BY. Essa cláusula nos permite criar grupos de registros e calcular diferentes métricas para cada grupo (como a média, o mínimo ou o máximo de valores em cada conjunto). Abaixo está uma consulta simples que usa GROUP BY para calcular o número de jogos produzidos por cada empresa:

SELECT 
  company, 
  COUNT(*)
FROM games
GROUP BY company;

Essa consulta informa ao banco de dados para criar grupos de linhas da tabela games que tenham o mesmo valor na coluna company. (Na imagem abaixo, as linhas com a mesma cor estão no mesmo grupo, ou seja, são feitas pela mesma empresa). Em seguida, a função COUNT(*) é usada para contar a quantidade de linhas em cada grupo; isso retorna o número de jogos produzidos por cada empresa.

Aqui está a tabela com as linhas codificadas por cores. Cada linha com a mesma cor pertence ao mesmo grupo:

E aqui está o resultado:

Exercício 1: Receita total de cada empresa

Exercício:

Obtenha o nome e a receita total de cada empresa.

Solução:

SELECT
  company, 
  SUM(revenue)
FROM games
GROUP BY company;

Explicação:

Na consulta, usamos a cláusula GROUP BY company para criar grupos de linhas com o mesmo valor na coluna company. Em seguida, a função SUM(revenue) é executada para cada grupo de linhas e o resultado é mostrado junto com o nome da empresa.

Cada linha do resultado é associada a um grupo de linhas. Esse é um ponto importante quando se usa o GROUP BY: os detalhes individuais da linha são recolhidos em uma linha por grupo, e os dados retornados são sobre o grupo de linhas.

Antes de passar para o próximo exercício, gostaria de recomendar o curso Desafio SQL do Mês, onde você pode encontrar uma fonte adicional de exercícios práticos de SQL. Lançamos um novo curso de prática de SQL todos os meses.

Exercício 2: Jogos produzidos por ano com receita e custo médios

Exercício:

Gere um relatório com o ano de produção e o número de jogos lançados nesse ano (denominado count), a média do custo de produção de todos os jogos produzidos nesse ano (denominado avg_cost) e a receita média desse ano (denominado avg_revenue).

Solução:

SELECT   
  production_year,
  COUNT(*) AS count,
  AVG(production_cost) AS avg_cost,
  AVG(revenue) AS avg_revenue
FROM games
GROUP BY production_year;

Explicação:

Este exercício é semelhante ao anterior. Aqui, usamos as funções de agregação COUNT() e AVG() para calcular as métricas. Além disso, renomeamos as colunas no relatório para descrever adequadamente seu conteúdo (como contagem, avg_cost e avg_revenue). Para renomear uma coluna, usamos a cláusula AS seguida do nome a ser atribuído à coluna, por exemplo:

AVG(production_cost) AS avg_cost

Exercício 3: Número de jogos lucrativos de cada tipo de jogo

Exercício:

Conte quantos jogos de um determinado tipo são lucrativos (ou seja, a receita foi maior que o custo de produção). Mostre o tipo de jogo e o número de jogos lucrativos (denominado number_of_games) para cada tipo.

Solução:

SELECT    
  type,
  COUNT(*) AS number_of_games
FROM games
WHERE revenue > production_cost
GROUP BY type;

Explicação:

Neste exercício, a condição WHERE é executada primeiro. Ela determina quais jogos são lucrativos (têm receita maior que o custo de produção). Depois disso, GROUP BY agrupa as linhas (jogos) do mesmo type. Por fim, a função COUNT(*) é aplicada a cada grupo de linhas para obter o número de jogos lucrativos de cada tipo.

Exercício 4: Receita total por tipo de jogo nos sistemas PS2 e PS3

Exercício:

Obtenha o tipo de jogo e a receita total gerada para jogos com um production_year após 2010 e com um PS2 ou PS3 system. Ordene o resultado de modo que os tipos com a maior receita venham primeiro.

Solução:

SELECT
  type,
  SUM(revenue) AS total_revenue
FROM games
WHERE production_year > 2010 AND systems IN ('PS2','PS3')
GROUP BY type;

Explicação:

Como no exercício anterior, primeiro filtramos as linhas; desta vez, as condições são production_year > 2010 e system IN (‘PS2’, ‘PS3’). Depois de filtrar as linhas, criamos grupos de linhas usando a cláusula GROUP BY type. Por fim, aplicamos a função SUM(revenue) a cada grupo de linhas para obter a receita total desse grupo.

Exercício 5: Lucro bruto total por empresa

Exercício:

Para todas as empresas presentes na tabela, obtenha seus nomes e a soma do lucro bruto em todos os anos. (Suponha que o lucro bruto = receita - custo de produção). Nomeie essa coluna como gross_profit_sum. Ordene os resultados por lucro bruto, em ordem decrescente.

Solução:

SELECT
  company,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM games
GROUP BY company
ORDER BY gross_profit_sum DESC;

Explicação:

Na consulta, usamos a expressão revenue - production_cost para calcular o lucro bruto de cada jogo. Se criarmos grupos usando a coluna company e depois somarmos o lucro bruto de todos os jogos dessa empresa, obteremos o lucro bruto global da empresa.

Observe a cláusula ORDER BY gross_profit_sum DESC. A palavra-chave DESC indica ordem decrescente; em outras palavras, o valor mais alto de gross_profit_sum aparece primeiro no resultado.

Exercício 6: Lucro bruto anual por empresa

Exercício:

Obter o lucro bruto anual de cada empresa. Em outras palavras, queremos um relatório com o nome da empresa, o ano e o lucro bruto desse ano. Ordene o relatório por nome da empresa e ano.

Solução:

SELECT
  company,
  production_year,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM  games
GROUP BY company, production_year 
ORDER BY company, production_year;

Explicação:

Como queremos obter o lucro bruto discriminado por ano, precisamos usar o GROUP BY company, production_year clause. Isso cria um grupo de linhas para cada par de valores company e production_year. Para cada grupo, calculamos a expressão SUM(revenue - production_cost); esse é o lucro bruto.

Exercício 7: Diferença entre lucros brutos anuais

Exercício:

Gerar um relatório para mostrar o lucro bruto anual de cada empresa, o lucro bruto do ano anterior e a diferença entre os dois anos. Sugiro usar a consulta anterior como ponto de partida.e

Solução:

WITH company_gross_profit AS (
  SELECT
    company,
    production_year AS year,
    SUM(revenue - production_cost) AS gross_profit
  FROM games
  GROUP BY company, production_year 
  ORDER BY company, production_year 
)
SELECT 
  cur.company,
  cur.year,
  cur.gross_profit,
  prev.gross_profit,
  cur.gross_profit - prev.gross_profit AS profit_delta
FROM company_gross_profit AS cur, 
LEFT JOIN company_gross_profit AS prev 
ON cur.company = prev.company AND cur.year = prev.year + 1;

Explicação:

Essa consulta é baseada em um CTE chamado company_gross_profit, que é criado usando a cláusula WITH na primeira parte da consulta. Depois disso, a consulta principal faz referência à tabela CTE company_gross_profit duas vezes em um self-join; a tabela "esquerda" ou "primeira" é chamada de cur (atual) e a outra é chamada de prev (anterior). Em seguida, juntamos duas linhas da mesma empresa, mas de anos contíguos. A condição para fazer isso é:

cur.company = prev.company AND cur.year = prev.year + 1

Por fim, a coluna profit_delta é um campo calculado. Ela é obtida usando a diferença entre o lucro bruto do ano atual e o lucro bruto do ano anterior:

cur.gross_profit - prev.gross_profit AS profit_delta

As CTEs, ou expressões de tabela comuns, são um recurso avançado do SQL. Recomendo os artigos A Guide to SQL Common Table Expressions, CTEs SQL Explicados com Exemplos e How to Learn SQL Common Table Expressions (CTEs) se você quiser saber mais sobre CTEs.

Exercício 8: Empresas que produzem mais de um jogo

Exercício:

Para cada empresa, selecione seu nome, o número de jogos que produziu (como a coluna number_of_games ) e o custo médio de produção (como a coluna avg_cost ). Mostre apenas as empresas que produzem mais de um jogo.

Solução:

SELECT 
  company,
  COUNT(company) AS number_of_games,
  AVG(production_cost) AS avg_cost
FROM games
GROUP BY company
HAVING COUNT(*) > 1;

Explicação:

Nessa consulta, usamos a cláusula HAVING em vez da cláusula WHERE. Enquanto a WHERE filtra registros individuais, a HAVING é usada para aplicar filtros a grupos de registros.

Nessa consulta, queremos um relatório que mostre apenas as empresas que produziram mais de um jogo. Depois de GROUP BY company, podemos usar a condição COUNT(*) > 1 para descartar as empresas com apenas um jogo.

No SQL, você só pode usar diretamente as condições que envolvem funções de agregação na cláusula HAVING; na cláusula WHERE, você teria de usar uma subconsulta. Isso ocorre porque o WHERE é processado antes das funções de agregação na ordem de operações do SQL.

Se você não tiver certeza sobre a cláusula HAVING, recomendo nosso artigo The SQL HAVING Clause Explained (A cláusula HAVING do SQL explicada), no qual discutimos detalhadamente como usar essa cláusula.

Exercício 9: Empresas que produzem jogos "bons" com receita superior a 4 milhões

Exercício:

Estamos interessados em bons jogos produzidos entre 2000 e 2009. Um bom jogo é um jogo lucrativo com uma classificação superior a 6. Para cada empresa, mostre o nome da empresa, sua receita total de jogos bons produzidos entre 2000 e 2009 (como a coluna revenue_sum ) e o número de jogos bons que ela produziu nesse período (como a coluna number_of_games ). Mostre apenas as empresas com receita de jogos de qualidade superior a 4.000.000.

Solução:

SELECT 
  company,
  COUNT(company) AS number_of_games,
  SUM(revenue) AS revenue_sum
FROM games
WHERE production_year BETWEEN 2000 AND 2009
  AND rating > 6
  AND revenue - production_cost > 0
GROUP BY company
HAVING SUM(revenue) > 4000000;

Explicação:

Na consulta, usamos a empresa GROUP BY porque as métricas que queremos obter (number_of_games e revenue_sum) estão no nível da empresa. O ponto interessante dessa consulta são os filtros que aplicamos aos dados. Alguns deles estão em nível de linha e devem estar na cláusula WHERE:

• production_year BETWEEN 2000 AND 2009
• rating > 6
• revenue - production_cost > 0

No entanto, há outro filtro em nível de grupo, que deve ser colocado na coluna HAVING:

• SUM(revenue) > 4000000

Exercício 10: Empresas líderes por tipo de jogo

Exercício:

Retornar uma lista das empresas e dos tipos de jogos em que a empresa é líder de mercado. Um líder de mercado para um tipo de jogo é uma empresa que tem uma receita total para esse tipo de jogo que excede a receita total de todas as outras empresas para esse tipo de jogo.

Mostre o nome da empresa, o tipo de jogo e a receita total da empresa para esse tipo de jogo.

Solução:

SELECT 
  company, 
  type, 
  SUM(revenue) as total_revenue
FROM games g1
GROUP BY company, type
HAVING SUM(revenue) > ( SELECT SUM(revenue) 
                        FROM games g2
                        WHERE g2.type = g1.type
                        AND g2.company <> g1.company );

Explicação:

Antes de tudo, observe a cláusula GROUP BY company, type. Nós a usamos porque queremos a receita da empresa para todos os jogos do mesmo tipo.

Entretanto, o ponto mais interessante é o uso de uma subconsulta na cláusula HAVING. Queremos empresas com uma soma de receita maior que a receita total do restante das empresas; usamos uma subconsulta para obter a receita total das outras empresas.

A subconsulta faz referência às colunas g1.company e g1.type; essas são as colunas company e type na consulta externa. Essas referências na subconsulta nos permitem obter a receita total do restante das empresas para o mesmo tipo de jogo.

Exercício 11: Continue a praticar suas habilidades em SQL

Cobrimos dez exercícios do site GROUP BY com diferentes níveis de complexidade, desde agregados simples até CTEs e subconsultas. Aprender GROUP BY usando exercícios práticos é uma das melhores abordagens para dominar essa cláusula. O artigo TOP 9 SQL GROUP BY Interview Questions apresenta as perguntas mais comuns feitas durante uma entrevista de emprego sobre SQL; essa também é uma ótima maneira de praticar se você não tiver uma entrevista em breve.

Nosso pacote Ilimitado Vitalício SQL é outra maneira de praticar SQL - e uma maneira muito abrangente! Esse pacote contém todos os nossos mais de 70 cursos atuais de SQL em quatro dialetos SQL diferentes (e acesso aos futuros cursos que criarmos). Todos os cursos em nossa plataforma são práticos e interativos. Portanto, escolha seu caminho de prática e comece a ser incrível no uso do GROUP BY!