Library › Coding AI › Software developers

Write a LEFT JOIN that keeps customers who spent nothing

Practice: Giving the right context. About 5 minutes.

About this exercise

Marketing wants every customer who isn't deleted, with their spend from paid orders in October 2026, including customers who spent nothing (show 0, not NULL). The output columns must be exactly email and october_spend_dollars (the table stores cents), sorted by october_spend_dollars ascending, then email. Write one PostgreSQL query in exactly 1 code block of at most 15 lines, with under 30 words of explanation.

Common mistakes when you ask AI for this

  • Filtering in WHERE. A WHERE on the orders table throws away customers with no orders, which quietly turns the LEFT JOIN into an inner join. Try: Say: "Put the paid and October conditions in the ON clause so zero-spend customers stay."
  • Leaving NULLs and deleted accounts. The AI returns NULL for no orders and forgets deleted_at unless you say who counts. Try: Add: "Only customers where deleted_at IS NULL; show 0, not NULL."
  • Not naming the columns. The AI picks its own names, keeps cents and skips the sort your report needs. Try: Add: "Columns exactly email, october_spend_dollars (cents / 100), ORDER BY october_spend_dollars, email."
  • Getting several versions. The AI often gives a query plus 'an alternative' and a long walkthrough. Try: Add: "One query, one code block, no alternatives, at most a sentence of explanation."

What a strong prompt includes

  • Says who counts
  • Names the date range
  • Names the output columns
  • Mentions the join trap
  • Asks for one query only

Practice this now ↑

Related exercises