Prepare the dataset

-- Create a consolidated joined_table from customer, accounts, and transactions
CREATE TABLE td_analytics_functions_demo.joined_table AS (
  SELECT
    T1.cust_id AS cust_id,
    MIN(T1.income) AS tot_income,
    MIN(T1.age) AS tot_age,
    MIN(T1.years_with_bank) AS tot_cust_years,
    MIN(T1.nbr_children) AS tot_children,
    MIN(T1.marital_status) AS marital_status,
    MIN(T1.gender) AS gender,
    MAX(T1.state_code) AS state_code,
    AVG(CASE WHEN T2.acct_type = 'CK' THEN T2.starting_balance + T2.ending_balance ELSE 0 END) AS ck_avg_bal,
    AVG(CASE WHEN T2.acct_type = 'SV' THEN T2.starting_balance + T2.ending_balance ELSE 0 END) AS sv_avg_bal,
    AVG(CASE WHEN T2.acct_type = 'CC' THEN T2.starting_balance + T2.ending_balance ELSE 0 END) AS cc_avg_bal,
    AVG(CASE WHEN T2.acct_type = 'CK' THEN T3.principal_amt + T3.interest_amt ELSE 0 END) AS ck_avg_tran_amt,
    AVG(CASE WHEN T2.acct_type = 'SV' THEN T3.principal_amt + T3.interest_amt ELSE 0 END) AS sv_avg_tran_amt,
    AVG(CASE WHEN T2.acct_type = 'CC' THEN T3.principal_amt + T3.interest_amt ELSE 0 END) AS cc_avg_tran_amt,
    COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 1 THEN T3.tran_id ELSE NULL END) AS q1_trans_cnt,
    COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 2 THEN T3.tran_id ELSE NULL END) AS q2_trans_cnt,
    COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 3 THEN T3.tran_id ELSE NULL END) AS q3_trans_cnt,
    COUNT(CASE WHEN ((EXTRACT(MONTH FROM T3.tran_date) + 2) / 3) = 4 THEN T3.tran_id ELSE NULL END) AS q4_trans_cnt
  FROM td_analytics_functions_demo.customer AS T1
  LEFT OUTER JOIN td_analytics_functions_demo.accounts AS T2
    ON T1.cust_id = T2.cust_id
  LEFT OUTER JOIN td_analytics_functions_demo.transactions AS T3
    ON T2.acct_nbr = T3.acct_nbr
  GROUP BY T1.cust_id
) WITH DATA UNIQUE PRIMARY INDEX (cust_id);

SELECT TOP 10 *
FROM td_analytics_functions_demo.joined_table;