6 Consultas SQL Avançadas para Análise de Dados Financeiros

Você é um usuário avançado de SQL que é novo em finanças? Você quer aprender como usar seus conhecimentos para analisar dados financeiros? O artigo lhe dará alguma orientação.

Você provavelmente sabe que SQL é uma ferramenta potente para analisar todos os tipos de dados. Quanto maiores e mais complexos os dados, mais benéfico se torna o SQL. Em certas situações, os dados financeiros podem ser muito complicados; produzir uma análise sofisticada requer ferramentas sofisticadas. SQL é uma dessas ferramentas, e quanto melhor você conhecer algumas práticas SQL avançadas, mais fácil se torna realizar análises complexas de dados financeiros e criar relatórios financeiros. Vou lhes mostrar alguns exemplos de como fazer isso, extraídos de minha própria experiência.

O nível de conhecimento SQL exigido neste artigo - como funções de janela, extensões GROUP BY e consultas recursivas - é coberto na faixa LearnSQL.com.br SQL Avançado .

Introduzindo o banco de dados

Todos os seis exemplos utilizarão este banco de dados, que consiste de cinco tabelas:

• country
• card_type
• customer
• card_number
• card_transaction

Vamos imaginar que este é o banco de dados de uma empresa de processamento de cartões de crédito e que você é o empregado deles quando se trata de analisar dados financeiros.

Agora, vamos entrar nos detalhes de cada tabela.

A tabela country tem os seguintes atributos:

• id - A identificação do país e a chave primária (PK) desta tabela.
• country_name - O nome do país.

A tabela seguinte é card_typecom estes atributos:

• id - O ID do tipo de cartão; a chave primária (PK).
• card_type_name - O nome do tipo de cartão.

A tabela customer consiste nas colunas:

• id - O ID do cliente; a chave primária (PK).
• NIN - O número de identificação nacional do cliente.
• first_name - O primeiro nome do cliente.
• last_name - O sobrenome do cliente.
• country_id - O ID do país; esta é uma chave estrangeira (FK) que faz referência à tabela country.

A seguir vêm as card_number tabela com os atributos:

• id - O ID do cartão; a chave primária (PK).
• card_number - O número do cartão.
• customer_id - A identificação do cliente; uma chave estrangeira (FK) que faz referência à tabela customer.
• card_type_id - O ID do tipo de cartão; uma chave estrangeira (FK) que faz referência à tabela card_type.

O último é o card_transaction tabela, que tem estas colunas:

• id - O ID da transação; a chave primária (PK).
• date - A data da transação.
• amount - O valor da transação, em dólares.
• card_number_id - A identificação do cartão; uma chave estrangeira (FK) que faz referência à tabela card_number.

Agora que você está familiarizado com o banco de dados, vamos passar para os exemplos!

6 Exemplos de SQL Avançado Consultas para análise financeira

1: Dados do Grupo por Ano e Trimestre

Os dados financeiros geralmente precisam ser agrupados em períodos de tempo ou baldes de tempo específicos. Você certamente precisará agrupar os dados em anos e trimestres se você estiver criando relatórios financeiros. Vou lhe mostrar como fazer isso. O aprendizado deste princípio lhe permitirá agrupar dados em qualquer outro nível (ou seja, meses, semanas ou dias, dependendo dos dados que você tiver).

Neste exemplo, vou agrupar os dados por anos e trimestres e mostrar o número de transações. Para conseguir isso, vou usar a tabela card_transaction e este código:

SELECT	EXTRACT(YEAR FROM date) AS year,
		EXTRACT(QUARTER FROM date) AS quarter,
		COUNT(amount) AS number_of_transactions
FROM card_transaction
GROUP BY EXTRACT(YEAR FROM date), EXTRACT(QUARTER FROM date)
ORDER BY EXTRACT(YEAR FROM date) ASC, EXTRACT(QUARTER FROM date);

O código usa a função EXTRACT() para obter os anos e trimestres. Esta é uma função SQL padrão, que funcionará no MySQL e no PostgreSQL. Entretanto, se você estiver usando o SQL Server, você terá que usar uma função diferente - explicarei qual em um momento.

Quando você utiliza a função EXTRACT(), é necessário especificar o período desejado e a coluna que esta função utilizará para retornar o período desejado. Primeiro, eu quero obter anos a partir da coluna de datas. Depois, quero trimestres, também da coluna de datas. Depois disso, tenho que contar o número de transações, o que eu fiz usando a função COUNT(). Os dados são agrupados pelas funções EXTRACT(), que representam anos e trimestres. Finalmente, os dados são ordenados por anos e trimestres de forma ascendente, já que quero ver o primeiro trimestre do primeiro ano no topo do relatório. Tenho certeza de que você quer ver o resultado:

Este relatório parece muito bonito, devo dizer!

Agora, se você estiver usando o SQL Server, terá que usar a função DATEPART() ao invés de EXTRACT(). Aqui está a primeira parte do código, apenas para ter certeza de que você entendeu. Você segue o mesmo princípio no restante do código:

SELECT	DATEPART(YEAR, date) AS year,
		DATEPART(QUARTER, date) AS quarter,
		COUNT(amount) AS number_of_transactions
...

2: Calcular totais de execução

Sua tarefa agora é mostrar os totais em execução para todas as transações feitas em dezembro de 2020. O relatório tem que ser agregado no nível do tipo de cartão. Para criar este relatório, você precisará introduzir funções de janela e JOINs ao código.

Antes de continuar, talvez você deva verificar seus conhecimentos sobre SQL. Você pode fazer isso na trilha do relatório SQL. Esta faixa também lhe ensinará como criar relatórios SQL básicos e realizar análises de tendência de receita e comportamento do cliente.

O código que retornará os dados desejados é:

SELECT	DISTINCT (ct.date),
		cty.card_type_name,
		SUM (ct.amount) OVER (PARTITION BY cty.card_type_name ORDER BY ct.date ASC) AS transaction_running_total
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
WHERE date > '2020-11-30' AND date <= '2020-12-31'
ORDER BY cty.card_type_name ASC;

O código primeiro seleciona a data específica, pois pode haver múltiplas transações diárias pelo mesmo tipo de cartão, mesmo o mesmo número de cartão. Em seguida, segue a coluna card_type_name. A última coluna na consulta é transaction_running_total. Para calcular o total em execução, usei uma função de janela. Primeiro, os dados na quantidade da coluna são somados. Depois, usando a cláusula OVER(), especifiquei que o total corrido deve ser calculado em um nível de tipo de cartão; daí PARTITION BY cty.card_type_name. Finalmente, quero que o total corrido seja calculado da data mais antiga para a mais nova: ORDER BY date ASC.

Para obter os dados, eu tive que juntar três tabelas. A primeira união conecta o card_transaction e card_number tabelas. A segunda junta faz referência à tabela card_typeque é como recebo o nome do tipo de cartão em meu relatório. Atribuí pseudônimos a todas as mesas unidas; assim, tive que digitar menos. O resultado é filtrado usando a cláusula WHERE, que me dará apenas as transações de dezembro de 2020. Decidi ordenar os dados pelo nome do tipo de cartão em um tipo ascendente.

A execução do código resultará no relatório mostrado abaixo:

Se você quiser saber mais sobre totais acumulados em SQL, leia este artigo para saber o que são totais em execução e como calculá-los.

3: Calcular as médias de execução

O próximo exemplo é a criação de um relatório que mostrará cada transação em dezembro de 2020 e seu valor para os cartões Visa Electron. Além disso, você mostrará o valor médio diário da transação usando uma média móvel. Dê uma olhada no código:

SELECT	ct.date,
		cty.card_type_name,
		SUM(ct.amount) AS daily_sum,
		AVG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS transaction_running_average
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
WHERE ct.date > '2020-11-30' AND date <= '2020-12-31' 
AND cty.card_type_name = 'visa-electron'
GROUP BY ct.date, cty.card_type_name
ORDER BY cty.card_type_name;

Primeiro selecionei as colunas que mostrarão a data da transação e o nome do tipo de cartão. Em seguida, somei o valor da transação diária, com o resultado mostrado na coluna daily_sum. Tive que fazer isso porque pode haver várias transações feitas diariamente pela Visa Electron. Em seguida, usei esta soma diária dos valores das transações para calcular sua média. Entretanto, para obter a média móvel, tenho que usar a cláusula OVER(). Quero que a média móvel seja calculada do primeiro ao último dia de dezembro de 2020, de modo que a operação seja ordenada por data ascendente.

No cálculo das médias móveis, decidi utilizar uma média móvel de três dias, definida pela parte seguinte do código: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Isto diz ao código para usar a linha atual e as duas linhas anteriores (três linhas/três datas no total) para calcular a média móvel.

As tabelas são então unidas exatamente da mesma forma que no exemplo anterior. Há duas condições na cláusula WHERE; uma define as datas levadas em conta; a outra define o tipo de cartão.

Esta consulta lhe dará a seguinte tabela:

Se você não estiver familiarizado com médias móveis, deixe-me explicar como elas funcionam. Você já sabe que esta é uma média móvel de três dias, que usa três filas para calcular a média.

Para a data '2020-12-01', a média na tabela acima é a mesma que a soma diária. Isto porque a consulta tem apenas os dados da linha atual para usar - não há linhas anteriores.

Para a data '2020-12-03', a média móvel é calculada da seguinte forma: (16,881.70 + 17,375.79) / 2 = 17,128.75. Tenha cuidado aqui! A média móvel não é calculada da seguinte forma: (16.881,70 + 17.375,79) / 3 = 11.419,16. Isso porque há apenas uma linha anterior, ou apenas dois valores para a média.

A média móvel para a data "2020-12-13" é calculada da seguinte forma: (16.881,70 + 17.375,79 + 17.725,49) / 3 = 17.327,66.

4: Análise de séries temporais

Um requisito muito comum quando se trabalha com dados financeiros é analisar uma série temporal (ou seja, a diferença entre períodos de tempo, como o dia-a-dia ou mês a mês). Por exemplo, suponha que seu chefe lhe tenha dito para criar um relatório que mostrará as mudanças diárias dos valores das transações feitas pelos clientes da China durante dezembro de 2020. O departamento de vendas na China não está satisfeito com o desempenho de dezembro de 2020, portanto, eles querem analisar este mês em detalhes para descobrir onde houve uma queda nas transações.

Para criar tal relatório, você precisará novamente de uma função de janela SQL. Desta vez, será a função LAG(), que lhe permitirá buscar dados da linha anterior. Veja como você deve fazer isso:

SELECT	ct.date,
		SUM(ct.amount) AS daily_sum,
		(SUM(ct.amount)-LAG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC)) 
AS daily_difference,
		co.country_name
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN customer cu ON cn.customer_id = cu.id 
JOIN country co ON cu.country_id = co.id
WHERE ct.date > '2020-11-30' AND date <= '2020-12-31'
AND co.country_name = 'China'
GROUP BY ct.date, co.country_name;

A consulta começa com um processo familiar: seleciona a data, depois calcula a soma diária das transações (no caso de haver várias transações diárias da China). Para calcular a diferença diária, você precisa deduzir a soma da transação do dia anterior da soma do dia atual. Este cálculo é feito por esta parte da consulta:

(SUM(ct.amount)-LAG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC)) AS daily_difference. 

A soma da transação do dia anterior é devolvida pela função LAG() combinada com a cláusula OVER(). A operação deve ser realizada da data mais antiga para a mais nova, que especificamos em ORDER BY ct.date ASC. A última coluna da consulta é a coluna country_name do país da tabela.

Três tabelas são unidas para obter os dados necessários; mais uma vez, usei pseudônimos. Há duas condições na cláusula WHERE, uma que define a data e a outra que define o país. Finalmente, o resultado é agrupado por data e nome do país.

Execute a consulta para obter esta tabela:

O primeiro valor é NULL porque não há linhas antes da primeira, ou seja, a primeira linha não tem uma linha com a qual possa ser comparada.

Mais informações sobre a função LAG() podem ser encontradas neste artigo sobre o cálculo da diferença entre duas fileiras.

Se você quiser praticar as funções de janela, experimente nosso curso SQL Funções de Janela (Window Functions) em SQL .

5: Adicionar Múltiplos Níveis de Agrupamento

O relatório que lhes mostrei no primeiro exemplo é bastante bom, mas isso não significa que não possa ser melhor. O que me falta ali é, por exemplo, um subtotal de contagem para 2019 e 2020 e um total geral - algo como tabelas pivot em Excel. Este relatório pode ser facilmente melhorado com o uso da função ROLLUP(). Vamos adicionar alguns subtotais e mostrar todos os valores em um nível de tipo de cartão, também. Veja como fazer isso:

SELECT	EXTRACT(YEAR FROM ct.date) AS year,
		EXTRACT(QUARTER FROM ct.date) AS quarter,
		COUNT(ct.amount) AS number_of_transactions,
		cty.card_type_name
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
GROUP BY ROLLUP(EXTRACT(YEAR FROM ct.date), EXTRACT(QUARTER FROM ct.date), cty.card_type_name);

A primeira parte do código é tirada diretamente do primeiro exemplo. Ela usa a função EXTRACT() para classificar as datas em anos e trimestres; depois conta o número de transações usando a função COUNT(). A última coluna que o código selecionará é card_type_name da tabela card_type.

Os dados são selecionados a partir das tabelas card_transaction, card_numbere card_typeque são unidas. Agora vem a parte mágica - usando ROLLUP(), que é uma extensão da cláusula GROUP BY. Depois de escrever GROUP BY no código, você simplesmente usa a função ROLLUP() para especificar múltiplos níveis de agrupamento em seu relatório. Como sua tarefa é agrupar dados em um nível anual, trimestral e tipo de cartão, essas colunas devem estar no ROLLUP(). Execute o código acima e você receberá este belo relatório:

Não deixe que os valores NULL o assustem! Tudo está bem com o relatório; os valores NULL aparecem quando há um subtotal, total ou grande total. Aqui está a primeira parte da tabela:

Esta tabela mostra o número de transações para o primeiro trimestre de 2019 por tipo de cartão. O subtotal de todas as transações do primeiro trimestre é 49 + 46 +36 = 131. A lógica é a mesma para o resto da tabela. Assim, por exemplo, quando você vê uma linha como a abaixo, significa que o número total de transações para o ano de 2019 é de 500:

ROLLUP() e outras extensões GROUP BY são legais; eu gosto muito delas! Se você quiser ver como elas podem ser úteis, então nosso curso GROUP BY Extensões é o ideal para você.

Vamos agora ao nosso último exemplo de consultas de análise de dados financeiros SQL.

6: Criar um Relatório de Receita em um nível anual

Esta será provavelmente a consulta mais complexa que lhe mostrarei, mas acho que valerá a pena. Aprender o que esta consulta faz lhe permitirá criar relatórios complexos e realizar análises estatísticas, ambas muito freqüentemente necessárias no setor financeiro.

Sua tarefa é criar um relatório que mostrará a receita de 2020, a receita de 2019 e a receita total. Todas as três categorias têm que ser exibidas no nível do cliente. Lembre-se, você está trabalhando para uma empresa de processamento de cartões de crédito, portanto a receita é a mesma que o valor da transação. Você também precisa atribuir categorias ao cliente; se o cliente trouxe $1.000.000 ou mais em receita total, elas são categorizadas como 'Platina'. Se a receita total for inferior a US$1.000.000, o cliente deve ser classificado como 'Ouro'. Aqui está a pergunta:

SELECT	cu.NIN,
		cu.first_name,
		cu.last_name,
		SUM(ct.amount) AS total_revenue_per_customer,
		CASE
			WHEN SUM(ct.amount) >= 1000000 THEN 'Platinum'
			WHEN SUM(ct.amount) < 1000000 THEN 'Gold'
		END AS customer_category,
		SUM(CASE WHEN ct.date >= '2019-01-01' AND ct.date < '2020-01-01' THEN ct.amount ELSE 0 END) AS revenue_2019,
		SUM(CASE WHEN ct.date >= '2020-01-01' AND ct.date < '2021-01-01' THEN ct.amount ELSE 0 END) AS revenue_2020
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN customer cu ON cn.customer_id = cu.id
GROUP BY cu.NIN, cu.first_name, cu.last_name
ORDER BY total_revenue_per_customer DESC;

Vamos começar com a parte mais fácil: a consulta seleciona as colunas NIN, first_name, e last_name da tabela customer. Em seguida, ele soma os valores, que serão a receita total. Depois disso, segue-se a categorização dos clientes usando uma declaração CASE WHEN. O primeiro WHEN atribui a categoria 'Platina', enquanto o outro atribui a categoria 'Ouro'. Estas declarações são fechadas por END, e esta nova coluna será denominada customer_category.

Em seguida, tive que especificar condições para as colunas que conterão os números da receita de 2019 e da receita de 2020. Para fazer isso, usei novamente a declaração CASE WHEN. Para a receita de 2019, a condição é que as datas devem ser iguais a 2019-01-01 ou acima/novos, mas abaixo/ mais antigas que 2020-01-01. Esta coluna é denominada revenue_2019.

O mesmo princípio é aplicado ao criar a coluna revenue_2020.

Para obter os dados, é preciso juntar três tabelas: card_transaction, card_numbere customer. Todas as três tabelas têm pseudônimos associados a elas.

Ao final da consulta, os dados são agrupados pelas colunas NIN, first_name, e last_name, porque o usuário deseja os dados em nível de cliente. Além disso, o resultado é ordenado pela receita total em ordem decrescente para parecer mais agradável.

Aqui está a tabela sexy que o deixará legal entre os totós dos dados:

Como você gosta de usar SQL Avançado Consultas em Análise de Dados Financeiros?

Eu pretendia mostrar a você práticas SQL relativamente avançadas que lhe permitem analisar dados financeiros e criar relatórios. Isto não é tudo o que SQL pode fazer, mas talvez eu tenha lhe interessado em algumas características avançadas de SQL. Todos os seis exemplos foram baseados em minha experiência como analista de dados. Já fiz muitos desses relatórios em minha vida, e teria ficado encantado se soubesse todas essas possibilidades SQL quando comecei a trabalhar; teria sido muito mais fácil para mim.

Nunca é tarde demais para aprender SQL avançado, mas é sempre melhor começar mais cedo. Se você tiver a oportunidade, inscreva-se no curso SQL Avançado ; isso será recompensado no futuro.