#!/bin/bash echo -e " \n----------- ACCEPTING NUMBER OF DAYS BEFORE THE RUN DATE FOR WHICH THE REPORT IS TO BE RUN --------------\n" backDay=$1 echo $backDay echo -e " \n------------- DATE IN THE REQUIRED FORMAT --------------\n" reportDate=$(date -d"$backDay day ago" "+%Y-%m-%d") date echo 'reportDate'=$reportDate echo " DROP TABLE raena_analytics.challenge_user_wise_rewards; CREATE TABLE raena_analytics.challenge_user_wise_rewards AS SELECT challenge_id, challenge_name, challenge_type, challenge_status, CC.name , CC.email, CC.mobile, rnk FROM (SELECT C.challenge_id, challenge_name, challenge_type, challenge_status, reseller_id , current_progress, rank()over(partition BY C.challenge_id ORDER BY current_progress DESC ,gross_profit DESC,total_revenue DESC ,randomness DESC) rnk FROM (SELECT DISTINCT challengeid challenge_id , name challenge_name, TYPE challenge_type, status challenge_status FROM raena_analytics_challenge_management.challenge_management_main cm ) D LEFT JOIN (SELECT * FROM (SELECT reseller_id, challenge_id , current_progress, gross_profit, total_revenue, randomness, row_number() over (partition BY reseller_id,challenge_id ORDER BY updated_at DESC) rn FROM raena_challenge_management.rank_progress ) A WHERE rn= 1) C ON C.challenge_id = D.challenge_id ORDER BY C.challenge_id , rnk, reseller_id) AA LEFT JOIN raena_user_management.user CC ON AA.reseller_id = cast(CC.id AS varchar) ORDER BY challenge_id, rnk; --------- DROP TABLE raena_analytics.cohort_level_reseller; CREATE TABLE raena_analytics.cohort_level_reseller AS SELECT reseller_id , CASE WHEN max_payment_amount BETWEEN 0 AND 2000000 THEN '0to2M' WHEN max_payment_amount BETWEEN 2000000 AND 10000000 THEN '2Mto10M' WHEN max_payment_amount > 10000000 THEN '>10M' END Bucket FROM (SELECT reseller_id , max(payment_amount) max_payment_amount FROM (SELECT reseller_id , date_trunc('Month',created_at)::date trnsaction_mount, sum(payment_amount)payment_amount FROM raena_order_management.order GROUP BY 1, 2) A GROUP BY 1) B ; drop table raena_analytics.challenge_brand_sku_mapping; create table raena_analytics.challenge_brand_sku_mapping as select distinct A.challengeid challenge_id , A.name challenge_name , A.type challenge_type, A.status challenge_status, cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date, cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date , cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date, brandid ,productsku, reseller_id from raena_analytics_challenge_management.challenge_management_main A left join raena_analytics_challenge_management.participatingbrands AA on A.challengeid = AA.challengeid LEFT JOIN (SELECT DISTINCT challenge_id , reseller_id FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id; drop table raena_analytics.reseller_revenue_gamification; create table raena_analytics.reseller_revenue_gamification as SELECT order_date, order_id , reseller_id , order_placed_by, sku, brand_id, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity, sum(shipping_amount) shipping_amount FROM (SELECT order_date, A.order_id , reseller_id , shipment_id, case when A.order_id = C.order_id then C.new_shipment_amount else D.new_shipment_amount end shipping_amount, A.sku, A.brand_id, CASE WHEN order_placed_by='merchant' THEN 'Merchant' WHEN order_placed_by='admin' THEN 'Admin Panel' WHEN order_placed_by='reseller' THEN 'App' END order_placed_by, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger A LEFT JOIN (SELECT DISTINCT id , created_at, order_placed_by FROM raena_order_management.order WHERE created_at::date >='2023-11-01') B ON A.order_id = B.id LEFT JOIN raena_analytics.sku_level_shipping_fee_final C on A.order_id = C.order_id and A.sku = C.sku left join raena_analytics.sku_level_shipping_fee_old_final D on A.order_id = D.external_id and A.sku = D.sku WHERE status NOT IN ('CANCELLED', 'RETURNED') and order_date >='2023-11-03' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8)c GROUP BY 1, 2, 3, 4,5,6; DROP TABLE raena_analytics.challenge_rewards_data; CREATE TABLE raena_analytics.challenge_rewards_data AS SELECT distinct A.challenge_id, B.name AS challenge_name , B.type AS challenge_type, A.type AS reward_type , A.name AS reward_name, A.reseller_id, A.status rewards_status, C.name reseller_name , C.mobile , C.email , challenge_target, value AS total_rewards, json_extract_path_text(json_extract_path_text(claim_meta,'addressDetails',TRUE),'name',TRUE) address_reseller_name , json_extract_path_text(json_extract_path_text(claim_meta,'addressDetails',TRUE),'address',TRUE) reseller_address , json_extract_path_text(json_extract_path_text(claim_meta,'addressDetails',TRUE),'phoneNumber',TRUE) reseller_phonenumber , json_extract_path_text(json_extract_path_text(claim_meta,'bankDetails',TRUE),'bankId',TRUE) reseller_bankid , json_extract_path_text(json_extract_path_text(claim_meta,'bankDetails',TRUE),'bankCode',TRUE) reseller_code , json_extract_path_text(json_extract_path_text(claim_meta,'bankDetails',TRUE),'bankAccount',TRUE) reseller_account FROM raena_challenge_management.challenge_reward_status A LEFT JOIN raena_analytics_challenge_management.challenge_management_main B ON A.challenge_id = B.challengeid LEFT JOIN raena_user_management.user C ON A.reseller_id = C.id ; drop table raena_analytics.challenge_rewards_base_table; create table raena_analytics.challenge_rewards_base_table as SELECT challenge_id , reseller_id , sum(total_rewards) rewards FROM (select * from raena_analytics.challenge_rewards_data where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D GROUP BY 1, 2; drop table raena_analytics.gamification_metrics_base_table_statge1_new; CREATE TABLE raena_analytics.gamification_metrics_base_table_statge1_new AS SELECT A.challenge_id , A.challenge_name , A.challenge_type, A.challenge_status, A.challenge_start_date, A.challenge_end_date , A.challenge_created_date, A.reseller_id, coalesce(cast(C.order_date::TIMESTAMP+interval'7 hours'AS date),cast(CC.order_date::TIMESTAMP+interval'7 hours'AS date)) transaction_date, coalesce(C.order_id,CC.order_id) order_id , A.brandid , A.productsku, coalesce(C.amount,CC.amount) amount, coalesce(C.cogs_amount,CC.cogs_amount) cogs_amount, coalesce(C.quantity,CC.quantity) quantity, D.rewards, coalesce(C.shipping_amount,CC.shipping_amount) new_shipment_amount, --case when C.order_id = E.order_id then E.new_shipment_amount else EE.new_shipment_amount end new_shipment_amount, F.name AS reseller_name , F.email AS reseller_email, F.mobile AS reseller_mobile, G.bucket, coalesce(C.order_placed_by,CC.order_placed_by) order_placed_by FROM raena_analytics.challenge_brand_sku_mapping A LEFT JOIN raena_analytics.reseller_revenue_gamification C ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.challenge_start_date AND A.challenge_end_date AND A.reseller_id = C.reseller_id and A.brandid =C.brand_id and (A.productsku is not null or A.productsku ='') LEFT JOIN raena_analytics.reseller_revenue_gamification CC ON CC.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.challenge_start_date AND A.challenge_end_date AND A.reseller_id = CC.reseller_id and A.productsku = CC.sku and (A.productsku is not null or A.productsku ='') LEFT JOIN raena_analytics.challenge_rewards_base_table D ON A.challenge_id = D.challenge_id AND A.reseller_id = D.reseller_id AND C.reseller_id = D.reseller_id LEFT JOIN raena_user_management.user F ON A.reseller_id = cast(F.id AS varchar) LEFT JOIN raena_analytics.cohort_level_reseller G ON A.reseller_id = G.reseller_id WHERE challenge_type <> 'MILESTONE' UNION SELECT A.challengeid challenge_id , A.name challenge_name , A.type challenge_type, A.status challenge_status, cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date, cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date , cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date, B.reseller_id, cast(C.order_date::TIMESTAMP+interval'7 hours'AS date) transaction_date, '', '', '', 0 amount, 0 Cogs_amount, 0 quantity, D.rewards, 0 , '' , '', '', G.bucket, '' FROM raena_analytics_challenge_management.challenge_management_main A LEFT JOIN (SELECT DISTINCT reseller_id FROM raena_challenge_management.challenge_participant UNION SELECT DISTINCT reseller_id FROM raena_challenge_management.order_ledger) b ON 1=1 LEFT JOIN (SELECT order_date, order_id , reseller_id , sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger GROUP BY 1, 2, 3)c ON b.reseller_id = c.reseller_id INNER JOIN (SELECT challenge_id , reseller_id , sum(total_rewards) rewards FROM (select * from raena_analytics.challenge_rewards_data where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D GROUP BY 1, 2) D ON A.challengeid = D.challenge_id AND B.reseller_id = D.reseller_id LEFT JOIN raena_user_management.user F ON B.reseller_id = cast(F.id AS varchar) LEFT JOIN raena_analytics.cohort_level_reseller G ON B.reseller_id = G.reseller_id WHERE A.type = 'MILESTONE' ORDER BY 1, challenge_start_date; DROP TABLE raena_analytics.gamification_metrics_base_table_statge1; CREATE TABLE raena_analytics.gamification_metrics_base_table_statge1 AS SELECT A.challengeid challenge_id , A.name challenge_name , A.type challenge_type, A.status challenge_status, cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date, cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date , cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date, B.reseller_id, cast(C.order_date::TIMESTAMP+interval'7 hours'AS date) transaction_date, C.order_id , C.amount, C.cogs_amount, C.quantity, D.rewards, shipping_amount new_shipment_amount, --case when C.order_id = E.order_id then E.new_shipment_amount else EE.new_shipment_amount end new_shipment_amount, F.name AS reseller_name , F.email AS reseller_email, F.mobile AS reseller_mobile, G.bucket, order_placed_by FROM raena_analytics_challenge_management.challenge_management_main A LEFT JOIN (SELECT DISTINCT challenge_id , reseller_id FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id LEFT JOIN (SELECT order_date, order_id , reseller_id , order_placed_by, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity, sum(shipping_amount) shipping_amount FROM (SELECT order_date, order_id , reseller_id , shipment_id, shipping_amount, CASE WHEN order_placed_by='merchant' THEN 'Merchant' WHEN order_placed_by='admin' THEN 'Admin Panel' WHEN order_placed_by='reseller' THEN 'App' END order_placed_by, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger A LEFT JOIN (SELECT DISTINCT id , created_at, order_placed_by FROM raena_order_management.order WHERE created_at::date >='2023-10-01') B ON A.order_id = B.id LEFT JOIN (SELECT DISTINCT id , shipping_amount FROM raena_order_management.sales_sub_order_shipment WHERE status NOT IN ('CANCELLED', 'RETURNED')) C ON A.shipment_id=C.id WHERE status NOT IN ('CANCELLED', 'RETURNED') GROUP BY 1, 2, 3, 4, 5, 6)c GROUP BY 1, 2, 3, 4) C ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.startdate::TIMESTAMP+interval'7 hours' AND (A.enddate::TIMESTAMP+interval'7 hours') AND B.reseller_id = C.reseller_id LEFT JOIN (SELECT challenge_id , reseller_id , sum(total_rewards) rewards FROM (select * from raena_analytics.challenge_rewards_data where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D GROUP BY 1, 2) D ON A.challengeid = D.challenge_id AND B.reseller_id = D.reseller_id AND C.reseller_id = D.reseller_id LEFT JOIN raena_user_management.user F ON B.reseller_id = cast(F.id AS varchar) LEFT JOIN raena_analytics.cohort_level_reseller G ON B.reseller_id = G.reseller_id WHERE A.type <> 'MILESTONE' UNION SELECT A.challengeid challenge_id , A.name challenge_name , A.type challenge_type, A.status challenge_status, cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date, cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date , cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date, B.reseller_id, cast(C.order_date::TIMESTAMP+interval'7 hours'AS date) transaction_date, '' , 0 amount, 0 Cogs_amount, 0 quantity, D.rewards, 0 , '' , '', '', G.bucket, '' FROM raena_analytics_challenge_management.challenge_management_main A LEFT JOIN (SELECT DISTINCT reseller_id FROM raena_challenge_management.challenge_participant UNION SELECT DISTINCT reseller_id FROM raena_challenge_management.order_ledger) b ON 1=1 LEFT JOIN (SELECT order_date, order_id , reseller_id , sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger GROUP BY 1, 2, 3)c ON b.reseller_id = c.reseller_id INNER JOIN (SELECT challenge_id , reseller_id , sum(total_rewards) rewards FROM (select * from raena_analytics.challenge_rewards_data where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D GROUP BY 1, 2) D ON A.challengeid = D.challenge_id AND B.reseller_id = D.reseller_id LEFT JOIN raena_user_management.user F ON B.reseller_id = cast(F.id AS varchar) LEFT JOIN raena_analytics.cohort_level_reseller G ON B.reseller_id = G.reseller_id WHERE A.type = 'MILESTONE' ORDER BY 1, challenge_start_date; DROP TABLE raena_analytics.challenge_type_retention; CREATE TABLE raena_analytics.challenge_type_retention AS SELECT A.challenge_type, min_order_date AS first_transaction_date, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6 FROM (SELECT challenge_type, reseller_id , min(date_trunc('Month',transaction_date)::date) min_order_date FROM raena_analytics.gamification_metrics_base_table_statge1_new WHERE order_id IS NOT NULL AND reseller_id IN (SELECT DISTINCT C.id FROM raena_analytics.charged_events_gamification_direct_revenue A LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','') WHERE challenge_detail_page>0 AND view_item_session>0 AND begin_checkout_session>0 AND finish_checkout_session>0 AND confirm_payment_session>0 AND charged>0) GROUP BY 1, 2) A LEFT JOIN (SELECT DISTINCT challenge_type, reseller_id , date_trunc('Month',transaction_date)::date order_date FROM raena_analytics.gamification_metrics_base_table_statge1_new WHERE order_id IS NOT NULL AND reseller_id IN (SELECT DISTINCT C.id FROM raena_analytics.charged_events_gamification_direct_revenue A LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','') WHERE challenge_detail_page>0 AND view_item_session>0 AND begin_checkout_session>0 AND finish_checkout_session>0 AND confirm_payment_session>0 AND charged>0)) B ON A.reseller_id = B.reseller_id AND A.challenge_type = B.challenge_type GROUP BY 1 , 2; drop table raena_analytics.challenge_cohort_type_retention; CREATE TABLE raena_analytics.challenge_cohort_type_retention AS SELECT A.challenge_type, bucket, min_order_date AS first_transaction_date, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6 FROM (SELECT challenge_type, reseller_id , min(date_trunc('Month',transaction_date)::date) min_order_date FROM raena_analytics.gamification_metrics_base_table_statge1_new WHERE order_id IS NOT NULL AND reseller_id IN (SELECT DISTINCT C.id FROM raena_analytics.charged_events_gamification_direct_revenue A LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','') WHERE challenge_detail_page>0 AND view_item_session>0 AND begin_checkout_session>0 AND finish_checkout_session>0 AND confirm_payment_session>0 AND charged>0) GROUP BY 1, 2) A LEFT JOIN (SELECT DISTINCT challenge_type, reseller_id , date_trunc('Month',transaction_date)::date order_date FROM raena_analytics.gamification_metrics_base_table_statge1_new WHERE order_id IS NOT NULL AND reseller_id IN (SELECT DISTINCT C.id FROM raena_analytics.charged_events_gamification_direct_revenue A LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','') WHERE challenge_detail_page>0 AND view_item_session>0 AND begin_checkout_session>0 AND finish_checkout_session>0 AND confirm_payment_session>0 AND charged>0)) B ON A.reseller_id = B.reseller_id AND A.challenge_type = B.challenge_type LEFT JOIN (SELECT * FROM raena_analytics.cohort_level_reseller) C ON A.reseller_id= C.reseller_id GROUP BY 1, 2, 3; /* DROP TABLE raena_analytics.challenge_type_retention; CREATE TABLE raena_analytics.challenge_type_retention AS SELECT challenge_type, min_order_date AS first_transaction_date, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6 FROM (SELECT TYPE challenge_type, B.reseller_id , min(date_trunc('Month',order_date)::date) min_order_date FROM raena_analytics_challenge_management.challenge_management_main A LEFT JOIN (SELECT DISTINCT challenge_id , reseller_id FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id inner JOIN (SELECT order_date, order_id , reseller_id , status order_status, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger GROUP BY 1, 2, 3, 4)c ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.startdate::TIMESTAMP+interval'7 hours' AND (A.enddate::TIMESTAMP+interval'7 hours') AND B.reseller_id = C.reseller_id WHERE TYPE <> 'MILESTONE' GROUP BY 1, 2 UNION SELECT TYPE challenge_type, B.reseller_id , min(date_trunc('Month',order_date)::date) min_order_date FROM raena_analytics_challenge_management.challenge_management_main A LEFT JOIN (SELECT DISTINCT reseller_id FROM raena_challenge_management.challenge_participant) b ON 1=1 inner JOIN (SELECT order_date, order_id , reseller_id , status order_status, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger GROUP BY 1, 2, 3, 4)c ON b.reseller_id = c.reseller_id WHERE TYPE = 'MILESTONE' GROUP BY 1, 2) A LEFT JOIN (SELECT DISTINCT date_trunc('Month',order_date)::date order_date, reseller_id FROM raena_challenge_management.order_ledger A ) B ON A.reseller_id = B.reseller_id GROUP BY 1 , 2; drop table raena_analytics.challenge_cohort_type_retention; CREATE TABLE raena_analytics.challenge_cohort_type_retention AS SELECT challenge_type, bucket, min_order_date AS first_transaction_date, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6, count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6 FROM (SELECT TYPE challenge_type, B.reseller_id , min(date_trunc('Month',order_date)::date) min_order_date FROM raena_analytics_challenge_management.challenge_management_main A LEFT JOIN (SELECT DISTINCT challenge_id , reseller_id FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id inner JOIN (SELECT order_date, order_id , reseller_id , status order_status, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger GROUP BY 1, 2, 3, 4)c ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.startdate::TIMESTAMP+interval'7 hours' AND (A.enddate::TIMESTAMP+interval'7 hours') AND B.reseller_id = C.reseller_id WHERE TYPE <> 'MILESTONE' GROUP BY 1, 2 UNION SELECT TYPE challenge_type, B.reseller_id , min(date_trunc('Month',order_date)::date) min_order_date FROM raena_analytics_challenge_management.challenge_management_main A LEFT JOIN (SELECT DISTINCT reseller_id FROM raena_challenge_management.challenge_participant) b ON 1=1 inner JOIN (SELECT order_date, order_id , reseller_id , status order_status, sum(amount) amount , sum(cogs_amount) cogs_amount, sum(quantity) quantity FROM raena_challenge_management.order_ledger GROUP BY 1, 2, 3, 4)c ON b.reseller_id = c.reseller_id WHERE TYPE = 'MILESTONE' GROUP BY 1, 2) A LEFT JOIN (SELECT DISTINCT date_trunc('Month',order_date)::date order_date, reseller_id FROM raena_challenge_management.order_ledger A ) B ON A.reseller_id = B.reseller_id LEFT JOIN (SELECT * FROM raena_analytics.cohort_level_reseller) C ON A.reseller_id= C.reseller_id GROUP BY 1, 2, 3; */ DROP TABLE IF EXISTS raena_analytics.gamification_funnel_base_stage1; CREATE TABLE raena_analytics.gamification_funnel_base_stage1 AS SELECT ts::date , events, name , email , phone, json_extract_path_text(event_props,'CT Session Id',TRUE) sessionid , json_extract_path_text(event_props,'page_name',TRUE) page_name , json_extract_path_text(event_props,'ProductList',TRUE) brand_list FROM clevertap.clevertap_master_data WHERE events IN ('load_challenge_home_banner' , 'click_on_challenge_home_banner', 'bottom_bar_challenge', 'click_on_challenge_home_banner', 'page_load', 'view_item', 'begin_checkout', 'Charged', 'view_cart', 'add_to_cart', 'confirm_payment', 'finish_checkout', 'click_on_milestone') AND ts::date >='2023-11-07'; DROP TABLE raena_analytics.gamification_funnel_base_stage2; CREATE TABLE raena_analytics.gamification_funnel_base_stage2 AS SELECT ts::date , sessionid, name , email , phone, count(DISTINCT CASE WHEN events ='page_load' AND page_name ='Home' THEN sessionid END) home_banner_session, count(DISTINCT CASE WHEN events ='click_on_challenge_home_banner' THEN sessionid END) click_challenge_home_banner_session, count(DISTINCT CASE WHEN events ='bottom_bar_challenge' THEN sessionid END) bottom_bar_challenge, count(distinct case when events ='click_on_milestone' then sessionid end) load_challenge_milestone_page, count(DISTINCT CASE WHEN events ='page_load' AND page_name ='Challenges' THEN sessionid END) challenge_home_page, count(DISTINCT CASE WHEN events ='page_load' AND page_name ='challenge-details' THEN sessionid END) challenge_detail_page, count(DISTINCT CASE WHEN events ='page_load' AND page_name ='ProductList' THEN sessionid END) brand_page, count(DISTINCT CASE WHEN events ='view_item' THEN sessionid END) view_item_session, count(DISTINCT CASE WHEN events ='view_cart' THEN sessionid END)view_cart_session, count(DISTINCT CASE WHEN events ='begin_checkout' THEN sessionid END) begin_checkout_session, count(DISTINCT CASE WHEN events ='finish_checkout' THEN sessionid END) finish_checkout_session, count(DISTINCT CASE WHEN events ='confirm_payment' THEN sessionid END) confirm_payment_session FROM raena_analytics.gamification_funnel_base_stage1 GROUP BY 1, 2, 3, 4, 5; DROP TABLE raena_analytics.gamification_funnel_base_stage3; CREATE TABLE raena_analytics.gamification_funnel_base_stage3 AS SELECT ts::date , sessionid, name , email , phone, count(DISTINCT CASE WHEN events ='Charged' THEN sessionid END) Charged FROM raena_analytics.gamification_funnel_base_stage1 WHERE ts::date >='2023-11-07' GROUP BY 1, 2, 3, 4, 5; DROP TABLE IF EXISTS raena_analytics.gamification_funnel_base_stage1_direct_revenue; CREATE TABLE raena_analytics.gamification_funnel_base_stage1_direct_revenue AS SELECT ts::date , events, name , email , phone, json_extract_path_text(event_props,'CT Session Id',TRUE) sessionid , json_extract_path_text(event_props,'challengeId',TRUE) challengeid FROM clevertap.clevertap_master_data WHERE events ='page_load' and json_extract_path_text(event_props,'page_name',TRUE) ='challenge-details' AND ts::date >='2023-11-07'; DROP TABLE IF EXISTS raena_analytics.gamification_funnel_base_stage2_direct_revenue; CREATE TABLE raena_analytics.gamification_funnel_base_stage2_direct_revenue AS SELECT ts::date , events, name , email , phone, json_extract_path_text(event_props,'CT Session Id',TRUE) sessionid , json_extract_path_text(event_props,'Payment ID',TRUE) payment_id FROM clevertap.clevertap_master_data WHERE events IN ('view_item', 'begin_checkout', 'Charged', 'confirm_payment', 'finish_checkout') AND ts::date >='2023-11-07'; drop table raena_analytics.gamification_funnel_base_stage3_direct_revenue; CREATE TABLE raena_analytics.gamification_funnel_base_stage3_direct_revenue AS SELECT ts::date , sessionid, name , email , phone, count(DISTINCT CASE WHEN events ='view_item' THEN sessionid END) view_item_session, count(DISTINCT CASE WHEN events ='begin_checkout' THEN sessionid END) begin_checkout_session, count(DISTINCT CASE WHEN events ='finish_checkout' THEN sessionid END) finish_checkout_session, count(DISTINCT CASE WHEN events ='confirm_payment' THEN sessionid END) confirm_payment_session FROM raena_analytics.gamification_funnel_base_stage2_direct_revenue GROUP BY 1, 2, 3, 4, 5; DROP TABLE raena_analytics.gamification_funnel_base_stage4_direct_revenue; CREATE TABLE raena_analytics.gamification_funnel_base_stage4_direct_revenue AS SELECT ts::date , sessionid, name , email , phone, payment_id, count(DISTINCT CASE WHEN events ='Charged' THEN sessionid END) Charged, order_id FROM raena_analytics.gamification_funnel_base_stage2_direct_revenue A left join raena_analytics.payment_order B on A.payment_id = B.id WHERE events = 'Charged' GROUP BY 1, 2, 3, 4, 5,6,8; DROP TABLE IF EXISTS raena_analytics.charged_events_gamification_direct_revenue; CREATE TABLE raena_analytics.charged_events_gamification_direct_revenue AS SELECT A.ts, A.phone, A.challengeid, count(DISTINCT A.sessionid) challenge_detail_page, count(DISTINCT CASE WHEN view_item_session>0 THEN A.sessionid END) view_item_session, count(DISTINCT CASE WHEN begin_checkout_session>0 THEN A.sessionid END) begin_checkout_session, count(DISTINCT CASE WHEN finish_checkout_session>0 THEN A.sessionid END) finish_checkout_session , count(DISTINCT CASE WHEN confirm_payment_session>0 THEN A.sessionid END) confirm_payment_session, count(DISTINCT CASE WHEN Charged>0 THEN A.sessionid END) charged FROM raena_analytics.gamification_funnel_base_stage1_direct_revenue A LEFT JOIN raena_analytics.gamification_funnel_base_stage3_direct_revenue B ON A.sessionid = B.sessionid LEFT JOIN (SELECT DISTINCT A.order_id , phone , charged , challenge_id FROM raena_analytics.gamification_funnel_base_stage4_direct_revenue A INNER JOIN raena_analytics.gamification_metrics_base_table_statge1_new B ON A.order_id = B.order_id) c ON B.phone = C.phone AND A.challengeid = C.challenge_id GROUP BY 1, 2, 3; " > /home/ec2-user/cronjob/redshift/sql_code/gamification_etl.sql psql "host=redshift-cluster-1.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/redshift/sql_code/gamification_etl.sql