Input Tokens
0
Output Tokens
0
Duration
0
Cost
0
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 target_user AS (
SELECT u.user_id
FROM users u
JOIN enrollments e ON e.user_id = u.user_id
WHERE u.name ILIKE 'Oscar%'
AND EXTRACT(YEAR FROM e.enrolled_at) = 2018
LIMIT 1
)
SELECT co.title AS completed_course
FROM courses co
JOIN lessons l ON l.course_id = co.course_id
LEFT JOIN completions cl ON cl.user_id = (SELECT user_id FROM target_user) AND cl.lesson_id = l.lesson_id
GROUP BY co.course_id, co.title
HAVING COUNT(DISTINCT l.lesson_id) = COUNT(DISTINCT cl.lesson_id) AND COUNT(DISTINCT l.lesson_id) > 0;