Aún no tienes acceso a esta clase

Crea una cuenta y continúa viendo este curso

Curso Práctico de SQL

Curso Práctico de SQL

Israel Vázquez Morales

Israel Vázquez Morales

Egoísta (selfish)

18/29
Recursos

Aportes 79

Preguntas 8

Ordenar por:

¿Quieres ver más aportes, preguntas y respuestas de la comunidad? Crea una cuenta o inicia sesión.

solución reto:

SELECT AVG(alumnos_por_tutor) AS promedio_alumnos
FROM(
SELECT CONCAT(t.nombre,' ',	t.apellido) AS tutor,
		COUNT(*)   AS alumnos_por_tutor
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id=t.id

GROUP BY tutor	
ORDER BY alumnos_por_tutor DESC
) AS promedio
--Experimento-- 
--promedio de tutores por carrera--
SELECT AVG(dc.tutor_por_carrera)
FROM(
	-- seleciona cuantas tutorias realiza en una carrera--
SELECT 	CONCAT(t.nombre,' ',	t.apellido) AS tutor,
		COUNT(*) AS tutor_por_carrera,
		a.carrera_id
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id=t.id 
			AND a.carrera_id=t.tutor_id
GROUP BY tutor, a.carrera_id	
ORDER BY tutor_por_carrera DESC) AS dc

Si no estoy mal, al hacer:

INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id

también estamos asumiendo que todos los 30 tutores son alumnos, pero no todos los alumnos son tutores ¿No?


-- Promedio General de alumnos por tutor 
SELECT AVG(n_alumnos) AS promedio_alumnos
FROM (
     SELECT COUNT(*) AS n_alumnos FROM alumnos
     GROUP BY tutor_id
         ) AS tabla_num_alumnos_por_tutor;

Lo que hace este query es asumir que el tutor con el tutor_id X de cada alumno es otro alumno con el mismo id, por eso te muestra los nombres y apellidos de profesores cuando en la table esta información no está, solo tenemos los nombres y apellidos de alumnos. Saludos

SELECT	a.nombre,
		a.apellido,
		t.nombre,
		t.apellido
FROM	platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id;

Mi respuesta el ejercicio de promedio

SELECT round((SUM(qq.alumnos_por_tutor) / COUNT(tutor)),0) as promedio
FROM (
	SELECT CONCAT(t1.nombre, ' ', t1.apellido ) AS tutor,
		   COUNT(*) AS alumnos_por_tutor
		   
	FROM platzi.alumnos AS t0
	INNER JOIN platzi.alumnos AS t1 ON t0.tutor_id = t1.id
	GROUP BY tutor
) QQ;```
  SELECT AVG(alumnos_por_tutor) as promedio
FROM (
	SELECT CONCAT(t.nombre,' ',	t.apellido) AS tutor, COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
	JOIN 
		 platzi.alumnos AS t 
	ON a.tutor_id = t.id
	GROUP BY tutor
) As promedio

SELF JOINS

  • Personalmente considero que a la hora de hacer self-joins es de especial importancia más que en otros casos el hecho de comentar el código
  • En este caso aclararía en el código que recurrí a esta técnica, ya que los tutores a la vez son alumnos, de ahí que puedo obtener toda la información de la propia tabla

RETO: Promedio de alumnos por tutor

Mi solución del reto (postgreSQL):

SELECT AVG(cant_alumnos) AS promedio_alumnos_por_tutor
FROM(
SELECT CONCAT(t.nombre,' ', t.apellido) AS tutor, COUNT(*) cant_alumnos
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t
	ON a.tutor_id=t.id
GROUP BY tutor
	) AS alumnos_por_tutor

Me costó mucho entenderlo, y tuve que ver algunos comentarios para este reto. Al final resultó de esta forma:

SELECT AVG(conteo.alumnos_por_tutor) AS promedio_alumnos
FROM(SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor, 
	COUNT(*) AS alumnos_por_tutor 
	FROM platzi.alumnos AS a INNER JOIN platzi.alumnos AS t ON (a.tutor_id = t.id)
	GROUP BY tutor) AS conteo
;
SELECT tutor_id, MIN(nombre)
FROM platzi.alumnos
GROUP BY tutor_id;

Esta fue la solución al reto propuesto y un reto personal adicional:

/* RETO: Sacar el promedio de alumnos por tutor */

SELECT AVG(test)
FROM(
	SELECT CONCAT (al.nombre,' ', al.apellido) AS tutor,  COUNT(*) AS test
	FROM platzi.alumnos as al
	INNER JOIN platzi.alumnos as ab
	ON al.id =ab.tutor_id
	GROUP BY tutor
	
)AS test

/*Reto Personal: Promedio de tutorores por carrera*/

SELECT carrera, AVG(cantidad_tutorias_carrera) AS t
FROM (
	SELECT CONCAT(a1.carrera_id) AS carrera, COUNT(*) AS cantidad_tutorias_carrera
	FROM platzi.alumnos AS a1
	INNER JOIN platzi.alumnos AS a2
	ON a1.id = a2.tutor_id
	GROUP BY carrera
	ORDER BY carrera ASC
) AS calculo
GROUP BY carrera
ORDER BY t ASC;

Podemos agregarle la carrera de la tutoría de la siguiente manera:

FROM(
SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor,
	COUNT(*) AS tutorias_por_carrera, c.carrera
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t
	ON a.tutor_id = t.id-----relacion del id del alumno con el id del tutor, un SELF JOIN
	INNER JOIN platzi.carreras AS c
	ON c.id = t.tutor_id
--WHERE c.vigente = true
GROUP BY tutor, c.carrera
ORDER BY tutorias_por_carrera DESC;

Comparto el reto

SELECT tutor_id, Min(nombre)
FROM platzi.alumnos
GROUP BY tutor_id
ORDER BY tutor_id;

Solución al reto:

  1. El mínimo del nombre en toda la tabla:
SELECT nombre
FROM platzi.alumnos
GROUP BY nombre
ORDER BY nombre
LIMIT 1;
  1. EL mínimo del nombre por tutor_id:
SELECT MIN(nombre), tutor_id
FROM platzi.alumnos
GROUP BY tutor_id
ORDER BY tutor_id;
from(SELECT count(*) as alumnos_por_tutor 
  FROM alumnos as a_
      INNER join alumnos as t_
      on a_.tutor_id = t_.id
  GROUP by t_.id)as est_por_tut;´´´

El reto del promedio de alumnos:

SELECT AVG(alumnos_por_tutor) as promedio_de_alumnos
FROM (
SELECT 	CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		COUNT(*) AS alumnos_por_tutor
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
GROUP BY tutor) AS numero_alumnos_tutor;

Desafío:

-- Reto: promedio general de alumnos por tutor

SELECT AVG(alumnos_por_tutor)
FROM (
	SELECT CONCAT( t.nombre, ' ', t.apellido) AS tutor,
			COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS T 
		ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumnos_por_tutor DESC
) AS tabla_alumnos_por_tutor;

Solución al reto de la clase pasada

-- el mínimo por tabla
select *
from platzi.alumnos
order by nombre asc
limit 1;

-- el mínimo por tutor id
select tutor_id, min(nombre)
from platzi.alumnos
group by tutor_id
order by tutor_id;
SELECT AVG(alumnos_por_tutor)
FROM(

SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor, COUNT(*)  AS alumnos_por_tutor
FROM platzi.alumnos AS a
inner join platzi.alumnos as t ON a.tutor_id=t.id
GROUP BY tutor
order by alumnos_por_tutor
	) AS promedio

Solución al reto:

SELECT AVG(alumnos_por_tutor) AS promedio_tutorias
FROM (
	SELECT CONCAT (t.nombre, ' ', t.apellido) AS tutor,
	COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t
		ON a.tutor_id = t.id
	GROUP BY tutor
	) AS promedio;

Reto de la clase, encontrar el promedio de alumnos por tutores

¿Cuántos alumnos tiene cada tutor?

Selfjoin y CONCAT para unir campos

Introducción a selfjoins, alumnos y tutores

Solucion de la tarea encontrar valores mínimos

Solución al reto:

SELECT AVG(alumnos_por_tutor) AS promedio_alumnos_por_tutor
FROM (
	SELECT  CONCAT(t.nombre,' ',t.apellido) AS tutor,
			COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t
		ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumnos_por_tutor DESC
) AS tutor_alumnos;

Resultado:
33.33333333

Mi solución al reto, no se si sea muy deportivo pero fue como lo pensé:🧓🏽

SELECT COUNT(id) / COUNT(DISTINCT(tutor_id)) AS promedio_alumnosXtutor
FROM platzi.alumnos

Reto 7:

SELECT AVG(total_alumnos) AS promedio_alumnos_por_tutor
FROM (SELECT	CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		COUNT(*) AS total_alumnos
FROM	platzi.alumnos AS a
	INNER JOIN  platzi.alumnos AS t ON a.tutor_id = t.id
GROUP BY tutor
ORDER BY total_alumnos DESC
) AS promedio;

RETO

Así quedó

-- El promedio de alumnos por tutor --
SELECT AVG(tutores.alumnos_por_tutor)
FROM (
	SELECT	CONCAT(t.nombre, ' ', t.apellido) AS tutor,
			COUNT(*) AS alumnos_por_tutor
	FROM	platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumnos_por_tutor DESC
) AS tutores;

select avg(prom) as promedio
from (
select count(*) as prom
from platzi.alumnos
group by tutor_id
) tabla_prom

MI solución del reto

SELECT AVG(alumnos_por_tutor) as promedio
FROM (SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor,
			COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
	GROUP BY tutor 
	ORDER BY alumnos_por_tutor DESC
	) as tabled
SELECT COUNT (DISTINCT id) / COUNT (DISTINCT tutor_id)
FROM platzi.alumnos

Comparo mis respuestas para el reto de la clase anterior:
Primer reto

SELECT MIN(nombre)
FROM platzi.alumnos

Segundo reto

SELECT tutor_id, MIN(nombre)
FROM platzi.alumnos
GROUP BY 1

Reto #9

SELECT AVG(alumnos_por_tutor)
FROM (
SELECT	CONCAT(t.nombre,  ' ', t.apellido) AS tutor,
		COUNT(*) AS alumnos_por_tutor
FROM 	platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t
		ON a.tutor_id = t.id
GROUP BY tutor) AS tabla_almunos_por_tutor;

Solución al reto:

SELECT AVG (alumnos_por_tutor) AS promedio_alumnos
FROM(
	SELECT	CONCAT(b.nombre, ' ', b.apellido) AS tutor,
	COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS b ON a.tutor_id = b.id
	GROUP BY tutor)
	AS promedio_alumnos;
SELECT avg(alumnos_por_tutor) promedio
FROM (
	SELECT CONCAT (t.nombre, ' ',t.apellido) AS tutor,
		   COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos as t ON a.tutor_id = t.id
	GROUP BY tutor
) datos

Mi solución al reto:

SELECT AVG(alumnos_count)
FROM (
    SELECT 
        COUNT(id) AS alumnos_count
    FROM platzi.alumnos
    GROUP BY tutor_id
    ORDER BY tutor_id
) AS alumnos_count;

Mi query 😃

SELECT ROUND(AVG(alumnos_por_tutor))AS promedio_alumnos_por_tutor
FROM(
	SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor,
       	   COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
	GROUP BY tutor
) AS tabla_alumnos_tutor

Reto

<code> 

SELECT AVG(conteo_alumnos)
FROM( 
	SELECT CONCAT (t.nombre,' ',t.apellido) AS tutor,
	   COUNT(*) AS conteo_alumnos
	FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.tutor_id
	GROUP BY tutor
	ORDER BY conteo_alumnos DESC
) AS resultado;
select avg(cuenta.cuenta) as promedio_general
from(
select concat(t2.nombre,' ',t2.apellido) as tutor,count(0) as cuenta
from platzi.alumnos t1
inner join platzi.alumnos t2
on t1.tutor_id=t2.id
group by tutor
order by 2 desc)cuenta;
SELECT tutor_id,MIN(nombre), carrera_id FROM alumnos GROUP by tutor_id;

solucion del reto

SELECT nombre,MIN(fecha_incorporacion), carrera_id FROM alumnos GROUP by nombre ORDER by fecha_incorporacion;

Hola, les comparto la solución que propongo al reto.
Saludos!

/*
Solucion del reto 
*/

WITH u AS (
	SELECT
		concat(t.nombre, ' ', t.apellido) tutor,
		count(*) alumnos_x_tutor
	FROM
		alumnos a
	INNER JOIN alumnos t ON
		a.tutor_id = t.id
	GROUP BY
		1
	ORDER BY
		2 DESC
)
SELECT
	trunc(avg(u.alumnos_x_tutor), 2) promedio
FROM
	u;

solución

<
SELECT AVG(alumnos_por_tutor) AS promedio_alumnos_por_tutor
FROM 
(
SELECT CONCAT(t.nombre,' ',t.apellido) AS tutor,
	   COUNT(*) AS alumnos_por_tutor
FROM platzi.alumnos AS a
INNER JOIN platzi.alumnos AS t 
ON a.tutor_id=t.id
GROUP BY tutor
ORDER BY alumnos_por_tutor DESC
) AS tblAlumnoTutor;

> 

mi reto

--reto promedio de los alumnos por tutor agrupado por tutor
SELECT AVG(alumnos_por_tutor) AS promedio_alumnos, tutor
FROM(
SELECT CONCAT(t.nombre,' ',t.apellido) AS tutor,
	   COUNT(*) AS alumnos_por_tutor
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
GROUP BY tutor
ORDER BY alumnos_por_tutor DESC) AS promedio
GROUP BY tutor
	
select count(*)/30 as alumnos_por_tutor
from  platzi.alumnos as a
	inner join platzi.alumnos as t on a.tutor_id = t.id

A partir del codigo mostrado en clase,no me queda claro como estos se estan uniendo

SELECT	CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		COUNT(*) AS alumnos_por_tutor
FROM	platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
GROUP BY tutor
ORDER BY alumnos_por_tutor DESC
LIMIT 5;```

Cantidad de alumnos promedio por tutor MySQL

SELECT AVG(cantidad) AS alumnos_promedio
FROM (
	SELECT 
		COUNT(A.id) AS cantidad, 
		CONCAT(IF(B.apellido IS NULL, "", B.apellido)," ", IF(B.nombre IS NULL, "", B.nombre)) AS tutor
	FROM platzi.alumnos AS A JOIN platzi.alumnos AS B ON A.tutor_id = B.id
	GROUP BY tutor
) AS C;

Sol:

select avg(tutores) promedio_tutores_po_alumno
from (select al.id id_alumnos, count(*) tutores 
	  from platzi.alumnos al 
	  inner join platzi.alumnos tu on al.id = tu.tutor_id
	  group by id_alumnos) tmp;

Solución rápida!

--Saber cuántos alumnos tiene cada tutor
--Crea una comlumna llamada 'alumnos_por_tutor'
SELECT 	CONCAT(pt.nombre, ' ', pt.apellido) AS tutor, 
		COUNT(*) AS alumnos_por_tutor
FROM platzi.alumnos AS pa
	INNER JOIN platzi.alumnos AS pt ON pa.tutor_id = pt.id
GROUP BY tutor
ORDER BY alumnos_por_tutor DESC;

--Copiar el código como una sub consulta
--Hacer el llamado a la columna 'alumnos_por_tutor'
--Implementar la función AVG
SELECT AVG(alumnos_por_tutor) AS promedio_alumnos_por_tutor
FROM (
	SELECT 	CONCAT(pt.nombre, ' ', pt.apellido) AS tutor, 
	COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS pa
		INNER JOIN platzi.alumnos AS pt ON pa.tutor_id = pt.id
	GROUP BY tutor
	ORDER BY alumnos_por_tutor DESC
	--La sentencia ORDER BY no es obligatoria
	--Pero sólo copié y pegué el código
) AS alumnos_totales_por_tutor
--NOTA: es obligatorio tener un AS en en sub query

mi respuesta

--promedio de alumnos por por tutor--
select avg(cantidad_alumnos)
from (
	select concat(t.nombre,' ',t.apellido) as tutor, count(a.*) as cantidad_alumnos
	  from platzi.alumnos as a join platzi.alumnos as t on a.tutor_id = t.id
	  group by tutor
	 ) as alumnos_por_tutor```
SELECT	nombre
FROM	platzi.alumnos
ORDER BY nombre ASC
LIMIT 1;

-- Funcion MIN --
SELECT	tutor_id, MIN(nombre)
FROM	platzi.alumnos
GROUP BY tutor_id
ORDER BY tutor_id);```

select tutor_id, min (nombre)
from platzi.alumnos
group by tutor_id
order by tutor_id

SELECT 
min([nombre])
FROM [HAHM].[dbo].[platzialumnos]

 


SELECT 
	tutor_id
	, min(nombre)
	FROM [HAHM].[dbo].[platzialumnos]
	GROUP BY tutor_id 
	ORDER BY tutor_id 
**SELECT COUNT**(nombre) **as** cantidad_de_tutores, **AVG** (cantidad_alumnos) **as** promedio_alumnosxtutor
**FROM** (
	**SELECT**  t.nombre, **COUNT(*)** **as** cantidad_alumnos 
	**FROM** platzi.alumnos as a
	**INNER JOIN** platzi.alumnos **as** t **ON** a.tutor_id = t.id
	**GROUP** by t.nombre
) **as** tutor_por_cantidad_de_alumos;

Solución:

SELECT
    AVG(alumnos_tutor) AS alum_prom
FROM (
    SELECT 
        concat(t.nombre,' ',t.apellido) as tutor
        ,COUNT(*) AS alumnos_tutor
    FROM alumnos AS a
        INNER JOIN alumnos AS t
            on a.tutor_id = t.id
    GROUP BY 1) AS prom;

Resultado:

 alum_prom      
---------------------
 33.3333333333333333
(1 row)

Ejercicio: Tabla con: el total de alumnos, promedio de alumnos por tutor y el número total de tutores.

SELECT SUM(alumnos_por_tutor) AS Total_Alumnos,
	   ROUND(AVG(alumnos_por_tutor),2) AS Promedio_Alumnos_por_Tutor,
	   COUNT(tutor) AS Total_Tutores
FROM
(
	SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		   COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
	GROUP BY tutor	
	ORDER BY alumnos_por_tutor DESC
) AS promedio_alumnos;

Solución al retor:

`SELECT AVG(alumnos_por_tutor.num_alum_por_tutor) AS promedio_alumnos_por_tutor
FROM (
	SELECT COUNT(t.id) AS num_alum_por_tutor
	FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
	GROUP BY t.id
) AS alumnos_por_tutor;

Reto

SELECT AVG(alumnos_por_tutor) AS promedio_alumnos_por_tutor
FROM (SELECT	CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		COUNT (*) AS alumnos_por_tutor
FROM	platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
GROUP BY tutor)
AS alumnos_tutor;```

Mi solución 😃

SELECT AVG(alumnos_x_tutor) AS promedio
FROM (SELECT COUNT(*) AS alumnos_x_tutor,
	   CONCAT(t.nombre,' ',t.apellido) AS tutor
	FROM platzi.alumnos a 
	INNER JOIN platzi.alumnos t ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumnos_x_tutor DESC) as agrupacion_alumnos_tutor 

Esta es mi solución:

SELECT AVG(alumnos_por_tutor) as prom
FROM (
	SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor, COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos as a
	INNER JOIN platzi.alumnos as t
		ON a.tutor_id = t.id
	GROUP BY tutor
) AS tutores

Solución al reto:

SELECT AVG(alumnos_por_tutor)
FROM (
	SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumnos_por_tutor DESC) AS alumnos_por_tutor;
SELECT AVG(alumnos_por_tutor)
FROM(
SELECT 	CONCAT(t.nombre,' ',t.apellido) AS tutor,
	   	COUNT(*) AS alumnos_por_tutor		   	   
FROM  platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t
	ON a.tutor_id=t.id
GROUP BY tutor
) AS table_tutor_alumnos;```

Me costó un poquito pero salió. Comparto mi humilde aporte

select round(avg(cantidad_alumnos))
from (
	select concat (t.nombre, ' ', t.apellido) as tutor, 
		count(*) as cantidad_alumnos
	from platzi.alumnos as a
		inner join platzi.alumnos as t
		on a.tutor_id = t.id
	group by tutor
	order by cantidad_alumnos desc
	 ) as promedio```

Mi solución al reto es la siguiente:

SELECT AVG (alumnos_por_tutor) AS promedio_alumnos_por_tutor
	FROM (
		SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		COUNT(*) AS alumnos_por_tutor
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
GROUP BY tutor
ORDER BY alumnos_por_tutor DESC) AS promedio;

Pero también quería ver cuantos alumnos había por carrera:


SELECT AVG (alumnos_por_carrera) AS promedio_alumnos_por_carrera
	FROM (
		SELECT CONCAT(t.nombre, ' ', t.apellido) AS carrera,
		COUNT(*) AS alumnos_por_carrera
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.carrera_id = t.id
GROUP BY carrera
ORDER BY alumnos_por_carrera DESC) AS promedio;

incluso abajo vi que un compañero hizo uno de promedio de tutores por carrera, muy interesante.

Creo que esta es la solución.

SELECT AVG(alumnos_por_tutor) AS prom
FROM (
	SELECT CONCAT(t.nombre, ' ', t.apellido) AS tutor,
		COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t
	ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumnos_por_tutor DESC
) AS self_tabla;
SELECT 	AVG(alumnos.alumnos_por_tutor)
FROM (
	SELECT  CONCAT(t.nombre,' ',t.apellido) AS tutor,
			COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t
		ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumnos_por_tutor DESC
) AS alumnos;
SELECT 
	CONCAT(t.nombre, ' ', t.apellido) AS tutor,
	AVG(a.colegiatura) AS promedio
FROM platzi.alumnos AS a
	INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
GROUP BY tutor
ORDER BY promedio DESC;

Creo que esta query no tiene mucho sentido, no?
Estamos asumiendo que cada alumno es tambien un tutor no es asi?

Reto de esta clase:

SELECT AVG(ALUMNOS_TUTOR) AS PROMEDIO_ALUMNOS
FROM(SELECT CONCAT(T.NOMBRE, ' ', T.APELLIDO) AS TUTOR,
		COUNT(*) AS ALUMNOS_TUTOR
FROM PLATZI.ALUMNOS A
INNER JOIN PLATZI.ALUMNOS T ON A.TUTOR_ID = T.ID
GROUP BY TUTOR
ORDER BY ALUMNOS_TUTOR DESC) AS NUMERO_ALUMNOS_TUTOR;

Mi respuesta al nuevo resto

SELECT tutor, AVG(alumnos_por_tutor)::int4 AS average
FROM (
	SELECT CONCAT(t.nombre,' ',t.apellido) AS tutor,
		COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t ON a.tutor_id = t.id
	GROUP BY tutor
) AS alumno_tutor
GROUP BY tutor
ORDER BY average;
SELECT AVG(alumno_por_tutor)
FROM (
	SELECT 	CONCAT(t.nombre,' ',t.apellido) AS tutor,
		COUNT(CONCAT(a.nombre,' ',a.apellido)) AS alumno_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t 
			ON a.tutor_id = t.id
	GROUP BY tutor
	ORDER BY alumno_por_tutor  DESC) AS promedios

No entiendo 😦

Solucion del reto

SELECT AVG(s.alumnos_for_tutor) AS mean_for_tutos 
    FROM
        (SELECT a.tutor_id AS tutor,
            COUNT(*) AS alumnos_for_tutor 
            FROM platzi.alumnos as a
            INNER JOIN platzi.alumnos as t ON a.tutor_id = t.id
            GROUP BY tutor) AS s;

Mi solución:

-- Reto: Promedio de alumnos por tutor

-- Solución: 

SELECT SUM(alumnos_por_tutor) / COUNT(tutor) FROM (
SELECT CONCAT(t.nombre,' ',t.apellido) AS tutor,
			COUNT(*) AS alumnos_por_tutor
	FROM platzi.alumnos AS a
		INNER JOIN platzi.alumnos AS t 
		ON a.tutor_id = t.id
	GROUP BY tutor
) AS alumnos_por_tutor_tabla

Mis repuestas de la clase anterior:

SELECT tutor_id,
MIN (nombre)
FROM platzi.alumnos
GROUP BY tutor_id
ORDER BY tutor_id DESC

SELECT MIN (nombre)
FROM platzi.alumnos

Promedio general de alumnos por tutor:

SELECT
	AVG(alumnos_por_tutor)
FROM
	(
	SELECT
		CONCAT(t.nombre, ' ', t.apellido) AS tutor, COUNT(*) AS alumnos_por_tutor
	FROM
		alumnos AS a
	INNER JOIN alumnos AS t ON
		a.tutor_id = t.id
	GROUP BY
		tutor) AS DATA