Solution to the Queo Backend Challenge

solution-queo-backend-challenge.sql

# NOTE: We need disable the ONLY_FULL_GROUP_BY mode
SET SESSION  sql_mode=(SELECT replace(REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''),'NO_ENGINE_SUBSTITUTION',''));

select count(1) cant from (
SELECT e.*, s.name , s2.code, group_concat(s2.code) codeVal, COUNT(s.name) cant
from enrollment e 
inner join student s on e.student_id =s.id 
inner join `group` g on e.group_id = g.id 
left join subject s2 on g.subject_id =s2.id and right(s2.code,1) = '1'
where e.enrollment_hour between '2024-02-05 00:00:00' AND '2024-02-07 23:59:59' 
group by e.student_id
having codeVal is not null and cant >4 
) t
;