Library › Coding AI › College students

Find students above their course average, minus audit seats

Practice: Giving the right context. About 5 minutes.

About this exercise

For your TA dashboard project, list enrollments scoring above their own course's average. Audit enrollments (status 'audit') have no real score, so leave them out of both the averages and the results. Columns exactly student_name, course_code, score, ordered by course_code, then score highest first. Write one PostgreSQL query in exactly 1 code block of at most 18 lines, under 25 words of text.

Common mistakes when you ask AI for this

  • Using the overall average. Wren's query compares everyone to one global average, which isn't what a TA needs. Try: Say: "Above the average of their own course."
  • Counting audit seats. Audit rows store 0, which drags each course average down unless you exclude them. Try: Add: "Exclude status = 'audit' from the averages and the results."
  • Unnamed columns and no order. The AI returns full_name and code with its own sort. Try: Add: "Columns exactly student_name, course_code, score; ORDER BY course_code, score DESC."
  • Several versions and a walkthrough. The AI shows a subquery version, a window version and explains both. Try: Add: "One query in one code block, no alternatives."

What a strong prompt includes

  • Says which average
  • Handles audit rows
  • Names the columns
  • Gives the order
  • Asks for one query

Practice this now ↑

Related exercises