Mode disclosure
All modes use one coherent workspace; only disclosure and guidance change. Project mode keeps the persistent brief, milestones, notes, saved intermediate queries, validation checklist, and report export together.
Pulse Activation, Cohorts, and Retention / capstone step
P02-S01 - Pulse Activation, Cohorts, and Retention: Event audit
Event audit. A product team needs a trustworthy activation funnel and cohort-retention analysis from raw product events without double-counting users or dropping quiet periods.
- Result grain
- one row per eligible user and event_name audit bucket
- Exact columns
- account_id; user_id; event_name; event_count; first_occurred_at; last_occurred_at; duplicate_same_key_count
SQL editor shortcuts: Ctrl or Command Enter runs the query, Ctrl or Command Shift Enter checks it, Alt H opens the next hint, Ctrl or Command slash toggles a line comment, Ctrl or Command Shift F formats the SQL, and Escape closes transient UI.
Cursor at line 1, column 1.
Project Evidence Contract
- Grain declaration
- one row per eligible user and event_name audit bucket
- Key uniqueness
- account_id plus user_id plus event_name is unique.
- Dependency gate
- First milestone
Validation Checklist
- Check event_id remains the deterministic tie-breaker for same timestamps.
- Verify excluded account plans are not counted.
- Compare event_count with distinct event IDs for duplicate review.
Required Snapshots
- event audit output
All activation and retention milestones inherit this eligibility and duplicate-event policy.
Project notes and intermediate queries
Notes and saved intermediate queries stay local to this project milestone.
Scenario
A product team needs a trustworthy activation funnel and cohort-retention analysis from raw product events without double-counting users or dropping quiet periods.
Pulse Activation, Cohorts, and Retention / capstone step
One-sentence task
Event audit. A product team needs a trustworthy activation funnel and cohort-retention analysis from raw product events without double-counting users or dropping quiet periods.
Project mode disclosure
Persistent brief, milestones, notes, saved intermediate queries, validation checklist, and report export stay together.
Structured output contract
- Result grain
- one row per eligible user and event_name audit bucket
- Exact columns
- account_id; user_id; event_name; event_count; first_occurred_at; last_occurred_at; duplicate_same_key_count
- Source population
- Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.
- Grouping
- Group only at the requested output grain: one row per eligible user and event_name audit bucket.
- Ordering
- state event grain, duplicate policy, account eligibility, and deterministic event ordering
- Validation
- select-only; hidden deterministic variants.
Relevant tables
Time and difficulty
- Estimated time
- 8 minutes
- Difficulty
- 5/5
Objective and concepts
Produce event audit evidence with SQL, validation, assumptions, and bounded snapshots.
Glossary links
Concept material
Trustworthy activation funnel and cohort-retention analysis from product events.
Why this works
Trustworthy activation funnel and cohort-retention analysis from product events.
Edge cases
Duplicate events Same-timestamp events Out-of-order events Test accounts Activation exactly on the deadline No-activity months Late activation Reactivation Zero-size filtered segments
PostgreSQL note
Project SQL runs locally with PostgreSQL-compatible semantics and project-specific hidden variants.
Assumptions, dialect notes, and common traps
- Duplicate policy
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
- Null policy
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Tie-breakers
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Zero-related entities
- Preserve requested zero-related, no-match, or complete-period entities and make display fallback explicit.
- Numeric tolerance
- Use exact semantic comparison unless the activity explicitly declares a numeric tolerance.
Opened hints
No hints opened yet.