Aggregating spend by customer where accounts can be re-assigned to different customers
You need a kind of best-match join like @TypoCubeᵀᴹ's approach, but depending on the actual data (i.e. number of rows per Account_no) this might create a huge intermediate result before aggregation (and needs a uniqueness contraint on table2(Account_no, Date)).

The same logic logic can be achieved by a different approach using UNION ALL and LAST_VALUE:

SELECT Customer_ID, Sum(spend)
FROM 
 (
   SELECT
      -- assign the "previous" Customer_ID to the current row
      Last_Value(Customer_ID IGNORE NULLS) 
          -- within a date first rows from table1, followed by table2
      Over (ORDER BY Date, x) AS Customer_ID,
      Spend
   FROM
    (
      SELECT 
         1 AS x, -- flag indicating source table
         Customer_ID,
         Account_no,
         dt,
         -- need to cast to datatype of table2.Spend
         Cast(NULL AS ...) AS Spend
      FROM cust
      UNION ALL
      SELECT
         2 AS x,
         -- need to cast to datatype of table1.Customer_ID 
         Cast(NULL AS ...), 
         Account_no,
         dt,
         spend
      FROM spend
    ) AS dt
   QUALIFY -- only rows from table2
           x = 2
       AND -- only if table1 and table2 Account_no match
           Last_Value(CASE WHEN x = 1 THEN Account_no END IGNORE NULLS) 
           Over (ORDER BY dt, Spend) = Account_no

 ) AS dt
GROUP BY Customer_ID
;