Skip to lesson
Exit
Capstone: Mock Loop & Self-Exam1 / 2

1 min lesson

Task 2 - the prompt→success funnel with drop-off

Explain what the example in "Task 2 - the prompt → success funnel with drop-off" is doing and why it matters.

Step 1 of 2

Task 2 - the prompt→success funnel with drop-off

Build the stage funnel prompt → retrieval → tool_call → edit_apply → success and show where attempts die. Anchor every stage to request_id so you're counting logical attempts, not raw rows.

stage funnel: distinct requests reaching each step, with step-over-step drop-off
WITH per_request AS (
  SELECT
    request_id,
    BOOL_OR(event_type = 'prompt')     AS hit_prompt,
    BOOL_OR(event_type = 'retrieval')  AS hit_retrieval,
    BOOL_OR(event_type = 'tool_call')  AS hit_tool,
    BOOL_OR(event_type = 'edit_apply') AS hit_edit,
    BOOL_OR(event_type = 'success')    AS hit_success
  FROM interaction_events
  GROUP BY request_id
)
SELECT
  COUNT(*) FILTER (WHERE hit_prompt)    AS s1_prompt,
  COUNT(*) FILTER (WHERE hit_retrieval) AS s2_retrieval,
  COUNT(*) FILTER (WHERE hit_tool)      AS s3_tool_call,
  COUNT(*) FILTER (WHERE hit_edit)      AS s4_edit_apply,
  COUNT(*) FILTER (WHERE hit_success)   AS s5_success,
  ROUND(100.0 * COUNT(*) FILTER (WHERE hit_success)
              / NULLIF(COUNT(*) FILTER (WHERE hit_prompt), 0), 1) AS overall_pct
FROM per_request;
Funnels lie when stages aren't strictly ordered

An agent can loop: retrieve, call a tool, retrieve again. If you count raw rows you'll show more tool_calls than prompts and the funnel inverts. Collapsing to distinct request_ids per stage fixes that. Say the assumption out loud: are stages monotonic or can a request reach success without an edit_apply? If you don't know, state how you'd check rather than assume.