Final daily aggregates

%jdbc(hive)
set tez.queue.name=default;
set hive.execution.engine=tez;

select senderuserid,
registration_date,
total_txns,
first_txn_date,
last_txn_date,
first_upi_txn_date,
first_money_received_date,

--------------------C2C AGGREGATES-------------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2c_txn_succ else 0 end ) as c2c_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2c_txn_succ else 0  end ) as c2c_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2c_txn_succ else 0  end ) as c2c_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2c_txn_succ else 0  end ) as c2c_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2c_txn_succ else 0  end ) as c2c_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2c_txn_err else 0  end ) as c2c_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2c_txn_err  else 0 end ) as c2c_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2c_txn_err else 0  end ) as c2c_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2c_txn_err else 0  end ) as c2c_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2c_txn_err else 0  end ) as c2c_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2c_txn_total else 0  end ) as c2c_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2c_txn_total else 0  end ) as c2c_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2c_txn_total else 0  end ) as c2c_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2c_txn_total else 0 end ) as c2c_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2c_txn_total else 0 end ) as c2c_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2c_amt_succ else 0  end ) as c2c_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2c_amt_succ else 0  end ) as c2c_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2c_amt_succ else 0 end ) as c2c_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2c_amt_succ else 0 end ) as c2c_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2c_amt_succ else 0 end ) as c2c_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2c_amt_err else 0 end ) as c2c_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2c_amt_err else 0  end ) as c2c_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2c_amt_err else 0 end ) as c2c_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2c_amt_err else 0 end ) as c2c_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2c_amt_err else 0 end ) as c2c_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2c_amt_total else 0 end ) as c2c_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2c_amt_total else 0 end ) as c2c_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2c_amt_total else 0 end ) as c2c_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2c_amt_total else 0 end ) as c2c_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2c_amt_total else 0 end ) as c2c_amt_total_180d,
--------------------C2E USER AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_user_txn_succ else 0 end ) as c2e_user_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_user_txn_succ else 0  end ) as c2e_user_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_user_txn_succ else 0  end ) as c2e_user_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_user_txn_succ else 0  end ) as c2e_user_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_user_txn_succ else 0  end ) as c2e_user_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_user_txn_err else 0  end ) as c2e_user_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_user_txn_err  else 0 end ) as c2e_user_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_user_txn_err else 0  end ) as c2e_user_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_user_txn_err else 0  end ) as c2e_user_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_user_txn_err else 0  end ) as c2e_user_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_user_txn_total else 0  end ) as c2e_user_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_user_txn_total else 0  end ) as c2e_user_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_user_txn_total else 0  end ) as c2e_user_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_user_txn_total else 0 end ) as c2e_user_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_user_txn_total else 0 end ) as c2e_user_txn_total_180d,


SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_user_amt_succ else 0  end ) as c2e_user_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_user_amt_succ else 0  end ) as c2e_user_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_user_amt_succ else 0 end ) as c2e_user_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_user_amt_succ else 0 end ) as c2e_user_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_user_amt_succ else 0 end ) as c2e_user_amt_succ_180d,


SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_user_amt_err else 0 end ) as c2e_user_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_user_amt_err else 0  end ) as c2e_user_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_user_amt_err else 0 end ) as c2e_user_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_user_amt_err else 0 end ) as c2e_user_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_user_amt_err else 0 end ) as c2e_user_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_user_amt_total else 0 end ) as c2e_user_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_user_amt_total else 0 end ) as c2e_user_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_user_amt_total else 0 end ) as c2e_user_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_user_amt_total else 0 end ) as c2e_user_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_user_amt_total else 0 end ) as c2e_user_amt_total_180d,
--------------------C2E MERCHANT AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_mer_txn_succ else 0 end ) as c2e_mer_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_mer_txn_succ else 0  end ) as c2e_mer_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_mer_txn_succ else 0  end ) as c2e_mer_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_mer_txn_succ else 0  end ) as c2e_mer_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_mer_txn_succ else 0  end ) as c2e_mer_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_mer_txn_err else 0  end ) as c2e_mer_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_mer_txn_err  else 0 end ) as c2e_mer_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_mer_txn_err else 0  end ) as c2e_mer_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_mer_txn_err else 0  end ) as c2e_mer_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_mer_txn_err else 0  end ) as c2e_mer_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_mer_txn_total else 0  end ) as c2e_mer_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_mer_txn_total else 0  end ) as c2e_mer_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_mer_txn_total else 0  end ) as c2e_mer_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_mer_txn_total else 0 end ) as c2e_mer_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_mer_txn_total else 0 end ) as c2e_mer_txn_total_180d,


SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_mer_amt_succ else 0  end ) as c2e_mer_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_mer_amt_succ else 0  end ) as c2e_mer_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_mer_amt_succ else 0 end ) as c2e_mer_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_mer_amt_succ else 0 end ) as c2e_mer_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_mer_amt_succ else 0 end ) as c2e_mer_amt_succ_180d,


SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_mer_amt_err else 0 end ) as c2e_mer_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_mer_amt_err else 0  end ) as c2e_mer_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_mer_amt_err else 0 end ) as c2e_mer_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_mer_amt_err else 0 end ) as c2e_mer_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_mer_amt_err else 0 end ) as c2e_mer_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2e_mer_amt_total else 0 end ) as c2e_mer_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2e_mer_amt_total else 0 end ) as c2e_mer_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2e_mer_amt_total else 0 end ) as c2e_mer_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2e_mer_amt_total else 0 end ) as c2e_mer_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2e_mer_amt_total else 0 end ) as c2e_mer_amt_total_180d,
--------------------C2A AGGREGATES--------------------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2a_txn_succ else 0 end ) as c2a_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2a_txn_succ else 0  end ) as c2a_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2a_txn_succ else 0  end ) as c2a_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2a_txn_succ else 0  end ) as c2a_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2a_txn_succ else 0  end ) as c2a_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2a_txn_err else 0  end ) as c2a_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2a_txn_err  else 0 end ) as c2a_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2a_txn_err else 0  end ) as c2a_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2a_txn_err else 0  end ) as c2a_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2a_txn_err else 0  end ) as c2a_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2a_txn_total else 0  end ) as c2a_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2a_txn_total else 0  end ) as c2a_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2a_txn_total else 0  end ) as c2a_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2a_txn_total else 0 end ) as c2a_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2a_txn_total else 0 end ) as c2a_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2a_amt_succ else 0  end ) as c2a_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2a_amt_succ else 0  end ) as c2a_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2a_amt_succ else 0 end ) as c2a_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2a_amt_succ else 0 end ) as c2a_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2a_amt_succ else 0 end ) as c2a_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2a_amt_err else 0 end ) as c2a_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2a_amt_err else 0  end ) as c2a_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2a_amt_err else 0 end ) as c2a_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2a_amt_err else 0 end ) as c2a_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2a_amt_err else 0 end ) as c2a_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then c2a_amt_total else 0 end ) as c2a_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then c2a_amt_total else 0 end ) as c2a_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then c2a_amt_total else 0 end ) as c2a_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then c2a_amt_total else 0 end ) as c2a_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then c2a_amt_total else 0 end ) as c2a_amt_total_180d,
--------------------GOLD AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gold_txn_succ else 0 end ) as gold_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gold_txn_succ else 0  end ) as gold_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gold_txn_succ else 0  end ) as gold_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gold_txn_succ else 0  end ) as gold_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gold_txn_succ else 0  end ) as gold_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gold_txn_err else 0  end ) as gold_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gold_txn_err  else 0 end ) as gold_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gold_txn_err else 0  end ) as gold_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gold_txn_err else 0  end ) as gold_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gold_txn_err else 0  end ) as gold_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gold_txn_total else 0  end ) as gold_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gold_txn_total else 0  end ) as gold_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gold_txn_total else 0  end ) as gold_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gold_txn_total else 0 end ) as gold_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gold_txn_total else 0 end ) as gold_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gold_amt_succ else 0  end ) as gold_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gold_amt_succ else 0  end ) as gold_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gold_amt_succ else 0 end ) as gold_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gold_amt_succ else 0 end ) as gold_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gold_amt_succ else 0 end ) as gold_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gold_amt_err else 0 end ) as gold_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gold_amt_err else 0  end ) as gold_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gold_amt_err else 0 end ) as gold_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gold_amt_err else 0 end ) as gold_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gold_amt_err else 0 end ) as gold_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gold_amt_total else 0 end ) as gold_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gold_amt_total else 0 end ) as gold_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gold_amt_total else 0 end ) as gold_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gold_amt_total else 0 end ) as gold_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gold_amt_total else 0 end ) as gold_amt_total_180d,

--------------------EDUCATION AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then edu_txn_succ else 0 end ) as edu_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then edu_txn_succ else 0  end ) as edu_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then edu_txn_succ else 0  end ) as edu_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then edu_txn_succ else 0  end ) as edu_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then edu_txn_succ else 0  end ) as edu_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then edu_txn_err else 0  end ) as edu_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then edu_txn_err  else 0 end ) as edu_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then edu_txn_err else 0  end ) as edu_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then edu_txn_err else 0  end ) as edu_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then edu_txn_err else 0  end ) as edu_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then edu_txn_total else 0  end ) as edu_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then edu_txn_total else 0  end ) as edu_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then edu_txn_total else 0  end ) as edu_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then edu_txn_total else 0 end ) as edu_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then edu_txn_total else 0 end ) as edu_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then edu_amt_succ else 0  end ) as edu_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then edu_amt_succ else 0  end ) as edu_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then edu_amt_succ else 0 end ) as edu_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then edu_amt_succ else 0 end ) as edu_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then edu_amt_succ else 0 end ) as edu_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then edu_amt_err else 0 end ) as edu_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then edu_amt_err else 0  end ) as edu_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then edu_amt_err else 0 end ) as edu_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then edu_amt_err else 0 end ) as edu_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then edu_amt_err else 0 end ) as edu_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then edu_amt_total else 0 end ) as edu_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then edu_amt_total else 0 end ) as edu_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then edu_amt_total else 0 end ) as edu_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then edu_amt_total else 0 end ) as edu_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then edu_amt_total else 0 end ) as edu_amt_total_180d,

--------------------PHONEPEGC AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppgc_txn_succ else 0 end ) as ppgc_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppgc_txn_succ else 0  end ) as ppgc_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppgc_txn_succ else 0  end ) as ppgc_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppgc_txn_succ else 0  end ) as ppgc_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppgc_txn_succ else 0  end ) as ppgc_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppgc_txn_err else 0  end ) as ppgc_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppgc_txn_err  else 0 end ) as ppgc_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppgc_txn_err else 0  end ) as ppgc_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppgc_txn_err else 0  end ) as ppgc_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppgc_txn_err else 0  end ) as ppgc_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppgc_txn_total else 0  end ) as ppgc_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppgc_txn_total else 0  end ) as ppgc_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppgc_txn_total else 0  end ) as ppgc_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppgc_txn_total else 0 end ) as ppgc_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppgc_txn_total else 0 end ) as ppgc_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppgc_amt_succ else 0  end ) as ppgc_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppgc_amt_succ else 0  end ) as ppgc_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppgc_amt_succ else 0 end ) as ppgc_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppgc_amt_succ else 0 end ) as ppgc_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppgc_amt_succ else 0 end ) as ppgc_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppgc_amt_err else 0 end ) as ppgc_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppgc_amt_err else 0  end ) as ppgc_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppgc_amt_err else 0 end ) as ppgc_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppgc_amt_err else 0 end ) as ppgc_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppgc_amt_err else 0 end ) as ppgc_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppgc_amt_total else 0 end ) as ppgc_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppgc_amt_total else 0 end ) as ppgc_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppgc_amt_total else 0 end ) as ppgc_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppgc_amt_total else 0 end ) as ppgc_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppgc_amt_total else 0 end ) as ppgc_amt_total_180d,

--------------------GPRC AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gprc_txn_succ else 0 end ) as gprc_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gprc_txn_succ else 0  end ) as gprc_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gprc_txn_succ else 0  end ) as gprc_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gprc_txn_succ else 0  end ) as gprc_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gprc_txn_succ else 0  end ) as gprc_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gprc_txn_err else 0  end ) as gprc_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gprc_txn_err  else 0 end ) as gprc_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gprc_txn_err else 0  end ) as gprc_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gprc_txn_err else 0  end ) as gprc_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gprc_txn_err else 0  end ) as gprc_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gprc_txn_total else 0  end ) as gprc_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gprc_txn_total else 0  end ) as gprc_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gprc_txn_total else 0  end ) as gprc_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gprc_txn_total else 0 end ) as gprc_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gprc_txn_total else 0 end ) as gprc_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gprc_amt_succ else 0  end ) as gprc_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gprc_amt_succ else 0  end ) as gprc_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gprc_amt_succ else 0 end ) as gprc_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gprc_amt_succ else 0 end ) as gprc_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gprc_amt_succ else 0 end ) as gprc_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gprc_amt_err else 0 end ) as gprc_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gprc_amt_err else 0  end ) as gprc_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gprc_amt_err else 0 end ) as gprc_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gprc_amt_err else 0 end ) as gprc_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gprc_amt_err else 0 end ) as gprc_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then gprc_amt_total else 0 end ) as gprc_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then gprc_amt_total else 0 end ) as gprc_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then gprc_amt_total else 0 end ) as gprc_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then gprc_amt_total else 0 end ) as gprc_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then gprc_amt_total else 0 end ) as gprc_amt_total_180d,
--------------------RECHARGES AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then recharge_txn_succ else 0 end ) as recharge_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then recharge_txn_succ else 0  end ) as recharge_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then recharge_txn_succ else 0  end ) as recharge_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then recharge_txn_succ else 0  end ) as recharge_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then recharge_txn_succ else 0  end ) as recharge_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then recharge_txn_err else 0  end ) as recharge_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then recharge_txn_err  else 0 end ) as recharge_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then recharge_txn_err else 0  end ) as recharge_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then recharge_txn_err else 0  end ) as recharge_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then recharge_txn_err else 0  end ) as recharge_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then recharge_txn_total else 0  end ) as recharge_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then recharge_txn_total else 0  end ) as recharge_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then recharge_txn_total else 0  end ) as recharge_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then recharge_txn_total else 0 end ) as recharge_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then recharge_txn_total else 0 end ) as recharge_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then recharge_amt_succ else 0  end ) as recharge_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then recharge_amt_succ else 0  end ) as recharge_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then recharge_amt_succ else 0 end ) as recharge_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then recharge_amt_succ else 0 end ) as recharge_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then recharge_amt_succ else 0 end ) as recharge_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then recharge_amt_err else 0 end ) as recharge_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then recharge_amt_err else 0  end ) as recharge_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then recharge_amt_err else 0 end ) as recharge_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then recharge_amt_err else 0 end ) as recharge_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then recharge_amt_err else 0 end ) as recharge_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then recharge_amt_total else 0 end ) as recharge_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then recharge_amt_total else 0 end ) as recharge_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then recharge_amt_total else 0 end ) as recharge_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then recharge_amt_total else 0 end ) as recharge_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then recharge_amt_total else 0 end ) as recharge_amt_total_180d,
-------------------- BILLPAY AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then billpay_txn_succ else 0 end ) as billpay_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then billpay_txn_succ else 0  end ) as billpay_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then billpay_txn_succ else 0  end ) as billpay_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then billpay_txn_succ else 0  end ) as billpay_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then billpay_txn_succ else 0  end ) as billpay_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then billpay_txn_err else 0  end ) as billpay_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then billpay_txn_err  else 0 end ) as billpay_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then billpay_txn_err else 0  end ) as billpay_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then billpay_txn_err else 0  end ) as billpay_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then billpay_txn_err else 0  end ) as billpay_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then billpay_txn_total else 0  end ) as billpay_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then billpay_txn_total else 0  end ) as billpay_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then billpay_txn_total else 0  end ) as billpay_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then billpay_txn_total else 0 end ) as billpay_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then billpay_txn_total else 0 end ) as billpay_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then billpay_amt_succ else 0  end ) as billpay_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then billpay_amt_succ else 0  end ) as billpay_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then billpay_amt_succ else 0 end ) as billpay_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then billpay_amt_succ else 0 end ) as billpay_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then billpay_amt_succ else 0 end ) as billpay_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then billpay_amt_err else 0 end ) as billpay_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then billpay_amt_err else 0  end ) as billpay_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then billpay_amt_err else 0 end ) as billpay_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then billpay_amt_err else 0 end ) as billpay_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then billpay_amt_err else 0 end ) as billpay_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then billpay_amt_total else 0 end ) as billpay_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then billpay_amt_total else 0 end ) as billpay_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then billpay_amt_total else 0 end ) as billpay_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then billpay_amt_total else 0 end ) as billpay_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then billpay_amt_total else 0 end ) as billpay_amt_total_180d,

-------------------- VOUCHER AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then voucher_txn_succ else 0 end ) as voucher_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then voucher_txn_succ else 0  end ) as voucher_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then voucher_txn_succ else 0  end ) as voucher_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then voucher_txn_succ else 0  end ) as voucher_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then voucher_txn_succ else 0  end ) as voucher_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then voucher_txn_err else 0  end ) as voucher_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then voucher_txn_err  else 0 end ) as voucher_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then voucher_txn_err else 0  end ) as voucher_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then voucher_txn_err else 0  end ) as voucher_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then voucher_txn_err else 0  end ) as voucher_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then voucher_txn_total else 0  end ) as voucher_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then voucher_txn_total else 0  end ) as voucher_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then voucher_txn_total else 0  end ) as voucher_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then voucher_txn_total else 0 end ) as voucher_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then voucher_txn_total else 0 end ) as voucher_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then voucher_amt_succ else 0  end ) as voucher_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then voucher_amt_succ else 0  end ) as voucher_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then voucher_amt_succ else 0 end ) as voucher_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then voucher_amt_succ else 0 end ) as voucher_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then voucher_amt_succ else 0 end ) as voucher_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then voucher_amt_err else 0 end ) as voucher_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then voucher_amt_err else 0  end ) as voucher_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then voucher_amt_err else 0 end ) as voucher_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then voucher_amt_err else 0 end ) as voucher_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then voucher_amt_err else 0 end ) as voucher_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then voucher_amt_total else 0 end ) as voucher_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then voucher_amt_total else 0 end ) as voucher_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then voucher_amt_total else 0 end ) as voucher_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then voucher_amt_total else 0 end ) as voucher_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then voucher_amt_total else 0 end ) as voucher_amt_total_180d,

--------------------WALLET_TOPUP AGGREGATES-----------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then wallet_topup_txn_succ else 0 end ) as wallet_topup_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then wallet_topup_txn_succ else 0  end ) as wallet_topup_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then wallet_topup_txn_succ else 0  end ) as wallet_topup_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then wallet_topup_txn_succ else 0  end ) as wallet_topup_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then wallet_topup_txn_succ else 0  end ) as wallet_topup_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then wallet_topup_txn_err else 0  end ) as wallet_topup_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then wallet_topup_txn_err  else 0 end ) as wallet_topup_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then wallet_topup_txn_err else 0  end ) as wallet_topup_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then wallet_topup_txn_err else 0  end ) as wallet_topup_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then wallet_topup_txn_err else 0  end ) as wallet_topup_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then wallet_topup_txn_total else 0  end ) as wallet_topup_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then wallet_topup_txn_total else 0  end ) as wallet_topup_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then wallet_topup_txn_total else 0  end ) as wallet_topup_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then wallet_topup_txn_total else 0 end ) as wallet_topup_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then wallet_topup_txn_total else 0 end ) as wallet_topup_txn_total_180d,


SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then wallet_topup_amt_succ else 0  end ) as wallet_topup_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then wallet_topup_amt_succ else 0  end ) as wallet_topup_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then wallet_topup_amt_succ else 0 end ) as wallet_topup_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then wallet_topup_amt_succ else 0 end ) as wallet_topup_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then wallet_topup_amt_succ else 0 end ) as wallet_topup_amt_succ_180d,


SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then wallet_topup_amt_err else 0 end ) as wallet_topup_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then wallet_topup_amt_err else 0  end ) as wallet_topup_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then wallet_topup_amt_err else 0 end ) as wallet_topup_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then wallet_topup_amt_err else 0 end ) as wallet_topup_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then wallet_topup_amt_err else 0 end ) as wallet_topup_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then wallet_topup_amt_total else 0 end ) as wallet_topup_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then wallet_topup_amt_total else 0 end ) as wallet_topup_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then wallet_topup_amt_total else 0 end ) as wallet_topup_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then wallet_topup_amt_total else 0 end ) as wallet_topup_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then wallet_topup_amt_total else 0 end ) as wallet_topup_amt_total_180d,

-----------------CARD PAYYMENT AGGREGATES-------------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppcardpayment_txn_succ else 0 end ) as ppcardpayment_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppcardpayment_txn_succ else 0  end ) as ppcardpayment_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppcardpayment_txn_succ else 0  end ) as ppcardpayment_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppcardpayment_txn_succ else 0  end ) as ppcardpayment_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppcardpayment_txn_succ else 0  end ) as ppcardpayment_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppcardpayment_txn_err else 0  end ) as ppcardpayment_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppcardpayment_txn_err  else 0 end ) as ppcardpayment_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppcardpayment_txn_err else 0  end ) as ppcardpayment_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppcardpayment_txn_err else 0  end ) as ppcardpayment_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppcardpayment_txn_err else 0  end ) as ppcardpayment_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppcardpayment_txn_total else 0  end ) as ppcardpayment_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppcardpayment_txn_total else 0  end ) as ppcardpayment_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppcardpayment_txn_total else 0  end ) as ppcardpayment_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppcardpayment_txn_total else 0 end ) as ppcardpayment_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppcardpayment_txn_total else 0 end ) as ppcardpayment_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppcardpayment_amt_succ else 0  end ) as ppcardpayment_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppcardpayment_amt_succ else 0  end ) as ppcardpayment_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppcardpayment_amt_succ else 0 end ) as ppcardpayment_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppcardpayment_amt_succ else 0 end ) as ppcardpayment_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppcardpayment_amt_succ else 0 end ) as ppcardpayment_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppcardpayment_amt_err else 0 end ) as ppcardpayment_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppcardpayment_amt_err else 0  end ) as ppcardpayment_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppcardpayment_amt_err else 0 end ) as ppcardpayment_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppcardpayment_amt_err else 0 end ) as ppcardpayment_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppcardpayment_amt_err else 0 end ) as ppcardpayment_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then ppcardpayment_amt_total else 0 end ) as ppcardpayment_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then ppcardpayment_amt_total else 0 end ) as ppcardpayment_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then ppcardpayment_amt_total else 0 end ) as ppcardpayment_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then ppcardpayment_amt_total else 0 end ) as ppcardpayment_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then ppcardpayment_amt_total else 0 end ) as ppcardpayment_amt_total_180d,

-------------------------------- RENT PAYMENT AGGREGATES----------------------------
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) then rent_txn_succ else 0 end ) as rent_txn_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) then rent_txn_succ else 0  end ) as rent_txn_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) then rent_txn_succ else 0  end ) as rent_txn_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) then rent_txn_succ else 0  end ) as rent_txn_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) then rent_txn_succ else 0  end ) as rent_txn_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) THEN rent_txn_err else 0  end ) as rent_txn_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) THEN rent_txn_err  else 0 end ) as rent_txn_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) THEN rent_txn_err else 0  end ) as rent_txn_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) THEN rent_txn_err else 0  end ) as rent_txn_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) THEN rent_txn_err else 0  end ) as rent_txn_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) THEN rent_txn_total else 0  end ) as rent_txn_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) THEN rent_txn_total else 0  end ) as rent_txn_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) THEN rent_txn_total else 0  end ) as rent_txn_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) THEN rent_txn_total else 0 end ) as rent_txn_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) THEN rent_txn_total else 0 end ) as rent_txn_total_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) THEN rent_amt_succ else 0  end ) as rent_amt_succ_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) THEN rent_amt_succ else 0  end ) as rent_amt_succ_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) THEN rent_amt_succ else 0 end ) as rent_amt_succ_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) THEN rent_amt_succ else 0 end ) as rent_amt_succ_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) THEN rent_amt_succ else 0 end ) as rent_amt_succ_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) THEN rent_amt_err else 0 end ) as rent_amt_err_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) THEN rent_amt_err else 0  end ) as rent_amt_err_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) THEN rent_amt_err else 0 end ) as rent_amt_err_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) THEN rent_amt_err else 0 end ) as rent_amt_err_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) THEN rent_amt_err else 0 end ) as rent_amt_err_180d,

SUM(CASE WHEN updated_date >= date_sub('2022-01-22',7) THEN rent_amt_total else 0 end ) as rent_amt_total_7d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',30) THEN rent_amt_total else 0 end ) as rent_amt_total_30d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',60) THEN rent_amt_total else 0 end ) as rent_amt_total_60d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',90) THEN rent_amt_total else 0 end ) as rent_amt_total_90d,
SUM(CASE WHEN updated_date >= date_sub('2022-01-22',180) THEN rent_amt_total else 0 end ) as rent_amt_total_180d

FROM
    (SELECT * FROM fraud.fra_consumer_category_master WHERE updated_date >= date_sub('2022-01-22',180))td
LEFT JOIN 
    (SELECT user_id, registration_date, total_txns, first_txn_date, last_txn_date, first_upi_txn_date, first_money_received_date 
    FROM edw_shared.user_dimension)ud 
ON td.senderuserid = ud.user_id
GROUP BY senderuserid, registration_date, total_txns, first_txn_date, last_txn_date, first_upi_txn_date, first_money_received_date