Library › Coding AI › Software developers

Count returns per category without double-counted rows

Practice: Giving the right context. About 5 minutes.

About this exercise

Ops wants product categories with more than 3 returned items in Q3 2026 (July 1 through September 30). You know the returns logger sometimes writes the same order_item_id twice, so count distinct items, and products with no category must show as 'Uncategorized'. Columns exactly category and returned_items, highest first. Write one SQLite query in exactly 1 code block of at most 14 lines, with under 30 words of text.

Common mistakes when you ask AI for this

  • Counting rows, not items. The AI copies COUNT(*) from the old query, so the logger's duplicate rows inflate the numbers. Try: Add: "The logger writes duplicates; count DISTINCT order_item_id."
  • Losing the NULL category. Grouping by a NULL category gives an unlabeled row the report can't read. Try: Add: "Show NULL categories as 'Uncategorized'. Columns exactly category, returned_items."
  • Fuzzy quarter edges. Comparing to '2026-09-30' drops returns later that day, like the 22:40 one. Try: Add: "returned_at >= '2026-07-01' AND returned_at < '2026-10-01'."
  • A query plus a tutorial. The AI explains GROUP BY and HAVING and offers variants. Try: Add: "One query in one code block, no alternatives, one sentence max."

What a strong prompt includes

  • Mentions the duplicate rows
  • Handles missing categories
  • Gives the date range
  • Names the output columns
  • Asks for one query only

Practice this now ↑

Related exercises