如何筛选两日交易达标账户并汇总其30天审核期交易金额？

需求描述 #

若账户在两日周期内存在2笔及以上独立交易，且该周期内交易总额达到100美元及以上，需汇总该账户在30天审核期内的交易总金额。

补充说明：

理想情况下，查询结果应仅包含Acct_1和Acct_3，因为只有这两个账户存在满足条件的两日周期（该周期内交易总额超100美元且交易数≥2）。

已编写的SQL代码片段 #

WITH date_interval AS (
        SELECT
          CAST(dt_et AS DATE) AS lkbck_end,
          CAST(dt_et AS DATE) - INTERVAL '1' DAY AS lkbck_start
        FROM core_data.daily_calendar
         WHERE 
          CAST(dt_et AS DATE) >= CAST('2024-07-01' AS DATE) - INTERVAL '30' day
          AND CAST(dt_et AS DATE) <= CAST('2024-07-01' AS DATE)
      ) 

, c AS (
          SELECT account_id,
          CAST(SUBSTR(created_at, 1, 10) AS DATE) AS created_at, 
          c.authorized_amount,
          c.mcc,
          c.merchant_id,
          c.entry_type,
          c.transaction_type,
          c.state,
          c.decline_reason
          FROM card_transactions_table c
          WHERE state IN ('settled', 'pending')
            AND transaction_type IN ('auth', 'preauth')
            AND CAST(SUBSTR(created_at, 1, 10) AS DATE) 
                  BETWEEN CAST(COALESCE(CAST('2024-07-01'AS DATE), current_date) AS DATE) - interval '31' day 
                  AND CAST(COALESCE(CAST('2024-07-01'AS DATE), current_date) AS DATE) - interval '1' day
            AND dt = CAST(CAST(COALESCE(CAST('2024-07-01'AS DATE), current_date) AS DATE) - interval '1' day AS VARCHAR)
        )
        , m AS (
          SELECT *
          FROM merchant_table
          WHERE dt = CAST(CAST(COALESCE(CAST('2024-07-01'AS DATE), current_date) AS DATE) - interval '1' day AS VARCHAR)
        )
        , a AS (
          SELECT *
          FROM account_table
          WHERE dt = CAST(CAST(COALESCE(TIMESTAMP '2024-07-01', current_date) AS DATE) - interval '1' day AS VARCHAR)
        )
        , h AS (
          SELECT c.account_id
            , CAST(CAST(c.created_at AS TIMESTAMP) AS DATE) AS transaction_date
            , c.authorized_amount
            , m.category
            , m.aggregate_merchant_id
          FROM c
          INNER JOIN m
            ON c.merchant_id = m.id
          WHERE CAST(c.created_at AS DATE) BETWEEN CAST(COALESCE(CAST('2024-07-01' AS DATE), current_date) AS DATE) - interval '31' day 
                                                    AND CAST(COALESCE(CAST('2024-07-01'AS DATE), current_date) AS DATE) - interval '1' day
        ) 

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