如何筛选10分钟时间窗口内失败存款次数≥5的用户？

核心修改仅需调整时间窗口的截断规则：把原SQL中两处DATE_TRUNC('hour', created)替换为DATE_TRUNC('10 minutes', created)，即可将统计窗口从1小时改为10分钟。

修改后的完整SQL代码如下：

with windowed_data as
    (
    select *,
       max(window_count) over (partition by user_id, transaction_window) as max_window_count
from (select
          *,
          COUNT(*) OVER (PARTITION BY user_id, DATE_TRUNC('10 minutes', created)  ORDER BY created) AS window_count,
          DATE_TRUNC('10 minutes', created) AS transaction_window
      FROM source.payments2_transactions
      WHERE state = 'FAILED'
      and type = 'DEPOSIT'
      and country = 'PE'
      and created >= current_date - interval '120 days'
      and created < current_date + interval '1 day') wd)
select
    user_id,
    created,
    window_count,
    transaction_window,
    max_window_count
from windowed_data wd
where max_window_count >= 5;

补充说明 #

• DATE_TRUNC('10 minutes', created)会将created字段的时间截断到最近的10分钟起始节点（如14:00、14:10、14:20），以此作为10分钟窗口的唯一标识。
• 原SQL的统计逻辑（按用户+窗口分区计数、取窗口内最大失败次数并筛选≥5的记录）完全保留，仅调整了窗口的时间粒度。

内容的提问来源于stack exchange，提问作者Steven