Duration
7s
Input Tokens
1023
Output Tokens
506
Cost
$0.01
Context
Input
Hi I'm Oscar that enrolled in 2018, I don't remember what courses I completed, can you help me?Expected output
SELECT DISTINCT c.title FROM users u JOIN enrollments e ON u.user_id = e.user_id JOIN lessons l ON l.course_id = e.course_id JOIN completions cp ON cp.lesson_id = l.lesson_id AND cp.user_id = u.user_id JOIN courses c ON c.course_id = l.course_id WHERE u.name ILIKE '%oscar%' AND DATE_TRUNC('year', e.enrolled_at) = DATE '2018-01-01';Model output
WITH oscar AS (
SELECT u.user_id
FROM users u
WHERE u.name = 'Oscar'
ORDER BY u.user_id
LIMIT 1
), course_lesson_counts AS (
SELECT l.course_id, COUNT(*)::int AS total_lessons
FROM lessons l
GROUP BY l.course_id
), oscar_course_progress AS (
SELECT
e.course_id,
COUNT(DISTINCT c.lesson_id)::int AS lessons_completed
FROM oscar o
JOIN enrollments e ON e.user_id = o.user_id
LEFT JOIN lessons l ON l.course_id = e.course_id
LEFT JOIN completions c ON c.user_id = o.user_id AND c.lesson_id = l.lesson_id
WHERE e.enrolled_at >= TIMESTAMP '2018-01-01'
AND e.enrolled_at < TIMESTAMP '2019-01-01'
GROUP BY e.course_id
)
SELECT
crs.course_id,
crs.title AS course_title,
e.enrolled_at,
e.status AS enrollment_status,
COALESCE(clc.total_lessons, 0) AS total_lessons,
COALESCE(ocp.lessons_completed, 0) AS lessons_completed,
CASE
WHEN COALESCE(clc.total_lessons, 0) > 0
AND COALESCE(ocp.lessons_completed, 0) = COALESCE(clc.total_lessons, 0)
THEN TRUE
ELSE FALSE
END AS course_completed
FROM oscar o
JOIN enrollments e ON e.user_id = o.user_id
JOIN courses crs ON crs.course_id = e.course_id
LEFT JOIN course_lesson_counts clc ON clc.course_id = e.course_id
LEFT JOIN oscar_course_progress ocp ON ocp.course_id = e.course_id
WHERE e.enrolled_at >= TIMESTAMP '2018-01-01'
AND e.enrolled_at < TIMESTAMP '2019-01-01'
ORDER BY e.enrolled_at, crs.title;