grouped by age in first course

grouped by age in first course and doesnt have lesson
mail@pastecode.io avatar
unknown
mysql
2 years ago
581 B
4
Indexable
Never
SELECT
	TIMESTAMPDIFF(YEAR, birthday, (
			SELECT
				start_date FROM courses
				JOIN course_students ON courses.id = course_students.course_id
			WHERE
				student_id = students.id
			ORDER BY
				start_date ASC
			LIMIT 1)) AS age,
	count(*) AS total
FROM
	students
WHERE
	birthday IS NOT NULL
	AND deleted_at IS NULL
	AND EXISTS (
		SELECT
			*
		FROM
			courses
			JOIN course_students ON courses.id = course_students.course_id
		WHERE
			student_id = students.id)
		AND NOT EXISTS (
			SELECT
				*
			FROM
				lessons
			WHERE
				student_id = students.id)
		GROUP BY
			age