Playground: Agrupamiento y Ordenamiento de Datos

Una pequeña correccion: en la descripcion del problema, en el primer item, en vez de decir que la tabla se llama teachers (en plural) dice que se llama teacher (en singular)

Les dejo mi analisis y solucion:

• Tengo dos tablas:

  • courses: id, name, n_reviews y teacher_id
  • teachers: id y name.

  (De lo anterior sabemos que courses.teacher_id y teachers .id son lo mismo, por lo que haremos un INNER JOIN hacioendo coincidir estos dos valores)
  FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id

• Nos piden SELECCIONAR la SUMA total de reviews en todos los cursos por cada profesor. Es decir:

  • SELECT teachers. name, SUM(courses.n_reviews).
    En este punto, vale aclarar que nos piden nombrar estos puntos, por lo que:

  SELECT teachers.name AS teacher, SUM (courses.n_reviews) AS total_reviews

• Piden AGRUPAR por profesores para luego ORDENAR:
  GROUP BY courses.teacher_id

• ORDENAR de forma DESCENDENTE el total de reviews (total_reviews) que encontramos anteriormente:
  ORDER BY total_reviews DESC

• Por ultimo, las reviews deben ser mayores a 0. Lo hacemos con HAVING ya que es una coluimna que se genera despues:
  HAVING total_reviews > 1

Basandonos en lo anterior, organizando la informacion en el orden correcto (ver la clase anterior) la solucion a la que llegue seria:

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY courses.teacher_id
HAVING total_reviews > 1
ORDER BY total_reviews DESC;

SELECT
  teachers.name AS teacher,
  SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses
ON teachers.id = courses.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC;

Estan a otro nivel los retos, no pude 😦
Al menos los intenté.

Despues de media hora y dos dias de repaso me salio, lagrimas de felicidad :’)

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM courses
RIGHT JOIN teachers ON teachers.id = courses.teacher_id
WHERE teacher_id IS NOT NULL
GROUP BY teacher
ORDER BY total_reviews DESC
; 

Mi Solución
.
.
.
.

<SELECT teachers.name AS teacher,SUM(courses.n_reviews) AS total_reviews
FROM teachers 
inner join courses ON teachers.id = courses.teacher_id
GROUP BY courses.teacher_id
ORDER BY total_reviews DESC;
> 

Les comparto el código de la prueba con comentarios de los pasos para que lo puedan entender mejor 😃

Solución… 😄
.

.
.
.
.
.
.

SELECT t.name AS teacher, SUM(c.n_reviews) AS total_reviews
FROM teachers AS t
INNER JOIN courses AS c ON t.id = c.teacher_id
GROUP BY c.teacher_id
ORDER BY total_reviews DESC;

Mi técnica es ir de menos a más… es decir tomo la parte más sencilla del requerimiento y voy probando que funcione, así le voy agregando las funcionalidades más complejas del requerimiento hasta completarlo, de esta manera en todo momento estoy seguro hasta que punto el desarrollo está funcionando y voy solucionando cada problema o error que se va presentando!

Mi solucion
.
.
.
.
.
.
.
.
.
.

SELECT 
  teachers.name AS teacher,
  SUM(n_reviews) AS total_reviews
FROM teachers  
INNER JOIN courses
ON teachers.id = courses.teacher_id
GROUP BY teachers.name
ORDER BY total_reviews DESC;

Fue un reto realmente…
Espero lo intentes pero si te frusta por acá te dejo mi solución, la idea es que puedas analizarlo y no solo transcribir la respuesta.

Les dejo mi solución, me lanza como error pero me sale el mismo resultado.

<select 
t.name teacher,
sum(c.n_reviews) total_reviews
FROM teachers t
LEFT JOIN courses c ON t.id = c.teacher_id
WHERE c.teacher_id IS NOT NULL
GROUP BY t.name
ORDER BY total_reviews DESC;
> 

Los ejercicios en el playground deberían revisarse en la prueba por el resultado que arrojas, ya que si lo haces algo diferente me da error y luego al correr la solución que presentan en el ejercicio obtengo la misma respuesta que con el código que escribí, pero igual al correr la prueba me sale que hay errores.

Estos playground son de lo mejor que pudieron hacer en platzi!! te imaginas que tuvieran mas de un ejercicio? o un mega ejercicio complementario tipo entrevista ?
esta de locos !
Mi solución:

SELECT teachers.name AS teacher,SUM(courses.n_reviews) total_reviews
FROM courses
INNER JOIN teachers ON  courses.teacher_id = teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC

En mi caso usé la sentencia where/having para considerar la restricción de no contar cursos sin al menos un review. Sé que en este caso es redundante porque los cursos sin reviews implícitamente se sumarán como 0 y dará igual el resultado, pero en caso de no contar reviews sino otra columna (como cantidad de cursos por profesor) sí sería necesario agregar la restricción.

select 
  teachers.name as teacher, 
  sum(courses.n_reviews) as total_reviews
from teachers
inner join courses on teachers.id = courses.teacher_id
where courses.n_reviews >= 1 -- omitir si se usa having y viceversa
group by teacher
--having total_reviews >= 1
order by total_reviews desc;

Con las tablas/entidades abreviadas… 😊

Hola, les comparto mi solución!

SELECT
  teachers.name AS teacher,
  SUM(courses.n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers
  ON courses.teacher_id = teachers.id
GROUP BY teacher
ORDER BY total_reviews DESC;

SELECT teachers.name as teacher, Sum(courses.n_reviews) as total_reviews 
from teachers 
inner join courses on teachers.id = courses.teacher_id
group by teacher 
order by total_reviews desc;

SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews
FROM courses
INNER JOIN teachers ON teacher_id = teachers.id
GROUP BY teacher
HAVING total_reviews > 0
ORDER BY total_reviews DESC;

MI SOLUCION CON SU AYUDA ( ’ o ’ )

SELECT 
  teachers.name AS teacher, 
  SUM(courses.n_reviews) AS total_reviews 
FROM teachers
INNER JOIN courses 
  ON teachers.id = courses.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC;

 cláusula JOIN, la función SUM y la cláusula GROUP BY

para agrupar los cursos por profesor y luego sumar la cantidad total de reviews de los cursos de cada profesor. Luego, ordenarás los resultados de manera descendente. Aquí está la consulta SQL para lograrlo

Estuvo interesante el reto!

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses 
ON teachers.id = courses.teacher_id
GROUP BY courses.teacher_id
HAVING total_reviews > 1
ORDER BY total_reviews DESC;

Medio confuso, pero se logró:

SELECT 
  t.name as teacher,
  SUM(n_reviews) AS total_reviews
FROM courses c

LEFT JOIN teachers t ON c.teacher_id = t.id

WHERE t.name IS NOT NULL

GROUP BY t.name

ORDER BY total_reviews DESC;

Perdí una vida pero lo logré, jaja

select c.teacher_id,c.name
from courses c
order by c.teacher_id

Asi se fue XD

SELECT teachers.name AS teacher, sum(courses.n_reviews) AS total_reviews
FROM teachers,courses
WHERE teachers.id = courses.teacher_id 
GROUP BY teacher
ORDER BY total_reviews DESC
;

SELECT t.name AS teacher, sum(c.n_reviews) AS total_reviews
FROM teachers AS t, courses AS c 
WHERE t.id = c.teacher_id
GROUP BY teacher
ORDER BY total_reviews desc;

buena practica

SELECT
  teachers.name AS teacher, 
  sum(n_reviews) AS total_reviews
FROM
  courses
INNER JOIN 
  teachers ON courses.teacher_id=teachers.id  
GROUP BY
  teacher_id
ORDER BY
  total_reviews DESC
;

Comparto mi solución, el query funciona si omito el WHERE Y HAVING, pero decido dejarlo así por las condiciones que pide el ejercicio 😃

SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL
GROUP BY courses.teacher_id
HAVING total_reviews > 1
ORDER BY total_reviews DESC;

Aquí mi aporte con LEFT JOIN
.
.
.
.
.
.
.

SELECT t.name AS teacher,
SUM(c.n_reviews) AS total_reviews
FROM courses AS c
LEFT JOIN teachers AS t
ON c.teacher_id = t.id
WHERE t.id IS NOT NULL
GROUP BY teacher
ORDER BY total_reviews DESC
;

mi aporte

SELECT 

teachers.name AS teacher,

SUM(n_reviews) AS total_reviews

FROM courses
LEFT JOIN teachers ON courses.teacher_id = teachers.id

WHERE teacher_id IS NOT NULL

GROUP BY teacher
ORDER BY total_reviews DESC;

SELECT
teachers. name AS teacher ,
SUM(courses. n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses
ON teachers. id = courses.teacher_id
GROUP BY teacher
ORDER BY total_reviews DESC

Buenas tardes muchachos acá están mis aportes

<SELECT 
teachers.name AS teacher,
SUM (courses.n_reviews) AS total_reviews
FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY teacher_id
ORDER BY Total_reviews DESC;> 

SELECT teachers.name AS teacher, SUM(courses.n_reviews) AS total_reviews FROM teachers
INNER JOIN courses ON teachers.id = courses.teacher_id
GROUP BY teachers.id
ORDER BY total_reviews DESC;

Con lucha pero se pudo

SELECT teachers.name AS teacher,SUM(n_reviews) AS total_reviews
FROM courses 
LEFT JOIN teachers ON teacher_id = teachers.id
GROUP BY teacher_id HAVING teacher_id is not null
ORDER BY total_reviews DESC;