Prática - Exercícios Group By

Exercícios Group By

-- Questão 1:
Select COUNT("endDate") AS "currentExperiences" FROM experiences;

-- Questão 2:
Select "userId" AS id, COUNT("userId") as educations  from educations group by "userId";

-- Questão 3:
Select u.name AS writer, COUNT(t."writerId") as "testimonialCount" from users u   JOIN testimonials t ON 
u.id = t."writerId" WHERE "writerId" = 435 GROUP BY u.id;

- Questão 4:
Select MAX(j.salary) as maximumSalary, r.name as role FROM jobs j JOIN roles r ON j."roleId" = r.id 
linkedrivenv2-# where active = true GROUP BY r.id order by maximumSalary asc;

- Questão bonus:
Select s.name as school, c.name as course, MAX(e."userId") as studentsCount 
FROM educations e 
JOIN schools s ON e."schoolId" = s.id 
JOIN courses c ON e."courseId" = c.id
WHERE e.status = 'ongoing' or e.status = 'finished'
GROUP by  s.id , c.id
ORDER BY studentsCount DESC 
LIMIT 3;