Library › Data AI › Business analysts

Write SQL that keeps the latest record per customer

  • For Business analysts
  • You practice Setting limits and rules
  • Time about 5 minutes
  • Level Advanced

About this exercise

A contacts table has several snapshots per customer and you need only the current one. Ask for one Postgres query in a single code block with at most 30 words outside it. Keep the newest non-deleted row per customer_id, break ties by the higher snapshot_id, and return customer_id, email, plan and updated_at. Start the code with the comment line '-- report: current_snapshots'.

Common mistakes when you ask AI for this

  • Filtering deleted too early. The AI adds WHERE is_deleted = false first, so customer 22 returns with the old row. Try: Add: "Rank all snapshots first, then keep rn = 1 AND is_deleted = false."
  • No tie-breaker. Two rows with the same timestamp both rank first or the choice is random. Try: Add: "ORDER BY updated_at DESC, snapshot_id DESC."
  • Self-joining on max date. The AI uses a join on MAX(updated_at), which returns duplicate rows for ties. Try: Add: "Use ROW_NUMBER in a CTE, no self-join."
  • Leaving the format to chance. The AI picks its own length and layout unless told the exact shape. Try: Add: "Put the code in exactly 1 code block. Keep the writing outside the code under 30 words."

What a strong prompt includes

  • Names the window function
  • Gives the tie-break
  • Says the filter order
  • Bans the self-join
  • Caps the writing

Practice this now ↑

Related exercises