Práctica SQL: 10 Ejercicios Prácticos GROUP BY con Soluciones Detalladas

¿Necesita practicar sus conocimientos de SQL? Estos 10 ejercicios de práctica GROUP BY - con explicaciones y soluciones - ¡son un gran comienzo!

GROUP BY GROUP BY es una poderosa cláusula SQL que le permite crear grupos de registros y luego calcular métricas de resumen (como promedios) para esos grupos. Sin embargo, GROUP BY suele ser difícil de dominar para los estudiantes de SQL. Sin embargo, practicar GROUP BY es muy importante si piensas utilizar SQL.

En este artículo, hemos recopilado varios ejercicios de práctica de GROUP BY para ayudarte a consolidar tu comprensión de cómo funciona esta cláusula.

Tabla de Contenidos

• Introducción
• Revisión de la Cláusula GROUP BY
• Ejercicios
  • Ejercicio 1: Ingresos Totales de Cada Compañía
  • Ejercicio 2: Juegos producidos al año con ingresos y costes medios
  • Ejercicio 3: Número de juegos rentables de cada tipo de juego
  • Ejercicio 4: Ingresos totales por tipo de juego en los sistemas PS2 y PS3
  • Ejercicio 5: Beneficio Bruto Total por Empresa
  • Ejercicio 6: Beneficio Bruto Anual por Empresa
  • Ejercicio 7: Diferencia entre los Beneficios Brutos Anuales
  • Ejercicio 8: Empresas que producen más de un juego
  • Ejercicio 9: Empresas que producen "buenos" juegos con ingresos superiores a 4 millones de euros
  • Ejercicio 10: Empresas Líderes por Tipo de Juego
  • Ejercicio 11: Continúe practicando sus habilidades SQL

Práctica de GROUP BY

Este artículo contiene 10 ejercicios GROUP BY que le ayudarán a practicar. Aprender SQL haciendo ejercicios es una de las mejores formas de perfeccionar tus habilidades.

Los ejercicios de este artículo provienen de nuestro Ejercicios prácticos de SQL. Tiene más de 80 ejercicios interactivos de SQL que cubren temas como consultas simples, JOINs, subconsultas, - y, por supuesto, GROUP BY. También ofrecemos otros conjuntos de prácticas, incluyendo:

• Básico Ejercicio de SQL: Una tienda
• Básico Ejercicio de SQL: Universidad
• Básico Ejercicio de SQL: Blog y Datos de Tráfico

Ahora que ya sabes qué recursos de práctica SQL están disponibles, vamos a profundizar en la cláusula GROUP BY. Si no sabes nada sobre GROUP BY, empieza leyendo GROUP BY en SQL Explicado y 5 Ejemplos de GROUP BY .

La Cláusula GROUP BY

En estos ejercicios prácticos de GROUP BY, utilizaremos una tabla llamada games que almacena información sobre videojuegos. Debemos enfatizar el hecho de que aunque los nombres de los juegos son reales, los otros campos de la tabla contienen datos que son completamente inventados. A continuación se muestra una vista parcial de la tabla:

Repasemos brevemente la cláusula GROUP BY. Esta cláusula nos permite crear grupos de registros y calcular diferentes métricas para cada grupo (como los valores medio, mínimo o máximo de cada conjunto). A continuación se muestra una consulta sencilla que utiliza GROUP BY para calcular el número de juegos producidos por cada empresa:

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

Esta consulta indica a la base de datos que cree grupos de filas de la tabla games que tengan el mismo valor en la columna company. (En la imagen de abajo, las filas con el mismo color están en el mismo grupo, es decir, han sido fabricadas por la misma empresa). A continuación, se utiliza la función COUNT(*) para contar la cantidad de filas de cada grupo; esto devuelve el número de juegos producidos por cada empresa.

Esta es la tabla con las filas codificadas por colores. Cada fila con el mismo color pertenece al mismo grupo:

Y aquí está el resultado:

Ejercicio 1: Ingresos totales de cada empresa

Ejercicio:

Obtener el nombre y los ingresos totales de cada empresa.

Solución:

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

Explicación:

En la consulta, utilizamos la cláusula GROUP BY empresa para crear grupos de filas con el mismo valor en la columna empresa. A continuación, se ejecuta la función SUM(revenue) para cada grupo de filas y se muestra el resultado junto con el nombre de la empresa.

Cada fila del resultado está asociada a un grupo de filas. Este es un punto importante cuando se utiliza GROUP BY: los detalles de las filas individuales se contraen en una fila por grupo, y los datos devueltos son sobre el grupo de filas.

Antes de pasar al siguiente ejercicio, me gustaría recomendarte el curso Práctica Mensual de SQL, donde puedes encontrar una fuente adicional de ejercicios de práctica de SQL. Publicamos un nuevo curso de práctica SQL cada mes.

Ejercicio 2: Juegos Producidos por Año con Ingreso y Costo Promedio

Ejercicio:

Generar un informe con el año de producción y el número de juegos lanzados este año (llamado recuento), la media del coste de producción de todos los juegos producidos en este año (llamado avg_cost) y los ingresos medios de ese año (llamado avg_revenue).

Solución:

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

Explicación:

Este ejercicio es similar al anterior. Aquí, utilizamos las funciones agregadas COUNT() y AVG() para calcular las métricas. Además, renombramos las columnas del informe para describir adecuadamente su contenido ( como recuento, avg_cost y avg_revenue). Para renombrar una columna, utilizamos la cláusula AS seguida del nombre que queremos asignar a la columna, por ejemplo

AVG(production_cost) AS avg_cost

Ejercicio 3: Número de partidas rentables de cada tipo de juego

Ejercicio:

Cuente cuántos juegos de un tipo determinado son rentables (es decir, los ingresos fueron superiores al coste de producción). Indique el tipo de juego y el número de juegos rentables (denominado number_of_games) para cada tipo.

Solución:

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

Explicación:

En este ejercicio, la condición WHERE se ejecuta en primer lugar. Determina qué juegos son rentables (tienen mayores ingresos que coste de producción). Después, GROUP BY agrupa las filas (juegos) del mismo type. Finalmente se aplica la función COUNT(*) a cada grupo de filas para obtener el número de juegos rentables de cada tipo.

Ejercicio 4: Ingresos totales por tipo de juego en los sistemas PS2 y PS3

Ejercicio:

Obtener el tipo de juegos y los ingresos totales generados por los juegos con una production_year posterior a 2010 y con una PS2 o PS3 system. Ordena el resultado de forma que los tipos con mayores ingresos aparezcan en primer lugar.

Solución:

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

Explicación:

Al igual que en el ejercicio anterior, primero filtramos las filas; esta vez, las condiciones son production_year > 2010 y system IN (‘PS2’, ‘PS3’). Una vez filtradas las filas, procedemos a crear grupos de filas utilizando la cláusula de tipo GROUP BY. Por último, aplicamos la función SUM(revenue) a cada grupo de filas para obtener el total de ingresos de dicho grupo.

Ejercicio 5: Beneficio Bruto Total por Empresa

Ejercicio:

Para todas las empresas presentes en la tabla, obtenga sus nombres y la suma del beneficio bruto de todos los años. (Supongamos que el beneficio bruto = ingresos - coste de producción). Nombra esta columna gross_profit_sum. Ordena los resultados por beneficio bruto, en orden descendente.

Solución:

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

Explicación:

En la consulta, utilizamos la expresión revenue - production_cost para calcular el beneficio bruto de cada juego. Si creamos grupos utilizando la columna empresa y luego sumamos el beneficio bruto de todos los juegos de esa empresa, obtenemos el beneficio bruto global de la empresa.

Observe la cláusula ORDER BY gross_profit_sum DESC. La palabra clave DESC indica orden descendente; en otras palabras, el valor más alto de gross_profit_sum aparece primero en el resultado.

Ejercicio 6: Beneficio Bruto Anual por Empresa

Ejercicio:

Obtener el beneficio bruto anual de cada empresa. Es decir, queremos un informe con el nombre de la empresa, el año y el beneficio bruto de ese año. Ordenar el informe por nombre de empresa y año.

Solución:

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

Explicación:

Como queremos obtener el beneficio bruto desglosado por año, tenemos que utilizar GROUP BY company, production_year clause. Esto crea un grupo de filas para cada par de valores company y production_year. Para cada grupo, calculamos la expresión SUM(revenue - production_cost); que es el beneficio bruto.

Ejercicio 7: Diferencia entre los beneficios brutos anuales

Ejercicio:

Generar un informe que muestre el beneficio bruto anual de cada empresa, el beneficio bruto del año anterior y la diferencia entre ambos años. Sugiero utilizar la consulta anterior como punto de partida.e

Solución:

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;

Explicación:

Esta consulta se basa en un CTE llamado company_gross_profit, que se crea utilizando la cláusula WITH en la primera parte de la consulta. A continuación, la consulta principal hace referencia a la tabla CTE company_gross_profit dos veces en una autounión; la tabla "izquierda" o "primera" se denomina cur (actual) y la otra prev (anterior). A continuación unimos dos filas de la misma empresa pero de años contiguos. La condición para hacerlo es

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

Por último, la columna profit_delta es un campo calculado. Se obtiene utilizando la diferencia entre el beneficio bruto del año en curso y el beneficio bruto del año anterior:

cur.gross_profit - prev.gross_profit AS profit_delta

Las CTEs, o expresiones comunes de tabla, son una característica avanzada de SQL. Recomiendo los artículos A Guide to SQL Common Table Expressions, Explicación de las CTEs de SQL con ejemplos y How to Learn SQL Common Table Expressions (CTEs) si quieres aprender más sobre las CTEs.

Ejercicio 8: Empresas que Producen Más de un Juego

Ejercicio:

Para cada empresa, seleccione su nombre, el número de juegos que ha producido (como columna number_of_games ), y el coste medio de producción (como columna avg_cost ). Muestra sólo las empresas que producen más de un juego.

Solución:

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

Explicación:

En esta consulta, utilizamos la cláusula HAVING en lugar de la cláusula WHERE. Mientras que WHERE filtra registros individuales, HAVING se utiliza para aplicar filtros a grupos de registros.

En esta consulta, queremos un informe que muestre sólo las empresas que produjeron más de un juego. Después de GROUP BY empresa, podemos utilizar la condición COUNT(*) > 1 para descartar las empresas que sólo tienen un juego.

En SQL, sólo puede utilizar directamente condiciones que impliquen funciones de agregación en la cláusula HAVING; en la cláusula WHERE, tendría que utilizar una subconsulta. Esto se debe a que WHERE se procesa antes que las funciones agregadas en el orden de operaciones de SQL.

Si tiene dudas sobre la cláusula HAVING, le recomiendo nuestro artículo La Cláusula HAVING de SQL Explicada, donde discutimos cómo usar esta cláusula en detalle.

Ejercicio 9: Empresas que producen juegos "buenos" con ingresos de más de 4 millones de dólares

Ejercicio:

Estamos interesados en los buenos juegos producidos entre 2000 y 2009. Un buen juego es un juego rentable con una puntuación superior a 6. Para cada empresa, muestre el nombre de la empresa, sus ingresos totales por juegos buenos producidos entre 2000 y 2009 (como la columna revenue_sum ), y el número de juegos buenos que produjo en este periodo (como la columna number_of_games ). Muestra sólo las empresas con ingresos por juegos buenos superiores a 4 000 000.

Solución:

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;

Explicación:

En la consulta, utilizamos GROUP BY empresa porque las métricas que queremos obtener (number_of_games y revenue_sum) son a nivel de empresa. Lo interesante de esta consulta son los filtros que aplicamos a los datos. Algunos de ellos están a nivel de fila y deben estar en la cláusula WHERE:

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

Sin embargo hay otro filtro a nivel de grupo, que debe ponerse en la columna HAVING:

• SUM(revenue) > 4000000

Ejercicio 10: Empresas Líderes por Tipo de Juego

Ejercicio:

Devolver una lista de aquellas empresas y tipos de juegos en los que la empresa es líder del mercado. Un líder de mercado para un tipo de juego es una empresa que tiene unos ingresos totales para ese tipo de juego que superan los ingresos totales de todas las demás empresas para ese tipo de juego.

Indique el nombre de la empresa, el tipo de juego y los ingresos totales de la empresa por ese tipo de juego.

Solución:

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

Explicación:

En primer lugar, observe la cláusula GROUP BY company, type. La utilizamos porque queremos los ingresos de la empresa para todos los juegos del mismo tipo.

Sin embargo, lo más interesante es el uso de una subconsulta en la cláusula HAVING. Queremos empresas con una suma de ingresos superior a los ingresos totales del resto de empresas; utilizamos una subconsulta para obtener los ingresos totales de las demás empresas..

La subconsulta hace referencia a las columnas g1.company y g1.type, que son las columnas empresa y tipo de la consulta externa. Estas referencias en la subconsulta nos permiten obtener los ingresos totales del resto de empresas para el mismo tipo de juego.

Ejercicio 11: Continúe practicando sus habilidades SQL

Hemos cubierto diez ejercicios de GROUP BY con diferentes niveles de complejidad, desde agregados simples hasta CTEs y subconsultas. Aprender GROUP BY utilizando ejercicios de práctica es uno de los mejores enfoques para dominar esta cláusula. El artículo TOP 9 SQL GROUP BY Interview Questions te guía a través de las preguntas más comunes durante una entrevista de trabajo SQL; esta es también una gran manera de practicar si no tienes una entrevista próximamente.

Nuestro paquete SQL Todo, para siempre es otra forma de practicar SQL, ¡y muy completa! Este paquete contiene todos nuestros más de 70 cursos actuales de SQL en cuatro dialectos de SQL diferentes (y acceso a los futuros cursos que creemos). Todos los cursos de nuestra plataforma son prácticos e interactivos. ¡Por lo tanto, elija su camino de práctica y comience a ser impresionante en el uso de GROUP BY!