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;

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

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;

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;

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 comparto el código de la prueba con comentarios de los pasos para que lo puedan entender mejor 😃

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!

Con las tablas/entidades abreviadas… 😊

Perdí una vida pero lo logré, jaja

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

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.

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;

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
; 

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> 

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

Si se pudo!!! 🥳🥳🥳

-- Escribe tu código aquí 👇
-- SELECT * FROM courses;

-- SELECT * FROM teachers;

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

Mi respuesta:

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

mi solución .
.
.
.
.
.
.
.

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

LEE ESTO ANTES DE EMPEZAR

En la descripcion dice que la tabla se llama teacher pero hay un error y la tabla se llama teachers.

Para que no te quedes pensando como loco cual es el error jeje

y tambien no se si soy el unico pero la consola me muestra que mi codigo esta bien y cumple las codiciones pero las pruebas nop y por lo que veo hay un error de entendimiento entre la consola y las pruebas, como sea aqui les dejo mi codigo espero les sirva.

SELECT teachers.name as teachers, sum(courses.n_reviews) as total_reviews
from teachers inner join courses on teachers.id = courses.teacher_id
group by teachers
having total_reviews >1
order by total_reviews desc;

43. Playground: Agrupamiento y Ordenamiento de Datos

-- Escribe tu código aquí 👇
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 teachers.name
ORDER BY total_reviews DESC;

Mi solución:

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

Mi solución!

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

Si estuvo bien difícil pero se logró!
*
Aquí está mi solución por si alguien se quiere guiar:

select 
teachers.name AS teacher, 
sum(courses.n_reviews) AS total_reviews
from courses 
left join teachers on courses.teacher_id = teachers.id
group by teachers.id, teachers.name
having count (courses.id) > 0 and courses.teacher_id is not null
order by total_reviews desc;

Como quedó muy largo el query decidí cambiar el nombre de las tablas y ponerles un alias 💚

Acá pueden probar el código:

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

Mi solución:

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

Teniendo clara la teoría de conjuntos se debería aplicar un INNER JOIN pero realice el ejercicio con un LEFT JOIN para tener que utilizar el HAVING y aplicar lo visto en la clase.
Igualmente no realice los filtros con los alias ya que en queries mas complejos se puede prestar para confusiones.

SELECT t.name       as teacher
  ,sum(c.n_reviews)  as total_reviews
FROM teachers     t
left join courses c on c.teacher_id = t.id
group by t.name
having sum(c.n_reviews) is not null
order by sum(c.n_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
HAVING total_reviews >=1 AND teacher IS NOT NULL
ORDER BY total_reviews DESC;> 

De mi lado use el Having

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

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

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;

Aquí Mi solución y la explico:

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;

EXPLICACION:

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

* La cláusula SELECT especifica las columnas que se incluirán en el resultado de la consulta.
* "teachers.name AS teacher" selecciona la columna "name" de la tabla "teachers" y la renombra como "teacher".
* "SUM(courses.n_reviews) AS total_reviews" calcula la suma de la columna "n_reviews" de la tabla "courses" y la renombra como "total_reviews".
* FROM teachers INNER JOIN courses ON teachers.id = courses.teacher_id:

2- La cláusula FROM especifica las tablas de las cuales se obtendrán los datos.
* "teachers" y "courses" son las tablas que se utilizan en la consulta.
* "INNER JOIN" combina las filas de las tablas "teachers" y "courses" basándose en la condición siguiente.
* "teachers.id = courses.teacher_id" es la condición de unión que establece que el ID del profesor en la tabla "teachers" debe ser igual al ID del profesor en la tabla "courses".

3- GROUP BY courses.teacher_id:

* La cláusula GROUP BY agrupa los resultados por el ID del profesor en la tabla "courses".
Esto significa que las filas con el mismo ID de profesor se agruparán juntas.

4- ORDER BY total_reviews DESC:

* La cláusula ORDER BY ordena los resultados en función de la columna "total_reviews" de forma descendente (de mayor a menor).
* Esto significa que los profesores con el mayor número de revisiones totales aparecerán primero en el resultado.

-- En resumen, esta consulta recuperará los nombres de los profesores y la suma total de revisiones de los cursos que han impartido, ordenados por el número total de revisiones en orden descendente. --

Solución al problema:

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 teachers.name
ORDER BY total_reviews DESC;

Ejercicio agrupamiento y ordenamiento de datos:

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

Se suele utilizar las iniciales del nombre de cada tabla para traer el valor de la columna. Por ejemplo, en este caso: courses (c) y necesito de courses la columna “n_reviews” por lo que quedaría ==> << c.n_reviews >>

Esta es mi solución, puede diferenciarse un poco con algunas respuestas, ya que no realizo INNER JOIN directamente con la sentencia “INNER”, la validacion la realizo con (teachers. id = courses.teacher_id) la cual esta en el "WHERE"
Espero sirva de ayuda y o si desean aportar alguna corrección bienvenida sea.

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

Comparto mi aporte
*
*

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
;

😁Logrado! Excelente que los retos vayan aumentando en complejidad, eso nos desafía a aplicar todo lo aprendido hasta ahora.
.
.
.
.
.
.
.
.

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

Solución
.
.
.
.
.

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

Mi código está aquí:

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;

Wuuu Resuelto !!
.
.
.
.
.
.
.
.
.
.
.
.

Hola a todos, les comparto mi código para este laboratorio, realmente interesante estos retos que nos plantea Platzi.
¡A seguir creciendo!

<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
HAVING total_reviews >= 1
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
ORDER BY total_reviews DESC;

select t.name as teacher, sum(c.n_reviews) as total_reviews
from courses c
join teachers t on t.id = c.teacher_id
group by t.name
order by total_reviews desc

Comparto mi Query

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
HAVING total_reviews >= 1
ORDER BY total_reviews DESC;

Me resultó un poco complejo completar porque no sabía de la función SUM(), la confundí con COUN, pero al final logré obtener el resultado, aqui les dejo mi respuesta

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 teachers.name
ORDER BY total_reviews DESC;

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

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
HAVING n_reviews>1
ORDER BY total_reviews DESC;

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

Piyenlon:
…
…
…
…
…
…
…
…

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 teachers.name
ORDER BY total_reviews DESC
;

Mi respuesta 😃

SELECT t.name as teacher, sum(c.n_reviews) as total_reviews
from courses c
INNER JOIN teachers t on c.teacher_id = t.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;

MI respuesta:

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 teacher
ORDER BY total_reviews DESC;

Muy dura Silvia.

SELECT * FROM courses;
SELECT * FROM teachers;


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,courses.n_reviews
HAVING courses.n_reviews > 0
ORDER BY total_reviews DESC;

#Creo que era algo asi, no estoy seguro