Library › Data AI › Business analysts

Write SQL for conversion rate by channel without double counting

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

About this exercise

Marketing wants October 2025 conversion by channel: the share of visitors who placed a paid order on the same day as a session in that channel. Ask for one Postgres query in a single code block with at most 35 words outside it. Count people not sessions or orders, keep channels with no buyers, round the percent to one decimal, and sort highest first.

Common mistakes when you ask AI for this

  • Counting sessions and orders. The AI divides order rows by session rows and gets rates above 100%. Try: Add: "COUNT(DISTINCT user_id) on both sides."
  • Dropping non-buyers. The AI inner joins or puts the paid filter in WHERE. Try: Add: "LEFT JOIN with status = 'paid' in the ON clause."
  • Integer division. The percent comes out as 0. Try: Add: "100.0 * buyers / visitors, ROUND to 1 decimal."
  • 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 35 words."

What a strong prompt includes

  • Says count distinct
  • Says left join and ON
  • Gives the date filter
  • Gives the percent math
  • Caps the writing

Practice this now ↑

Related exercises