I am trying to find a way to sum a transaction amount over a 30-day review period if the account had 2 or more distinct transactions that totaled $100 or more.

So if the account had three transactions:
05/17 – $60.00
05/16 – $50.00
05/15 – $60.00

It would only populate 1 result (doesn’t really matter which of the grouping of 2 it would populate on).

 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
        )