Prática avançada de SQL: 10 exercícios práticos de SQL com soluções

Como a proficiência em SQL continua sendo muito procurada por profissionais de dados e desenvolvedores, nunca é demais enfatizar a importância da prática. Continue lendo para mergulhar no mundo do SQL avançado e participar de exercícios práticos para aprimorar suas habilidades.

Este artigo apresenta uma coleção de dez exercícios práticos de SQL desafiadores, especificamente para aqueles que buscam aprimorar suas habilidades em SQL avançado. Os exercícios abrangem uma seleção de conceitos de SQL e o ajudarão a atualizar seus conhecimentos avançados de SQL. Cada exercício é acompanhado de uma solução detalhada, permitindo que você teste seus conhecimentos e obtenha uma compreensão mais profunda de conceitos complexos de SQL. Os exercícios são provenientes de nossos cursos práticos de SQL avançado. Se você quiser ver mais exercícios como esse, confira estes cursos:

1. Funções de Janela (Window Functions) em SQL Conjunto de práticas
2. 2021 Mensal Curso de Práticas em SQLs - Avançado
3. 2022 Mensal Curso de Práticas em SQLs - Avançado

Vamos começar.

Praticando seu caminho para a proficiência em SQL

A prática é um componente integral do domínio do SQL; sua importância não pode ser exagerada. A jornada para se tornar proficiente em SQL avançado exige dedicação, perseverança e um forte compromisso com a prática contínua. Ao praticar regularmente o SQL avançado, as pessoas podem aprimorar suas habilidades, expandir seus conhecimentos e desenvolver um entendimento profundo das complexidades do gerenciamento e da manipulação de dados.

SQL Avançado Os exercícios servem como ferramentas valiosas, desafiando os alunos a aplicar seus conhecimentos teóricos em cenários práticos e solidificando ainda mais sua compreensão de conceitos complexos. Com cada sessão de prática dedicada ao SQL, você pode descobrir técnicas eficientes e ganhar a confiança necessária para enfrentar os desafios de dados do mundo real.

Vamos examinar os exercícios e suas soluções.

SQL Avançado Exercícios práticos

Apresentaremos vários exercícios avançados de SQL que abrangem funções de janela, JOINs, GROUP BY, expressões de tabela comum (CTEs) e muito mais.

Seção 1: SQL Avançado Exercícios de JOIN

Nos exercícios avançados de SQL a seguir, usaremos um banco de dados de roupas esportivas que armazena informações sobre roupas, categorias de roupas, cores, clientes e pedidos. Ele contém cinco tabelas: color, customer, category, clothing, e clothing_order. Vamos dar uma olhada nos dados desse banco de dados.

A tabela color contém as seguintes colunas:

• idarmazena o ID exclusivo de cada cor.
• name armazena o nome da cor.
• extra_fee armazena a taxa extra (se houver) adicionada para roupas encomendadas nessa cor.

Na tabela customer, você encontrará as seguintes colunas:

• id armazena os IDs dos clientes.
• first_name armazena o primeiro nome do cliente.
• last_name armazena o sobrenome do cliente.
• favorite_color_idarmazena o ID da cor favorita do cliente (faz referência à tabela de cores).

A tabela category contém essas colunas:

• id armazena o ID exclusivo de cada categoria.
• name armazena o nome da categoria.
• parent_id armazena o ID da categoria principal para essa categoria (se for uma subcategoria). Se esse valor for NULL, isso indica que essa categoria é uma categoria principal. Observação: Os valores estão relacionados aos da coluna id nessa tabela.

A tabela clothing armazena dados nas seguintes colunas:

• id armazena o ID exclusivo de cada item.
• name armazena o nome do item.
• size armazena o tamanho da roupa: S, M, L, XL, 2XL ou 3XL.
• price armazena o preço do item.
• color_id armazena o site color do item (faz referência à tabela de cores).
• category_id armazena a categoria do item (faz referência à tabela de categorias).

A tabela clothing_order contém as seguintes colunas:

• id armazena o ID exclusivo do pedido.
• customer_id armazena o ID do cliente que fez o pedido das roupas (faz referência à tabela customer ).
• clothing_id armazena o ID do item pedido (faz referência à tabela clothing ).
• items armazena a quantidade do item de roupa que o cliente pediu.
• order_date armazena a data do pedido.

Vamos fazer alguns exercícios avançados de SQL que se concentram em JOINs.

Exercício 1: Listar todos os itens de vestuário

Exercício:

Exiba o nome dos itens de vestuário (nomeie a coluna clothes), sua cor (nomeie a coluna color) e o sobrenome e o nome do(s) cliente(s) que comprou(aram) esse vestuário em sua cor favorita. Classifique as linhas de acordo com a cor, em ordem crescente.

Solução:

SELECT
  cl.name AS clothes,
  col.name AS color,
  cus.last_name,
  cus.first_name
FROM clothing_order co
JOIN clothing cl
  ON cl.id = co.clothing_id
JOIN color col
  ON col.id = cl.color_id
JOIN customer c
  ON cus.id = co.customer_id
WHERE cus.favorite_color_id = cl.color_id
ORDER BY col.name;

Explicação da solução:

Queremos exibir os valores das colunas de três tabelas diferentes (clothing, color e customer), incluindo informações sobre qual cliente pediu um determinado item (da tabela clothing_order ). Portanto, precisamos unir essas quatro tabelas em suas colunas comuns.

Primeiro, selecionamos a tabela clothing_order (conhecida como co) e a unimos à tabela clothing (conhecida como cl). Unimos as tabelas usando a coluna de chave primária da tabela clothing (id) e a coluna de chave estrangeira da tabela clothing_order (clothing_id); essa coluna de chave estrangeira vincula as tabelas clothing e clothing_order.

Em seguida, juntamos a tabela color (conhecida como col) com a tabela clothing (conhecida como cl). Aqui usamos a coluna de chave primária da tabela color (id) e a coluna de chave estrangeira da tabela clothing (color_id).

Por fim, juntamos a tabela customer (conhecida como cus) com a tabela clothing_order (conhecida como co). A chave estrangeira da tabela clothing_order (customer_id) está vinculada à chave primária da tabela customer (id).

A cláusula ON armazena a condição para a instrução JOIN. Por exemplo, um item da tabela clothing com um id de 23 é unido a um pedido da tabela clothing_order em que o valor clothing_id é igual a 23.

Siga este artigo para ver mais exemplos de junção de três (ou mais) tabelas. E aqui está como fazer LEFT JOIN em várias tabelas.

Exercício 2: Obter todos os clientes que não estão comprando

Exercício:

Selecione o sobrenome e o nome dos clientes e o nome de sua cor favorita para clientes sem compras.

Solução:

SELECT
  cus.last_name,
  cus.first_name,
  col.name
FROM customer cus
JOIN color col
  ON col.id = cus.favorite_color_id
LEFT JOIN clothing_order o
  ON o.customer_id = cus.id
WHERE o.customer_id IS NULL;

Explicação da solução:

color Aqui precisamos exibir o nome e o sobrenome dos clientes da tabela customer e o nome de sua cor favorita da tabela de cores. Devemos fazer isso somente para os clientes que ainda não fizeram nenhum pedido; portanto, precisamos de informações da tabela clothing_order. Portanto, a próxima etapa é unir essas três tabelas.

Primeiro, juntamos a tabela customer (com o pseudônimo cus) com a tabela color (com o pseudônimo col). Para fazer isso, usamos a seguinte condição: a coluna de chave primária da tabela color (id) deve ser igual à coluna de chave estrangeira da tabela customer (favorite_color_id). Isso nos permite selecionar o nome da cor favorita em vez de seu ID.

Veja como garantir que listaremos apenas os clientes que ainda não fizeram nenhum pedido:

• Nós LEFT JOIN a tabela clothing_order (com o pseudônimo o) com a tabela customer (com o pseudônimo cus) para garantir que todas as linhas da tabela customer (mesmo aquelas sem correspondência) sejam listadas.
• Na cláusula WHERE, definimos uma condição para exibir somente as linhas com a coluna customer_id da tabela clothing_order igual a NULL (ou seja, somente os clientes cujos IDs não estão na tabela clothing_order serão retornados).

Há diferentes tipos de JOINs, incluindo INNER JOIN, LEFT JOIN, RIGHT JOIN e FULL JOIN. Para saber mais, consulte os artigos vinculados.

Exercício 3: Selecionar todas as categorias principais e suas subcategorias

Exercício:

Selecione o nome das categorias principais (que têm um NULL na coluna parent_id) e o nome de sua subcategoria direta (se houver). Nomeie a categoria da primeira coluna e a subcategoria da segunda coluna.

Solução:

SELECT
  c1.name AS category,
  c2.name AS subcategory
FROM category c1
JOIN category c2
  ON c2.parent_id = c1.id
WHERE c1.parent_id IS NULL;

Explicação da solução:

Cada categoria listada na tabela category tem seu próprio ID (armazenado na coluna id ); algumas também têm o ID de sua categoria pai (armazenado na coluna parent_id ). Assim, podemos vincular a tabela category a ela mesma para listar as categorias principais e suas subcategorias.

O tipo de JOIN em que unimos uma tabela a ela mesma é chamado coloquialmente de self join. Ao unir uma tabela a ela mesma, você deve dar nomes de alias diferentes a cada cópia da tabela. Aqui temos uma tabela category com o pseudônimo c1 e outra tabela category com o pseudônimo c2.

Selecionamos name da tabela category (com o alias c1) e garantimos que listamos somente as categorias principais, fazendo com que a coluna parent_id seja igual a NULL na cláusula WHERE. Em seguida, juntamos a tabela category (com o pseudônimo c1) com a tabela category (com o pseudônimo c2). Essa última fornece subcategorias para as categorias principais. Portanto, na cláusula ON, definimos que a coluna parent_id de c2 deve ser igual à coluna id de c1.

Leia este artigo para saber mais sobre junções automáticas.

Os exercícios desta seção foram retirados do nosso curso 2021 Monthly Curso de Práticas em SQL s - Advanced. Todos os meses, publicamos um novo curso de prática de SQL em nossa faixa Monthly Trilha de Práticas em SQL; a cada mês ímpar, o curso é de nível avançado. Os cursos de prática avançada de SQL de 2021 foram reunidos em nosso curso 2021 Monthly Curso de Práticas em SQL s - Advanced. Dê uma olhada nele para encontrar mais exercícios JOIN e outros desafios avançados de SQL.

Seção 2: Exercícios avançados de GROUP BY

Nos exercícios avançados de SQL a seguir, usaremos um banco de dados de um clube esportivo que armazena informações sobre corredores e eventos de corrida. Ele contém três tabelas: runner, event, e runner_event. Vamos dar uma olhada nos dados desse banco de dados.

A tabela runner contém as seguintes colunas:

• id armazena o ID exclusivo do corredor.
• name armazena o nome do corredor.
• main_distance armazena a distância (em metros) que o corredor corre durante os eventos.
• age armazena a idade do corredor.
• is_female indica se o corredor é homem ou mulher.

A tabela event contém as seguintes colunas:

• id armazena o ID exclusivo do evento.
• name armazena o nome do evento (por exemplo, London Marathon (Maratona de Londres), Warsaw Runs (Corrida de Varsóvia) ou New Year Run (Corrida de Ano Novo)).
• start_date armazena a data do evento.
• city armazena a cidade onde o evento é realizado.

A tabela runner_event contém as seguintes colunas:

• runner_id armazena o ID do corredor.
• event_id armazena o ID do evento.

Vamos fazer alguns exercícios avançados de SQL que se concentram em GROUP BY.

Exercício 4: Organizar corredores em grupos

Exercício:

Selecione a distância principal e o número de corredores que correram a distância determinada (runners_number). Exiba somente as linhas em que o número de corredores for maior que 3.

Solução:

SELECT
  main_distance,
  COUNT(*) AS runners_number
FROM runner
GROUP BY main_distance
HAVING COUNT(*) > 3;

Explicação da solução:

Queremos obter a contagem de corredores para cada distância que eles correm. Para fazer isso, precisamos agrupar todos os corredores por distância e usar a função de agregação COUNT() para calcular quantos corredores estão em cada grupo de distância.

Selecionamos a coluna main_distance e GROUP BY essa coluna. Agora, quando usarmos as funções de agregação COUNT(), elas nos darão o número de corredores que correspondem a cada valor main_distance.

A cláusula GROUP BY é usada para agrupar linhas de uma tabela com base em uma ou mais colunas. Ela divide o conjunto de resultados em subconjuntos ou grupos, em que cada grupo compartilha os mesmos valores na(s) coluna(s) especificada(s). Isso nos permite executar funções de agregação (como SUM(), COUNT(), AVG(), etc.) em cada grupo separadamente.

Aqui estão as perguntas mais comuns da entrevista sobre GROUP BY.

Para exibir somente os grupos com mais de três participantes, usamos uma cláusula HAVING que filtra os valores retornados pela função de agregação COUNT().

A cláusula HAVING é frequentemente usada junto com a cláusula GROUP BY para filtrar os dados agrupados com base em condições específicas. Ela funciona de forma semelhante à cláusula WHERE, mas opera nos dados agrupados em vez de em linhas individuais. Consulte este artigo para saber mais sobre a cláusula HAVING.

Exercício 5: Quantos corredores participam de cada evento

Exercício:

Exiba o nome do evento e o número de membros do clube que participam desse evento (chame essa coluna de runner_count). Observe que pode haver eventos em que nenhum associado do clube participe. Para esses eventos, o runner_count deve ser igual a 0.

Solução:

SELECT
  event.name,
  COUNT(runner.id) AS runner_count
FROM event
LEFT JOIN runner_event
  ON runner_event.event_id = event.id
LEFT JOIN runner
  ON runner_event.runner_id = runner.id
GROUP BY event.name;

Explicação da solução:

Aqui queremos exibir o nome do evento da tabela event e o número de participantes da tabela runner. As tabelas event e runner estão vinculadas por uma relação muitos-para-muitos; para unir essas tabelas, também precisamos da tabela runner_event que relaciona eventos e participantes.

Primeiro, selecionamos na tabela event. Em seguida, a LEFT JOIN com a tabela runner_event, que por sua vez é LEFT JOINed com a tabela runner. Por que usamos a LEFT JOIN aqui? Porque queremos garantir que todos os eventos (mesmo os que não têm participantes) sejam exibidos.

Selecionamos o nome do evento e a contagem de todos os participantes; portanto, precisamos GROUP BY o nome do evento para obter a contagem de participantes por evento. Observe que usamos COUNT(runner_id) em vez de COUNT(*). Isso serve para garantir a exibição de zero para eventos sem participantes (ou seja, para eventos que não têm link para nenhum runner_id). Você pode ler mais sobre as diferentes variantes da função COUNT() aqui.

Exercício 6: Agrupar corredores por distância principal e idade

Exercício:

Exiba a distância e o número de corredores para as seguintes categorias de idade: menos de 20, 20-29, 30-39, 40-49 e mais de 50. Use os seguintes aliases de coluna: under_20, age_20_29, age_30_39, age_40_49, e over_50.

Solução:

SELECT
  main_distance,
  COUNT(CASE WHEN age < 20 THEN id END) AS under_20,
  COUNT(CASE WHEN age >= 20 AND age < 30 THEN id END) AS age_20_29,
  COUNT(CASE WHEN age >= 30 AND age < 40 THEN id END) AS age_30_39,
  COUNT(CASE WHEN age >= 40 AND age < 50 THEN id END) AS age_40_49,
  COUNT(CASE WHEN age >= 50 THEN id END) AS over_50
FROM runner
GROUP BY main_distance;

Explicação da solução:

Isso é semelhante ao Exercício 4 - queremos saber o número de corredores por valor de distância. Portanto, selecionamos a coluna main_distance e GROUP BY essa coluna. Em seguida, usamos várias funções de agregação COUNT() para obter o número de corredores por distância. Entretanto, aqui precisamos dividir ainda mais os corredores de acordo com a idade.

A instrução CASE WHEN é útil aqui, pois pode ser usada para avaliar condições e retornar valores diferentes com base nos resultados dessas condições. Podemos passá-la como um argumento para a função de agregação COUNT() para obter o número de corredores que atendem a uma determinada condição. Vamos ver como isso funciona.

CASE WHEN age >= 20 AND age < 30 THEN id END

Essa instrução CASE WHEN retorna id somente quando a idade de um corredor é maior ou igual a 20 e menor que 30. Caso contrário, ela retorna NULL. Quando envolvida na função de agregação COUNT(), ela retorna a contagem de corredores que satisfazem a condição definida na instrução CASE WHEN.

Para obter o número de corredores para cada uma das cinco faixas etárias, precisamos usar tantas funções COUNT() e declarações CASE WHEN quanto o número de faixas etárias. Você pode ler sobre a contagem de linhas combinando CASE WHEN e GROUP BY aqui.

Seção 3: Exercícios avançados de Funções de Janela (Window Functions) em SQL

Nos exercícios avançados de SQL a seguir, usaremos um banco de dados Northwind para uma loja on-line com vários alimentos. Ele contém seis tabelas: customers, orders, products, categories, order_items, e channels. Vamos dar uma olhada nos dados desse banco de dados.

A tabela customers tem 15 colunas:

• customer_id armazena o ID do cliente.
• email armazena o endereço de e-mail do cliente.
• full_name Armazena o nome completo do cliente.
• address armazena a rua e o número da casa do cliente.
• city armazena a cidade onde o cliente mora.
• region armazena a região do cliente (nem sempre aplicável).
• postal_code armazena o CEP/código postal do cliente.
• country Armazena o país do cliente.
• phone Armazena o número de telefone do cliente.
• registration_date armazena a data em que o cliente se registrou.
• channel_id armazena o ID do canal pelo qual o cliente encontrou a loja.
• first_order_id armazena o ID do primeiro pedido feito pelo cliente.
• first_order_date armazena a data do primeiro pedido do cliente.
• last_order_id armazena o ID do último pedido do cliente (ou seja, o mais recente).
• last_order_date armazena a data do último pedido do cliente.

A tabela orders tem as seguintes colunas:

• order_id armazena o ID do pedido.
• customer_id armazena o ID do cliente que fez o pedido.
• order_date armazena a data em que o pedido foi feito.
• total_amount armazena o valor total pago pelo pedido.
• ship_name stores o nome da pessoa para quem o pedido foi enviado.
• ship_address armazena o endereço (número da casa e rua) para onde o pedido foi enviado.
• ship_city armazena a cidade para onde o pedido foi enviado.
• ship_region armazena a região em que a cidade está localizada.
• ship_postalcode armazena o código postal de destino.
• ship_country armazena o país de destino.
• shipped_date armazena a data em que o pedido foi enviado.

A tabela products tem as seguintes colunas:

• product_id armazena o ID do produto.
• product_name armazena o nome do produto.
• category_id Armazena a categoria à qual o produto pertence.
• unit_price armazena o preço de uma unidade do produto (por exemplo, por garrafa, pacote etc.).
• discontinued indica se o produto não é mais vendido.

A tabela categories tem as seguintes colunas:

• category_id armazena o ID da categoria.
• category_name armazena o nome da categoria.
• description armazena uma breve descrição da categoria.

A tabela order_items tem as seguintes colunas:

• order_id armazena o ID do pedido no qual o produto foi comprado.
• product_id armazena o ID do produto comprado no pedido.
• unit_price armazena o preço por unidade do produto. (Observe que isso pode ser diferente do preço na categoria do produto; o preço pode mudar com o tempo e descontos podem ser aplicados).
• quantity armazena o número de unidades compradas no pedido.
• discount armazena o desconto aplicado ao produto em questão.

A tabela channels tem as seguintes colunas:

• id stores the ID of the channel.
• channel_name stores the name of the channel through which the customer found the shop.

Vamos fazer alguns exercícios avançados de SQL que se concentram nas funções de janela.

Exercício 7: Listar os 3 pedidos mais caros

Exercício:

Crie uma classificação densa dos pedidos com base em seu total_amount. Quanto maior o valor, mais alto deve ser o pedido. Se duas ordens tiverem o mesmo total_amount, a ordem mais antiga deverá ser a mais alta (você terá de adicionar a coluna order_date à ordenação). Nomeie a coluna de classificação como rank. Depois disso, selecione somente as ordens com as três classificações densas mais altas. Mostre a classificação, order_id, e total_amount.

Solução:

WITH orders_with_ranking AS (
  SELECT
    DENSE_RANK() OVER(ORDER BY total_amount DESC, order_date) AS rank,
    order_id,
    total_amount
  FROM orders
)
SELECT *
FROM orders_with_ranking
WHERE rank <= 3;

Explicação da solução:

Vamos começar com a primeira parte da instrução. Queremos criar uma classificação densa de pedidos com base em seu total_amount (quanto maior o valor, mais alta a classificação) e seu valor order_date (quanto mais antiga a data, mais alta a classificação). Observe que o valor da classificação pode ser duplicado somente quando as colunas total_amount e order_date forem iguais em mais de uma linha.

Para fazer isso, usamos a função de janela DENSE_RANK(). Em sua cláusula OVER(), especificamos a ordem: descendente para os valores total_amount e ascendente para os valores order_date. Também exibimos as colunas order_id e total_amount da tabela orders.

Até agora, listamos todas as ordens junto com seus valores de classificação densa. Mas queremos ver apenas as três principais ordens (em que a coluna de classificação é menor ou igual a 3). Vamos analisar as etapas que seguimos a partir daqui:

1. Definimos uma expressão de tabela comum (CTE) usando essa instrução SELECT - ou seja, usamos a cláusula WITH seguida do nome do CTE e, em seguida, colocamos a instrução SELECT entre parênteses.
2. Em seguida, selecionamos a partir desse CTE, fornecendo a condição para a coluna de classificação na cláusula WHERE.

Talvez você se pergunte por que precisamos de uma sintaxe tão complexa que define um CTE e depois o consulta. Você pode dizer que poderíamos definir a condição para a coluna de classificação na cláusula WHERE da primeira consulta SELECT. Bem, isso não é possível devido à ordem de execução da consulta SQL.

Temos de usar a Common Table Expression aqui porque não é possível usar funções de janela na cláusula WHERE. A ordem das operações no SQL é a seguinte:

1. FROM, JOIN
2. WHERE
3. GROUP BY
4. Aggregate functions
5. HAVING
6. Window functions
7. SELECT
8. ORDER BY

Você só pode usar funções de janela nas cláusulas SELECT e ORDER BY. Se quiser fazer referência a funções de janela na cláusula WHERE, você deverá colocar o cálculo da função de janela em um CTE (como fizemos em nosso exemplo) ou em uma subconsulta e fazer referência à função de janela na consulta externa.

Siga este artigo para saber mais sobre CTEs e CTEs recursivos.

Para que você saiba um pouco mais sobre as funções de classificação disponíveis, há três funções que permitem classificar seus dados: RANK(), DENSE_RANK(), e ROW_NUMBER(). Vamos vê-las em ação.

Values to be ranked	RANK()	DENSE_RANK()	ROW_NUMBER()
1	1	1	1
1	1	1	2
1	1	1	3
2	4	2	4
3	5	3	5
3	5	3	6
4	7	4	7
5	8	5	8

A função RANK() atribui a mesma classificação se várias linhas consecutivas tiverem o mesmo valor. Em seguida, a próxima linha recebe a próxima classificação, como se as linhas anteriores tivessem valores distintos. Aqui, as classificações 1,1,1 são seguidas por 4 (como se fosse 1,2,3 em vez de 1,1,1).

A função DENSE_RANK() também atribui a mesma classificação se várias linhas consecutivas tiverem o mesmo valor. Então, a próxima linha recebe a próxima classificação, uma maior que a anterior. Aqui, 1,1,1 é seguido por 2.

A função ROW_NUMBER() atribui números consecutivos a cada linha seguinte sem considerar os valores das linhas.

Aqui está um artigo sobre como classificar dados. Você também pode saber mais sobre as diferenças entre as funções de classificação do SQL.

Exercício 8: Calcular deltas entre ordens consecutivas

Exercício:

Neste exercício, vamos calcular a diferença entre dois pedidos consecutivos do mesmo cliente.

Mostre o ID do pedido (order_id), o ID do cliente (customer_id), o total_amount do pedido, o total_amount do pedido anterior com base no order_date (nomeie a coluna previous_value) e a diferença entre o total_amount do pedido atual e o pedido anterior (nomeie a coluna delta).

Solução:

SELECT
  order_id,
  customer_id,
  total_amount,
  LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS previous_value,
  total_amount - LAG(total_amount) OVER(PARTITION BY customer_id ORDER BY order_date) AS delta
FROM orders;

Explicação da solução:

Aqui, selecionamos o ID do pedido, o ID do cliente e o valor total na tabela orders. A função LAG() busca o valor anterior total_amount. Na cláusula OVER(), definimos a função LAG() separadamente para cada cliente e ordenamos o resultado por uma data de pedido. Por fim, subtraímos o valor retornado pela função LAG() do valor total_amount de cada linha para obter o delta.

A coluna previous_value armazena nulo para a primeira linha, pois não há valores anteriores. Portanto, a coluna delta também é nula para a primeira linha. Os valores da coluna delta a seguir armazenam as diferenças entre pedidos consecutivos feitos pelo mesmo cliente.

Vale a pena mencionar que um delta representa a diferença entre dois valores. Ao calcular o delta entre os valores de vendas diárias, podemos determinar a direção do crescimento/declínio das vendas em uma base diária.

Siga este artigo para saber mais sobre o cálculo de diferenças entre duas linhas. E aqui está como calcular as diferenças ano a ano.

Exercício 9: Calcular o total acumulado de compras por cliente

Exercício:

Para cada cliente e seus pedidos, mostre o seguinte:

• customer_id - o ID do cliente.
• full_name - O nome completo do cliente.
• order_id - O ID do pedido.
• order_date - A data do pedido.
• total_amount - O total gasto nesse pedido.
• running_total - o total corrente gasto pelo cliente em questão.

Classifique as linhas por ID do cliente e data do pedido.

Solução:

SELECT
  orders.customer_id,
  customers.full_name,
  orders.order_id,
  orders.order_date,
  orders.total_amount,
  SUM(orders.total_amount) OVER(PARTITION BY orders.customer_id ORDER BY orders.order_date) AS running_total
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id
ORDER BY orders.customer_id, orders.order_date;

Explicação da solução:

Um total em execução refere-se ao cálculo que acumula os valores de uma coluna ou expressão específica à medida que as linhas são processadas em um conjunto de resultados. Ele fornece uma soma contínua dos valores encontrados até a linha atual. Um total em execução é calculado adicionando o valor atual à soma de todos os valores anteriores. Isso pode ser particularmente útil em vários cenários, como o rastreamento de vendas acumuladas, o cálculo de saldos correntes ou a análise do progresso acumulado ao longo do tempo.

Siga este artigo para saber mais sobre o cálculo de um total acumulado. E aqui está um artigo sobre o cálculo de médias em execução.

Selecionamos a ID do cliente, a ID do pedido, a data do pedido e o total do pedido na tabela orders. Em seguida, juntamos a tabela orders com a tabela customers em suas respectivas colunas customer_id para que possamos exibir o nome completo do cliente.

Usamos a função de janela SUM() para calcular o total em execução para cada cliente separadamente (PARTITION BY orders.customer_id) e, em seguida, ordenamos de forma ascendente por data (ORDER BY orders.order_date).

Por fim, ordenamos a saída dessa consulta por ID do cliente e data do pedido.

Seção 4: Exercícios avançados de consulta recursiva

Nos exercícios avançados de SQL a seguir, usaremos um banco de dados de site que armazena informações sobre alunos e cursos. Ele contém três tabelas: student, course, e student_course. Vamos dar uma olhada nos dados desse banco de dados.

A tabela student contém as seguintes colunas:

• id armazena o número de identificação exclusivo de cada aluno.
• name armazena o nome do aluno.
• email Armazena o e-mail do aluno.
• invited_by_id armazena o ID do aluno que convidou esse aluno para o site. Se o aluno se inscreveu sem um convite, essa coluna será NULL.

A tabela course consiste nas seguintes colunas:

• id armazena o número de ID exclusivo de cada curso.
• name armazena o nome do curso.

A tabela student_course contém as seguintes colunas:

• id armazena o ID exclusivo de cada linha.
• student_id armazena o ID do aluno.
• course_id armazena o ID do curso.
• minutes_spent armazena o número de minutos que o aluno passou no curso.
• is_completed é definida como True quando o aluno termina o curso.

Os exercícios desta seção foram retirados do nosso Funções de Janela (Window Functions) em SQL Practice Set. Nesse conjunto, você encontrará mais exercícios de função de janela em bancos de dados que armazenam varejo, acompanham competições e tráfego de sites.

Vamos fazer alguns exercícios avançados de SQL com foco em consultas recursivas.

Exercício 10: Encontre o caminho do convite para cada aluno

Exercício:

Mostre o caminho dos convites para cada aluno (nomeie essa coluna como path). Por exemplo, se a Mary foi convidada pela Alice e a Alice não foi convidada por ninguém, o caminho para a Mary deve ser o seguinte: Alice->Mary.

Inclua os endereços id, name e invited_by_id de cada aluno nos resultados.

Solução:

WITH RECURSIVE hierarchy AS (
  SELECT
	id,
	name,
    invited_by_id,
    CAST(name AS text) AS path
  FROM student
  WHERE invited_by_id IS NULL
  UNION ALL
  SELECT
    student.id,
    student.name,
    student.invited_by_id,
    hierarchy.path || '->' || student.name
  FROM student, hierarchy
  WHERE student.invited_by_id = hierarchy.id
)
 
SELECT *
FROM hierarchy;

Explicação da solução:

Este exercício exige que criemos um valor personalizado para a coluna path que contenha o caminho do convite para cada cliente. Por exemplo, Ann Smith foi convidado por Veronica Knight, que, por sua vez, foi convidado por Karli Roberson; portanto, obtemos a coluna path como Karli Roberson->Veronica Knight->Ann Smith para o nome Ann Smith.

Como você pode notar, precisamos de um mecanismo de recursão para analisar o caminho do convite. Podemos escrever uma consulta recursiva definindo-a com a instrução WITH RECURSIVE, seguida do nome da consulta.

O conteúdo da consulta recursiva hierarchy é o seguinte:

• Selecionamos as colunas id, name e invited_by_id da tabela student. Em seguida, usamos a função CAST() para converter o tipo de coluna name para o tipo de dados TEXT, garantindo uma concatenação suave (com -> e os nomes a seguir) na consulta principal. A condição da cláusula WHERE garante que somente os alunos que não foram convidados sejam listados por essa consulta.
• O operador UNION ALL combina os conjuntos de resultados de duas ou mais instruções SELECT sem remover duplicatas. Aqui, as consultas nas quais o UNION ALL é executado têm os mesmos conjuntos de quatro colunas; o conjunto de resultados de uma é anexado ao conjunto de resultados de outra.
• No próximo comando SELECT, selecionamos novamente as colunas id, name e invited_by_id da tabela student. Em seguida, concatenamos a coluna path (que vem da consulta recursiva da hierarquia, conforme definido na primeira instrução SELECT ) com o sinal -> e o nome do aluno. Para realizar essa concatenação, selecionamos tanto a tabela de alunos quanto a consulta recursiva de hierarquia (é aqui que o mecanismo recursivo entra em ação). Na cláusula WHERE, definimos que a coluna invited_by_id da tabela student é igual à coluna id da consulta recursiva de hierarquia, de modo que obtemos o nome do aluno que convidou o aluno atual; na próxima iteração, obtemos o nome do aluno que convidou esse aluno e assim por diante.

Isso é chamado de consulta recursiva, pois ela consulta a si mesma para percorrer o caminho do convite.

Avançando uma consulta de cada vez

Os exercícios avançados de SQL apresentados neste artigo fornecem uma plataforma abrangente para aprimorar suas habilidades em SQL, uma consulta de cada vez. Ao se aprofundar nas funções de janela, JOINs, GROUP BY e outras, você expandiu sua compreensão dos conceitos complexos de SQL e adquiriu experiência prática na solução de desafios de dados do mundo real.

A prática é a chave para o domínio das habilidades em SQL. Por meio da prática constante, você pode elevar sua proficiência e transformar seu conhecimento teórico em experiência prática. Este artigo apresentou exercícios de nossos cursos; você pode descobrir mais exercícios como este inscrevendo-se em nosso curso:

1. Funções de Janela (Window Functions) em SQL Conjunto de práticas
2. 2021 Mensal Curso de Práticas em SQLs - Avançado
3. 2022 Mensal Curso de Práticas em SQLs - Avançado

Inscreva-se agora e comece gratuitamente! Boa sorte!