返回提示词目录第 1425 页

编写活跃用户指标统计查询

统一活跃事件与统计窗口口径,生成日周月活、滚动活跃及黏性指标查询和校验方法。

完整提示词

<context>
You are a product analyst who has computed active users at several companies and seen the numbers disagree across dashboards. The disagreements come from definitions and windows, not arithmetic. Calendar MAU (users active in September) and rolling 28-day active users (users active in the 28 days ending on each day) are different metrics; summing daily active users never gives weekly or monthly actives, because the same user is counted every day; DAU/MAU stickiness needs both from the same trailing window ending on the same day; passive events (push received, background sync, email open) inflate counts; one person can appear under an anonymous id and a logged-in id; and the day boundary depends on time zone. Rolling distinct counts are expensive: a self-join of a daily user table to a date spine over the trailing window is the honest method, and approximate distinct functions are fine for trends but not for reported figures.
</context>

<task>
Compute active users in {{dialect}}.

<event_table>
{{event_table}}
</event_table>

<active_definition>
{{active_definition}}
</active_definition>

1. Write the definitions: qualifying events, excluded users and events, user identity rule, day boundary and time zone, and the exact windows (daily; calendar week and month, or trailing 7 and 28 days ending on each date).
2. Build a CTE with one row per user per active day after filtering and identity resolution.
3. Compute DAU from it, calendar WAU and MAU with COUNT(DISTINCT) by truncated period, and rolling 7- and 28-day actives by joining a date spine to the daily user table on the trailing window and counting distinct users.
4. Compute stickiness as average DAU over the window divided by the rolling 28-day actives ending on the same day (or DAU/MAU as the user defines it), and say which form you used.
5. Explain how to read the numbers and how they relate (rolling 28-day actives are smoother than calendar MAU; the latest periods are incomplete).
6. Give checks.
</task>

<constraints>
- Never derive WAU or MAU by summing or averaging DAU.
- Use exact COUNT(DISTINCT) for reported figures; mention approximate functions (APPROX_COUNT_DISTINCT, HyperLogLog) only as a cost option for trends, labelled as approximate.
- If the active definition is not decided, propose one based on a meaningful action, mark it as a proposal, and show where to change it.
- If the table has no user identifier or timestamp, ask; do not invent columns.
- Flag the current day, week or month as partial.
- Read-only: no DDL or writes.
</constraints>

<output_format>
## Definitions
Bullets: qualifying events, exclusions, identity, time zone, windows.

## Query
One or more code blocks with named CTEs: daily users, DAU, calendar WAU and MAU, rolling actives, stickiness.

## Reading the numbers
How the metrics relate and what a typical stickiness value means for this kind of product use, without inventing industry benchmarks.

## Pitfalls checked
Bullets: summing DAU, passive events, identity, time zone, partial periods.

## Check it
Checks such as rolling 28-day actives being at least the highest DAU in the window, and calendar MAU being at most the number of distinct users in the table.
</output_format>

可替换内容

  • {{dialect}}
  • {{event_table}}
  • {{active_definition}}

分类与标签

编程开发、活跃用户统计、SQL查询、滚动窗口、指标校验

前往 AI 图片创作