6 Consultas SQL avanzadas para analizar datos financieros

¿Es usted un usuario avanzado de SQL que es nuevo en las finanzas? ¿Quiere aprender a utilizar sus conocimientos para analizar datos financieros? Este artículo le servirá de orientación.

Probablemente sepa que SQL es una potente herramienta para analizar todo tipo de datos. Cuanto más grandes y complejos sean los datos, más beneficioso será SQL. En ciertas situaciones, los datos financieros pueden ser muy complicados; producir un análisis sofisticado requiere herramientas sofisticadas. SQL es una de estas herramientas, y cuanto mejor conozca algunas prácticas avanzadas de SQL, más fácil será realizar análisis complejos de datos financieros y crear informes financieros. Voy a mostrarte algunos ejemplos de cómo hacerlo, extraídos de mi propia experiencia.

El nivel de conocimientos de SQL que se requiere en este artículo -como las funciones de ventana, las extensiones GROUP BY y las consultas recursivas- se cubre en el tema LearnSQL.es SQL avanzado .

Presentación de la base de datos

Los seis ejemplos utilizarán esta base de datos, que consta de cinco tablas:

• country
• card_type
• customer
• card_number
• card_transaction

Imaginemos que se trata de la base de datos de una empresa de procesamiento de tarjetas de crédito y que usted es su empleado cuando se trata de analizar datos financieros.

Ahora, entremos en los detalles de cada tabla.

La tabla country tiene los siguientes atributos:

• id - El ID del país y la clave primaria (PK) de esta tabla.
• country_name - El nombre del país.

La siguiente tabla es card_typecon estos atributos:

• id - El ID del tipo de tarjeta; la clave primaria (PK).
• card_type_name - El nombre del tipo de tarjeta.

La tabla customer consta de las columnas:

• id - El ID del cliente; la clave primaria (PK).
• NIN - El número de identificación nacional del cliente.
• first_name - El nombre del cliente.
• last_name - Apellido del cliente.
• country_id - El ID del país; se trata de una clave externa (FK) que hace referencia a la tabla country.

A continuación viene la tabla card_number con los atributos:

• id - El ID de la tarjeta; la clave primaria (PK).
• card_number - El número de tarjeta.
• customer_id - El ID del cliente; es una clave foránea (FK) que hace referencia a la tabla customer.
• card_type_id - El ID del tipo de tarjeta; una clave foránea (FK) que hace referencia a la tabla card_type.

La última es la tabla card_transaction tabla, que tiene estas columnas:

• id - El ID de la transacción; la clave primaria (PK).
• date - La fecha de la transacción.
• amount - El importe de la transacción, en dólares.
• card_number_id - El ID de la tarjeta; una clave foránea (FK) que hace referencia a la tabla card_number.

Ahora que está familiarizado con la base de datos, pasemos a los ejemplos.

6 Ejemplos de consultas en SQL avanzado para el análisis financiero

1: Agrupar datos por año y trimestre

Los datos financieros generalmente necesitan ser agrupados en períodos de tiempo específicos o cubos de tiempo. Seguramente necesitará agrupar los datos en años y trimestres si está creando informes financieros. Te voy a mostrar cómo hacerlo. Aprender este principio te permitirá agrupar los datos en cualquier otro nivel (es decir, meses, semanas o días, dependiendo de los datos que tengas).

En este ejemplo, agruparé los datos por años y trimestres y mostraré el número de transacciones. Para ello, utilizaré la tabla card_transaction y 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);

El código utiliza la función EXTRACT() para obtener los años y los trimestres. Esta es una función SQL estándar, que funcionará en MySQL y PostgreSQL. Sin embargo, si estás usando SQL Server, tendrás que usar una función diferente - explicaré cuál en un momento.

Cuando usas la función EXTRACT(), se requiere que especifiques el periodo que quieres y la columna que esta función usará para devolver el periodo deseado. Primero, quiero obtener los años de la columna de la fecha. Luego quiero los trimestres, también de la columna de la fecha. Después, tengo que contar el número de transacciones, lo que hice utilizando la función COUNT(). Los datos se agrupan mediante las funciones EXTRACT(), que representan los años y los trimestres. Por último, los datos se ordenan por años y trimestres de forma ascendente, ya que quiero ver el primer trimestre del primer año en la parte superior del informe. Seguro que quieres ver el resultado:

Este informe tiene un aspecto bastante bonito, debo decir.

Ahora, si estás usando SQL Server, tendrás que usar la función DATEPART() en lugar de EXTRACT(). Aquí está la primera parte del código, sólo para asegurarte de que lo entiendes. Sigue el mismo principio en el resto del código:

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

2: Calcular los Totales Actuales

Su tarea ahora es mostrar los totales corrientes de todas las transacciones realizadas en diciembre de 2020. El informe tiene que ser agregado a nivel de tipo de tarjeta. Para crear este informe, tendrá que introducir funciones de ventana y JOINs en el código.

Antes de seguir adelante, tal vez debería comprobar sus conocimientos de SQL. Puede hacerlo en la pista de Informes SQL. Esta pista también le enseñará a crear informes SQL básicos y a realizar análisis de tendencias de ingresos y de comportamiento de los clientes.

El código que devolverá los datos deseados es:

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;

El código primero selecciona la fecha específica, ya que puede haber múltiples transacciones diarias por el mismo tipo de tarjeta, incluso el mismo número de tarjeta. Luego sigue la columna card_type_name. La última columna de la consulta es transaction_running_total. Para calcular el total acumulado, he utilizado una función de ventana. En primer lugar, se suman los datos de la columna importe. A continuación, mediante la cláusula OVER(), he especificado que el total acumulado debe calcularse a nivel de tipo de tarjeta; por tanto, PARTITION BY cty.card_type_name. Por último, quiero que el total acumulado se calcule desde la fecha más antigua a la más reciente: ORDER BY date ASC.

Para obtener los datos, tuve que unir tres tablas. La primera unión conecta las tablas card_transaction y card_number tablas. La segunda unión hace referencia a la tabla card_typeque es como obtengo el nombre del tipo de tarjeta en mi informe. He asignado alias a todas las tablas unidas; de este modo, he tenido que escribir menos. El resultado se filtra mediante la cláusula WHERE, que me permitirá obtener sólo las transacciones de diciembre de 2020. He decidido ordenar los datos por el nombre del tipo de tarjeta en forma ascendente.

Al ejecutar el código se obtendrá el informe que se muestra a continuación:

Si quiere saber más sobre los totales acumulados en SQL, lea este artículo para aprender qué son los totales corridos y cómo calcularlos.

3: Calcular promedios corridos

El siguiente ejemplo consiste en crear un informe que mostrará todas las transacciones de diciembre de 2020 y su importe para las tarjetas Visa Electron. Además, mostrará el importe medio de las transacciones diarias utilizando una media móvil. Eche un vistazo al 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;

Primero he seleccionado las columnas que mostrarán la fecha de la transacción y el nombre del tipo de tarjeta. Después he sumado los importes de las transacciones diarias, y el resultado se muestra en la columna daily_sum. He tenido que hacer esto porque puede haber varias transacciones realizadas diariamente por Visa Electron. Luego he utilizado esta suma diaria de los valores de las transacciones para calcular su media. Sin embargo, para obtener la media móvil, tengo que utilizar la cláusula OVER(). Quiero que la media móvil se calcule desde el primer hasta el último día de diciembre de 2020, por lo que la operación se ordena por fecha de forma ascendente.

Para calcular las medias móviles, he decidido utilizar una media móvil de tres días, definida por la siguiente parte del código: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Esto indica al código que utilice la fila actual y las dos filas anteriores (tres filas/tres fechas en total) para calcular la media móvil.

Las tablas se unen de la misma manera que en el ejemplo anterior. Hay dos condiciones en la cláusula WHERE; una define las fechas que se tienen en cuenta; la otra define el tipo de tarjeta.

Esta consulta le dará la siguiente tabla:

Si no está familiarizado con las medias móviles, permítame explicarle cómo funcionan. Ya sabe que se trata de una media móvil de tres días, que utiliza tres filas para calcular la media.

Para la fecha "2020-12-01", la media en la tabla anterior es la misma que la suma diaria. Esto se debe a que la consulta sólo tiene los datos de la fila actual para utilizarlos, no hay filas anteriores.

Para la fecha "2020-12-03", la media móvil se calcula como sigue (16,881.70 + 17,375.79) / 2 = 17,128.75. Tenga cuidado con esto. La media móvil no se calcula de la siguiente manera: (16.881,70 + 17.375,79) / 3 = 11.419,16. Eso es porque sólo hay una fila anterior, o sólo dos valores para promediar.

La media móvil para la fecha "2020-12-13" se calcula de la siguiente manera: (16.881,70 + 17.375,79 + 17.725,49) / 3 = 17.327,66.

4: Análisis de series temporales

Un requisito muy común cuando se trabaja con datos financieros es analizar una serie temporal (es decir, la diferencia entre períodos de tiempo, como día a día o mes a mes). Por ejemplo, suponga que su jefe le ha dicho que cree un informe que muestre los cambios diarios de los valores de las transacciones realizadas por los clientes de China durante diciembre de 2020. El departamento de ventas en China no está contento con el rendimiento de diciembre de 2020, por lo que quiere analizar este mes en detalle para descubrir dónde hubo una caída en las transacciones.

Para crear dicho informe, volverá a necesitar una función de ventana SQL. Esta vez será la función LAG(), que le permitirá obtener los datos de la fila anterior. Así es como debes hacerlo:

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;

La consulta comienza con un proceso familiar: selecciona la fecha, luego calcula la suma diaria de las transacciones (en caso de que haya varias transacciones diarias de China). Para calcular la diferencia diaria, tiene que deducir la suma de transacciones del día anterior de la suma del día actual. Este cálculo se realiza en esta parte de la consulta:

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

La suma de las transacciones del día anterior es devuelta por la función LAG() combinada con la cláusula OVER(). La operación debe realizarse desde la fecha más antigua a la más reciente, que especificamos en ORDER BY ct.date ASC. La última columna de la consulta es la columna country_name de la tabla país.

Se unen tres tablas para obtener los datos requeridos; una vez más he utilizado alias. En la cláusula WHERE hay dos condiciones, una que define la fecha y otra que define el país. Por último, el resultado se agrupa por fecha y nombre del país.

Ejecuta la consulta para obtener esta tabla:

El primer valor es NULL porque no hay filas antes de la primera, es decir, la primera fila no tiene una fila con la que se pueda comparar.

Puede encontrar más información sobre la función LAG() en este artículo sobre el cálculo de la diferencia entre dos filas.

Si quieres practicar las funciones de ventana, prueba nuestro curso de SQL Funciones de ventana .

5: Añadir varios niveles de agrupación

El informe que te mostré en el primer ejemplo es bastante bueno, pero eso no significa que no pueda ser mejor. Lo que me falta ahí es, por ejemplo, un subtotal de conteo para 2019 y 2020 y un total general - algo así como las tablas dinámicas en Excel. Este informe puede mejorarse fácilmente utilizando la función ROLLUP(). Añadamos algunos subtotales y mostremos también todos los valores a nivel de tipo de tarjeta. A continuación se explica cómo hacerlo:

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);

La primera parte del código está tomada directamente del primer ejemplo. Utiliza la función EXTRACT() para categorizar las fechas en años y trimestres; luego cuenta el número de transacciones utilizando la función COUNT(). La última columna que el código seleccionará es card_type_name de la tabla card_type.

Los datos se seleccionan de las tablas card_transaction, card_numbery card_typeque están unidas. Ahora viene la parte mágica - usar ROLLUP(), que es una extensión de la cláusula GROUP BY. Después de escribir GROUP BY en el código, simplemente se utiliza la función ROLLUP() para especificar múltiples niveles de agrupación en su informe. Dado que su tarea es agrupar los datos a nivel anual, trimestral y de tipo de tarjeta, esas columnas deben estar en el ROLLUP(). Ejecute el código anterior y obtendrá este bonito informe:

¡No dejes que los valores NULL te asusten! Todo está bien en el informe; los valores NULL aparecen cuando hay un subtotal, un total o un gran total. Aquí está la primera parte de la tabla:

Esta tabla muestra el número de transacciones del primer trimestre de 2019 por tipo de tarjeta. El subtotal de todas las transacciones del primer trimestre es 49 + 46 +36 = 131. La lógica es la misma para el resto de la tabla. Así, por ejemplo, cuando veas una fila como la siguiente, significa que el número total de transacciones para el año 2019 es de 500:

ROLLUP() y otras extensiones de GROUP BY son geniales; ¡me gustan mucho! Si quieres ver lo útiles que pueden ser, entonces nuestro curso de Extensiones GROUP BY es el adecuado para ti.

Pasemos ahora a nuestro último ejemplo de consultas de análisis de datos financieros en SQL.

6: Crear un Informe de Ingresos a Nivel Anual

Esta será probablemente la consulta más compleja que le mostraré, pero creo que valdrá la pena. Aprender lo que hace esta consulta le permitirá crear informes complejos y realizar análisis estadísticos, ambos requeridos muy a menudo en la industria financiera.

Tu tarea consiste en crear un informe que muestre los ingresos de 2020, los ingresos de 2019 y los ingresos totales. Las tres categorías deben mostrarse a nivel de cliente. Recuerde que está trabajando para una empresa de procesamiento de tarjetas de crédito, por lo que los ingresos son lo mismo que el importe de la transacción. También tiene que asignar categorías al cliente; si el cliente ha aportado 1.000.000 de dólares o más en ingresos totales, se le categoriza como "Platino". Si los ingresos totales son inferiores a 1.000.000 de dólares, el cliente debe ser clasificado como 'Oro'. Esta es la consulta:

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;

Empecemos por la parte más fácil: la consulta selecciona las columnas NIN, first_name, y last_name de la tabla customer. Luego suma los importes, que serán los ingresos totales. Después sigue la categorización de los clientes mediante una sentencia CASE WHEN. La primera WHEN asigna la categoría "Platino", mientras que la otra asigna la categoría "Oro". Estas sentencias se cierran con END, y esta nueva columna se llamará customer_category.

A continuación, tuve que especificar las condiciones para las columnas que contendrán los números de los ingresos de 2019 y los ingresos de 2020. Para ello, volví a utilizar la sentencia CASE WHEN. Para los ingresos de 2019, la condición es que las fechas deben ser iguales a 2019-01-01 o superiores/nuevas, pero inferiores/anteriores a 2020-01-01. Esta columna se llama revenue_2019.

El mismo principio se aplica al crear la columna revenue_2020.

Para obtener los datos, hay que unir tres tablas: card_transaction, card_number, y customer. Las tres tablas tienen alias asociados.

Al final de la consulta, los datos se agrupan por las columnas NIN, first_name, y last_name porque se quieren los datos a nivel de cliente. Además, el resultado se ordena por el total de ingresos en orden descendente para que se vea mejor.

Esta es la tabla más sexy que te hará sentir bien entre los fanáticos de los datos:

¿Qué le parece el uso de SQL avanzado Queries en el análisis de datos financieros?

Mi intención es mostrarte prácticas relativamente avanzadas de SQL que te permitan analizar datos financieros y crear informes. Esto no es todo lo que SQL puede hacer, pero tal vez conseguí que te interesaras por algunas funciones avanzadas de SQL. Los seis ejemplos se basan en mi experiencia como analista de datos. He hecho muchos informes de este tipo en mi vida, y me habría encantado conocer todas esas posibilidades de SQL cuando empecé a trabajar; me habría resultado mucho más fácil.

Nunca es demasiado tarde para aprender SQL avanzado, pero siempre es mejor empezar antes. Si tienes la oportunidad, inscríbete en el curso SQL avanzado ; te compensará en el futuro.