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)

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;

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;

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

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;

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

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.

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!

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;

Aqui mi propuesta de solucion, me llama la atencion como en las demas propuestas de solucion los compañeros hacen el join desde teachers como tabla de la cual jalan la informacion
Segun a mi me enseñaron en un join debes poner tu from desde la tabla donde esté la segunda tabla como foranea, o bien la tabla en donde este contenida la informacion de varias tablas, para despues poder explotar correctamente las relaciones, digo en esta ocasión no importa mucho pero me pareció muy curioso que colocaran el from desde una tabla independiente y no de la tabla donde estan enlazadas ambas

.
.
.
.
.
.
.
.
.
.

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

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

Hola les comparto mi query, ojo que no está bien, en mi caso no leí la parte de no contemplar los cursos sin review y tampoco renombre la columna t. name

<


SELECT t.name, sum(c.n_reviews) as total_reviews
FROM teachers t 
left JOIN courses c
ON t.id = c.teacher_id
Group by t.name
Order by total_reviews desc
;  
>

Aquí el correcto:

<

SELECT t.name as tracher, sum(c.n_reviews) as 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
; 


> 

Aquí mi aporte a la 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 teacher
ORDER BY total_reviews DESC
;

Mi solucion
↓
↓
↓
↓
↓
↓
↓
↓
↓
↓

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

-- Escribe tu código aquí 👇
SELECT teachers.name AS teacher, SUM(n_reviews) AS total_reviews
FROM courses
RIGHT JOIN teachers on teachers."id" = courses.teacher_id
GROUP BY teachers.name
HAVING total_reviews > 0
ORDER BY total_reviews DESC

mi solucion:

<SELECT teachers.name as teacher,sum(courses.n_reviews) as total_reviews FROM courses INNER JOIN teachers
on teachers.id=courses.teacher_id
where courses.teacher_id  is not null
and courses.n_reviews>=1
group by teacher
order by total_reviews DESC>