Spec the 28-day retention query for a dashboard

Your team wants an agent to write a Snowflake query for retention cohorts, and the numbers feed the executive dashboard. A cohort is the users who signed up on one day, and the query counts how many came back 1, 7 and 28 days later.

The last attempt looked right, but it counted a user twice when they left and came back. It also lost users whose local day did not line up with the UTC day.

Write the spec you would hand the agent, including how you will check the query.

What you know:

• events(user_id, event_ts, event_name), with event_ts in UTC, one row per event, and duplicate rows from the loader
• users(user_id, signup_ts, tz), with signup_ts in UTC and tz a timezone name like America/Los_Angeles
• the business rule: a user's cohort is their signup day in their own timezone, and they are retained on day N if they have any event on that local day