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") echo 'reportDate'=$reportDate echo " drop table if exists raena_analytics.homepage_base_stage1_v1; create table raena_analytics.homepage_base_stage1_v1 as select * FROM clevertap.clevertap_master_data cmd WHERE events IN ('home_stories', 'home_banner', 'home_category', 'videofeed_videoclick', 'home_brand_grid_view', 'home_brand_Caraousel', 'home_product_Caraousel', 'home_product_infinite') and ts::date >= '$reportDate' ; delete from raena_analytics.homepage_base_stage1 where ts::date >= '$reportDate'; insert into raena_analytics.homepage_base_stage1 SELECT ts, events, name , email, phone, objectid, json_extract_path_text(profiledata,'tier',true) tier, json_extract_path_text(profiledata,'isguestuser',true) isguestuser, split_part(json_extract_path_text(replace(event_props,'\\\xa0',' '),'url' ,true),'/',2) collection, split_part(json_extract_path_text(replace(event_props,'\\\xa0',' '),'url',true) ,'/',3) collection_id, json_extract_path_text(replace(event_props,'\\\xa0',' '),'userId',true) userId, json_extract_path_text(replace(event_props,'\\\xa0',' '),'sectionName',true) sectionName, json_extract_path_text(replace(event_props,'\\\xa0',' '),'guest_user',true) guest_user, json_extract_path_text(replace(event_props,'\\\xa0',' '),'tier',true) tier_id, json_extract_path_text(replace(event_props,'\\\xa0',' '),'categoryName',true) categoryName, json_extract_path_text(replace(event_props,'\\\xa0',' '),'brand_code',true) brand_code, json_extract_path_text(replace(event_props,'\\\xa0',' '),'brand_name',true) brand_name, case when events ='home_product_infinite' then json_extract_path_text(replace(event_props,'\\\xa0',' '),'brand',true) end brand_id, case when events ='home_product_Caraousel' then json_extract_path_text(replace(event_props,'\\\xa0',' '),'brand',true) end brand, json_extract_path_text(replace(event_props,'\\\xa0',' '),'productId',true) productId, json_extract_path_text(replace(event_props,'\\\xa0',' '),'videoId',true) videoId, json_extract_path_text(replace(event_props,'\\\xa0',' '),'pageName',true) pageName, json_extract_path_text(replace(event_props,'\\\xa0',' '),'SKU_code',true) SKU_code FROM raena_analytics.homepage_base_stage1_v1; drop table if exists raena_analytics.homepage_base; create table raena_analytics.homepage_base AS SELECT 'Day' Frequency, date_trunc('day',ts)::date AS created_date, coalesce(tier,C.name) tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, coalesce(isguestuser,guest_user) guest_user, collection, coalesce(F.name,categoryName)categoryName, coalesce(E.name,D.name,brand_name,brand) brand_name, videoId, pageName, SKU_code, count(CASE WHEN events = 'home_banner' THEN events END) total_home_banner_event, count(DISTINCT CASE WHEN events = 'home_banner' THEN phone END) total_home_banner_user, count(CASE WHEN events = 'home_stories' THEN events END) total_home_stories_event, count(DISTINCT CASE WHEN events = 'home_stories' THEN phone END) total_home_stories_user, count(CASE WHEN events = 'home_category' THEN events END) total_home_category_event, count(DISTINCT CASE WHEN events = 'home_category' THEN phone END) total_home_category_user, count(CASE WHEN events = 'home_brand_grid_view' THEN events END) total_home_brand_grid_view_event, count(DISTINCT CASE WHEN events = 'home_brand_grid_view' THEN phone END) total_home_brand_grid_view_user, count(CASE WHEN events = 'videofeed_videoclick' THEN events END) total_videofeed_videoclick_event, count(DISTINCT CASE WHEN events = 'videofeed_videoclick' THEN phone END) total_videofeed_videoclick_user, count(CASE WHEN events = 'home_brand_Caraousel' THEN events END) total_home_brand_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_brand_Caraousel' THEN phone END) total_home_brand_Caraousel_user, count(CASE WHEN events = 'home_product_Caraousel' THEN events END) total_home_product_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_product_Caraousel' THEN phone END) total_home_product_Caraousel_user, count(CASE WHEN events = 'home_product_infinite' THEN events END) total_home_product_infinite_event, count(DISTINCT CASE WHEN events = 'home_product_infinite' THEN phone END) total_home_product_infinite_user FROM raena_analytics.homepage_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile LEFT JOIN raena_user_management.tier C ON tier_id= cast(C.id as varchar) LEFT JOIN raena_catalog_management.brand D ON brand_code=cast(D.id as varchar) LEFT JOIN raena_catalog_management.brand E ON collection_id= cast(E.id as varchar) LEFT JOIN raena_catalog_management.category F ON collection_id= cast(F.id as varchar) OR brand_id = cast(D.id as varchar) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 union SELECT 'Week' Frequency, date_trunc('Week',ts)::date AS created_date, coalesce(tier,C.name) tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, coalesce(isguestuser,guest_user) guest_user, collection, coalesce(F.name,categoryName)categoryName, coalesce(E.name,D.name,brand_name,brand) brand_name, videoId, pageName, SKU_code, count(CASE WHEN events = 'home_banner' THEN events END) total_home_banner_event, count(DISTINCT CASE WHEN events = 'home_banner' THEN phone END) total_home_banner_user, count(CASE WHEN events = 'home_stories' THEN events END) total_home_stories_event, count(DISTINCT CASE WHEN events = 'home_stories' THEN phone END) total_home_stories_user, count(CASE WHEN events = 'home_category' THEN events END) total_home_category_event, count(DISTINCT CASE WHEN events = 'home_category' THEN phone END) total_home_category_user, count(CASE WHEN events = 'home_brand_grid_view' THEN events END) total_home_brand_grid_view_event, count(DISTINCT CASE WHEN events = 'home_brand_grid_view' THEN phone END) total_home_brand_grid_view_user, count(CASE WHEN events = 'videofeed_videoclick' THEN events END) total_videofeed_videoclick_event, count(DISTINCT CASE WHEN events = 'videofeed_videoclick' THEN phone END) total_videofeed_videoclick_user, count(CASE WHEN events = 'home_brand_Caraousel' THEN events END) total_home_brand_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_brand_Caraousel' THEN phone END) total_home_brand_Caraousel_user, count(CASE WHEN events = 'home_product_Caraousel' THEN events END) total_home_product_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_product_Caraousel' THEN phone END) total_home_product_Caraousel_user, count(CASE WHEN events = 'home_product_infinite' THEN events END) total_home_product_infinite_event, count(DISTINCT CASE WHEN events = 'home_product_infinite' THEN phone END) total_home_product_infinite_user FROM raena_analytics.homepage_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile LEFT JOIN raena_user_management.tier C ON tier_id= cast(C.id as varchar) LEFT JOIN raena_catalog_management.brand D ON brand_code=cast(D.id as varchar) LEFT JOIN raena_catalog_management.brand E ON collection_id= cast(E.id as varchar) LEFT JOIN raena_catalog_management.category F ON collection_id= cast(F.id as varchar) OR brand_id = cast(D.id as varchar) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 union SELECT 'Month' Frequency, date_trunc('Month',ts)::date AS created_date, coalesce(tier,C.name) tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, coalesce(isguestuser,guest_user) guest_user, collection, coalesce(F.name,categoryName)categoryName, coalesce(E.name,D.name,brand_name,brand) brand_name, videoId, pageName, SKU_code, count(CASE WHEN events = 'home_banner' THEN events END) total_home_banner_event, count(DISTINCT CASE WHEN events = 'home_banner' THEN phone END) total_home_banner_user, count(CASE WHEN events = 'home_stories' THEN events END) total_home_stories_event, count(DISTINCT CASE WHEN events = 'home_stories' THEN phone END) total_home_stories_user, count(CASE WHEN events = 'home_category' THEN events END) total_home_category_event, count(DISTINCT CASE WHEN events = 'home_category' THEN phone END) total_home_category_user, count(CASE WHEN events = 'home_brand_grid_view' THEN events END) total_home_brand_grid_view_event, count(DISTINCT CASE WHEN events = 'home_brand_grid_view' THEN phone END) total_home_brand_grid_view_user, count(CASE WHEN events = 'videofeed_videoclick' THEN events END) total_videofeed_videoclick_event, count(DISTINCT CASE WHEN events = 'videofeed_videoclick' THEN phone END) total_videofeed_videoclick_user, count(CASE WHEN events = 'home_brand_Caraousel' THEN events END) total_home_brand_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_brand_Caraousel' THEN phone END) total_home_brand_Caraousel_user, count(CASE WHEN events = 'home_product_Caraousel' THEN events END) total_home_product_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_product_Caraousel' THEN phone END) total_home_product_Caraousel_user, count(CASE WHEN events = 'home_product_infinite' THEN events END) total_home_product_infinite_event, count(DISTINCT CASE WHEN events = 'home_product_infinite' THEN phone END) total_home_product_infinite_user FROM raena_analytics.homepage_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile LEFT JOIN raena_user_management.tier C ON tier_id= cast(C.id as varchar) LEFT JOIN raena_catalog_management.brand D ON brand_code=cast(D.id as varchar) LEFT JOIN raena_catalog_management.brand E ON collection_id= cast(E.id as varchar) LEFT JOIN raena_catalog_management.category F ON collection_id= cast(F.id as varchar) OR brand_id = cast(D.id as varchar) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 union SELECT 'Quarter' Frequency, date_trunc('quarter',ts)::date AS created_date, coalesce(tier,C.name) tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, coalesce(isguestuser,guest_user) guest_user, collection, coalesce(F.name,categoryName)categoryName, coalesce(E.name,D.name,brand_name,brand) brand_name, videoId, pageName, SKU_code, count(CASE WHEN events = 'home_banner' THEN events END) total_home_banner_event, count(DISTINCT CASE WHEN events = 'home_banner' THEN phone END) total_home_banner_user, count(CASE WHEN events = 'home_stories' THEN events END) total_home_stories_event, count(DISTINCT CASE WHEN events = 'home_stories' THEN phone END) total_home_stories_user, count(CASE WHEN events = 'home_category' THEN events END) total_home_category_event, count(DISTINCT CASE WHEN events = 'home_category' THEN phone END) total_home_category_user, count(CASE WHEN events = 'home_brand_grid_view' THEN events END) total_home_brand_grid_view_event, count(DISTINCT CASE WHEN events = 'home_brand_grid_view' THEN phone END) total_home_brand_grid_view_user, count(CASE WHEN events = 'videofeed_videoclick' THEN events END) total_videofeed_videoclick_event, count(DISTINCT CASE WHEN events = 'videofeed_videoclick' THEN phone END) total_videofeed_videoclick_user, count(CASE WHEN events = 'home_brand_Caraousel' THEN events END) total_home_brand_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_brand_Caraousel' THEN phone END) total_home_brand_Caraousel_user, count(CASE WHEN events = 'home_product_Caraousel' THEN events END) total_home_product_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_product_Caraousel' THEN phone END) total_home_product_Caraousel_user, count(CASE WHEN events = 'home_product_infinite' THEN events END) total_home_product_infinite_event, count(DISTINCT CASE WHEN events = 'home_product_infinite' THEN phone END) total_home_product_infinite_user FROM raena_analytics.homepage_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile LEFT JOIN raena_user_management.tier C ON tier_id= cast(C.id as varchar) LEFT JOIN raena_catalog_management.brand D ON brand_code=cast(D.id as varchar) LEFT JOIN raena_catalog_management.brand E ON collection_id= cast(E.id as varchar) LEFT JOIN raena_catalog_management.category F ON collection_id= cast(F.id as varchar) OR brand_id = cast(D.id as varchar) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 union SELECT 'Year' Frequency, date_trunc('Year',ts)::date AS created_date, coalesce(tier,C.name) tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, coalesce(isguestuser,guest_user) guest_user, collection, coalesce(F.name,categoryName)categoryName, coalesce(E.name,D.name,brand_name,brand) brand_name, videoId, pageName, SKU_code, count(CASE WHEN events = 'home_banner' THEN events END) total_home_banner_event, count(DISTINCT CASE WHEN events = 'home_banner' THEN phone END) total_home_banner_user, count(CASE WHEN events = 'home_stories' THEN events END) total_home_stories_event, count(DISTINCT CASE WHEN events = 'home_stories' THEN phone END) total_home_stories_user, count(CASE WHEN events = 'home_category' THEN events END) total_home_category_event, count(DISTINCT CASE WHEN events = 'home_category' THEN phone END) total_home_category_user, count(CASE WHEN events = 'home_brand_grid_view' THEN events END) total_home_brand_grid_view_event, count(DISTINCT CASE WHEN events = 'home_brand_grid_view' THEN phone END) total_home_brand_grid_view_user, count(CASE WHEN events = 'videofeed_videoclick' THEN events END) total_videofeed_videoclick_event, count(DISTINCT CASE WHEN events = 'videofeed_videoclick' THEN phone END) total_videofeed_videoclick_user, count(CASE WHEN events = 'home_brand_Caraousel' THEN events END) total_home_brand_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_brand_Caraousel' THEN phone END) total_home_brand_Caraousel_user, count(CASE WHEN events = 'home_product_Caraousel' THEN events END) total_home_product_Caraousel_event, count(DISTINCT CASE WHEN events = 'home_product_Caraousel' THEN phone END) total_home_product_Caraousel_user, count(CASE WHEN events = 'home_product_infinite' THEN events END) total_home_product_infinite_event, count(DISTINCT CASE WHEN events = 'home_product_infinite' THEN phone END) total_home_product_infinite_user FROM raena_analytics.homepage_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile LEFT JOIN raena_user_management.tier C ON tier_id= cast(C.id as varchar) LEFT JOIN raena_catalog_management.brand D ON brand_code=cast(D.id as varchar) LEFT JOIN raena_catalog_management.brand E ON collection_id= cast(E.id as varchar) LEFT JOIN raena_catalog_management.category F ON collection_id= cast(F.id as varchar) OR brand_id = cast(D.id as varchar) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11; drop table if exists raena_analytics.pdp_base_stage1_v1; create table raena_analytics.pdp_base_stage1_v1 as select * FROM clevertap.clevertap_master_data cmd WHERE events IN ('view_item', 'pdp_buynow', 'add_to_cart', 'pdp_setmargin') and ts::date >= '$reportDate' ; delete from raena_analytics.pdp_base_stage1 where ts::date >= '$reportDate' ; insert into raena_analytics.pdp_base_stage1 SELECT ts, events, name , email, phone, objectid, json_extract_path_text(profiledata,'tier',true) tier, json_extract_path_text(profiledata,'isguestuser',true) isguestuser, json_extract_path_text(replace(event_props,'\\\xa0',' '),'item_brand',true) brand_name, json_extract_path_text(replace(event_props,'\\\xa0',' '),'item_id',true) SKU_code, json_extract_path_text(replace(event_props,'\\\xa0',' '),'categoryPageId',true) categoryPageId, json_extract_path_text(replace(event_props,'\\\xa0',' '),'screen',true) screen FROM raena_analytics.pdp_base_stage1_v1; drop table if exists raena_analytics.pdp_base; create table raena_analytics.pdp_base AS SELECT 'Day' Frequency, date_trunc('day',ts)::date AS created_date, tier tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, isguestuser guest_user, brand_name brand_name, categoryPageId, screen, SKU_code, count(CASE WHEN events = 'view_item' THEN events END) total_view_item_event, count(DISTINCT CASE WHEN events = 'view_item' THEN phone END) total_view_item_user, count(CASE WHEN events = 'pdp_buynow' THEN events END) total_pdp_buynow_event, count(DISTINCT CASE WHEN events = 'pdp_buynow' THEN phone END) total_pdp_buynow_user, count(CASE WHEN events = 'add_to_cart' THEN events END) total_add_to_cart_event, count(DISTINCT CASE WHEN events = 'add_to_cart' THEN phone END) total_add_to_cart_user, count(CASE WHEN events = 'pdp_setmargin' THEN events END) total_pdp_setmargin_event, count(DISTINCT CASE WHEN events = 'pdp_setmargin' THEN phone END) total_pdp_setmargin_user FROM raena_analytics.pdp_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 union SELECT 'Week' Frequency, date_trunc('Week',ts)::date AS created_date, tier tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, isguestuser guest_user, brand_name brand_name, categoryPageId, screen, SKU_code, count(CASE WHEN events = 'view_item' THEN events END) total_view_item_event, count(DISTINCT CASE WHEN events = 'view_item' THEN phone END) total_view_item_user, count(CASE WHEN events = 'pdp_buynow' THEN events END) total_pdp_buynow_event, count(DISTINCT CASE WHEN events = 'pdp_buynow' THEN phone END) total_pdp_buynow_user, count(CASE WHEN events = 'add_to_cart' THEN events END) total_add_to_cart_event, count(DISTINCT CASE WHEN events = 'add_to_cart' THEN phone END) total_add_to_cart_user, count(CASE WHEN events = 'pdp_setmargin' THEN events END) total_pdp_setmargin_event, count(DISTINCT CASE WHEN events = 'pdp_setmargin' THEN phone END) total_pdp_setmargin_user FROM raena_analytics.pdp_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 union SELECT 'Month' Frequency, date_trunc('Month',ts)::date AS created_date, tier tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, isguestuser guest_user, brand_name brand_name, categoryPageId, screen, SKU_code, count(CASE WHEN events = 'view_item' THEN events END) total_view_item_event, count(DISTINCT CASE WHEN events = 'view_item' THEN phone END) total_view_item_user, count(CASE WHEN events = 'pdp_buynow' THEN events END) total_pdp_buynow_event, count(DISTINCT CASE WHEN events = 'pdp_buynow' THEN phone END) total_pdp_buynow_user, count(CASE WHEN events = 'add_to_cart' THEN events END) total_add_to_cart_event, count(DISTINCT CASE WHEN events = 'add_to_cart' THEN phone END) total_add_to_cart_user, count(CASE WHEN events = 'pdp_setmargin' THEN events END) total_pdp_setmargin_event, count(DISTINCT CASE WHEN events = 'pdp_setmargin' THEN phone END) total_pdp_setmargin_user FROM raena_analytics.pdp_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 union SELECT 'Quarter' Frequency, date_trunc('quarter',ts)::date AS created_date, tier tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, isguestuser guest_user, brand_name brand_name, categoryPageId, screen, SKU_code, count(CASE WHEN events = 'view_item' THEN events END) total_view_item_event, count(DISTINCT CASE WHEN events = 'view_item' THEN phone END) total_view_item_user, count(CASE WHEN events = 'pdp_buynow' THEN events END) total_pdp_buynow_event, count(DISTINCT CASE WHEN events = 'pdp_buynow' THEN phone END) total_pdp_buynow_user, count(CASE WHEN events = 'add_to_cart' THEN events END) total_add_to_cart_event, count(DISTINCT CASE WHEN events = 'add_to_cart' THEN phone END) total_add_to_cart_user, count(CASE WHEN events = 'pdp_setmargin' THEN events END) total_pdp_setmargin_event, count(DISTINCT CASE WHEN events = 'pdp_setmargin' THEN phone END) total_pdp_setmargin_user FROM raena_analytics.pdp_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9 union SELECT 'Year' Frequency, date_trunc('Year',ts)::date AS created_date, tier tier_name, CASE WHEN date_trunc('Month',ts)::date = date_trunc('Month',first_install_date)::date AND date_trunc('Month',ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New' ELSE 'existing' END user_type, isguestuser guest_user, brand_name brand_name, categoryPageId, screen, SKU_code, count(CASE WHEN events = 'view_item' THEN events END) total_view_item_event, count(DISTINCT CASE WHEN events = 'view_item' THEN phone END) total_view_item_user, count(CASE WHEN events = 'pdp_buynow' THEN events END) total_pdp_buynow_event, count(DISTINCT CASE WHEN events = 'pdp_buynow' THEN phone END) total_pdp_buynow_user, count(CASE WHEN events = 'add_to_cart' THEN events END) total_add_to_cart_event, count(DISTINCT CASE WHEN events = 'add_to_cart' THEN phone END) total_add_to_cart_user, count(CASE WHEN events = 'pdp_setmargin' THEN events END) total_pdp_setmargin_event, count(DISTINCT CASE WHEN events = 'pdp_setmargin' THEN phone END) total_pdp_setmargin_user FROM raena_analytics.pdp_base_stage1 LEFT JOIN raena_analytics.user_type_table ON phone = reseller_mobile GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9; drop table if exists raena_analytics.flash_sale_order_stage_v1; create table raena_analytics.flash_sale_order_stage_v1 as SELECT BB.*,AA.flash_sale_id FROM (select external_id , sku, brand_name, tier_name, sum(quantity)quantity, sum((discounted_price*quantity)) discounted_price, raena_analytics.gm_dashboard.reseller_id, transaction_date::date transaction_date from raena_analytics.gm_dashboard where item_type like 'Flash%' group by 1,2,3,4,7,8) BB INNER JOIN (select id,flash_sale_id from raena_order_management.order where flash_sale_id IS NOT NULL)AA ON external_id = id ; DROP TABLE IF EXISTS raena_analytics.flash_sale_order; CREATE TABLE raena_analytics.flash_sale_order AS SELECT 'Day' Frequency, date_trunc('Day',transaction_date)::date AS created_date, upper(tier_name)tier_name, sku, brand_name, flash_sale_id, count(DISTINCT external_id) total_orders, sum(discounted_price) post_discount_revenue, sum(quantity) total_quantity, count(distinct reseller_id) number_of_reseller FROM raena_analytics.flash_sale_order_stage_v1 GROUP BY 1, 2, 3, 4, 5, 6 union SELECT 'Week' Frequency, date_trunc('Week',transaction_date)::date AS created_date, upper(tier_name)tier_name, sku, brand_name, flash_sale_id, count(DISTINCT external_id) total_orders, sum(discounted_price) post_discount_revenue, sum(quantity) total_quantity, count(distinct reseller_id) number_of_reseller FROM raena_analytics.flash_sale_order_stage_v1 GROUP BY 1, 2, 3, 4, 5, 6 union SELECT 'Month' Frequency, date_trunc('Month',transaction_date)::date AS created_date, upper(tier_name)tier_name, sku, brand_name, flash_sale_id, count(DISTINCT external_id) total_orders, sum(discounted_price) post_discount_revenue, sum(quantity) total_quantity, count(distinct reseller_id) number_of_reseller FROM raena_analytics.flash_sale_order_stage_v1 GROUP BY 1, 2, 3, 4, 5, 6 union SELECT 'Quarter' Frequency, date_trunc('Quarter',transaction_date)::date AS created_date, upper(tier_name)tier_name, sku, brand_name, flash_sale_id, count(DISTINCT external_id) total_orders, sum(discounted_price) post_discount_revenue, sum(quantity) total_quantity, count(distinct reseller_id) number_of_reseller FROM raena_analytics.flash_sale_order_stage_v1 GROUP BY 1, 2, 3, 4, 5, 6 union SELECT 'Year' Frequency, date_trunc('year',transaction_date)::date AS created_date, upper(tier_name)tier_name, sku, brand_name, flash_sale_id, count(DISTINCT external_id) total_orders, sum(discounted_price) post_discount_revenue, sum(quantity) total_quantity, count(distinct reseller_id) number_of_reseller FROM raena_analytics.flash_sale_order_stage_v1 GROUP BY 1, 2, 3, 4, 5, 6; delete from raena_analytics.flash_base_stage1 where ts::date >= '$reportDate'; insert into raena_analytics.flash_base_stage1 SELECT ts, name , email, events, phone, objectid, json_extract_path_text(profiledata,'tier',true) tier, json_extract_path_text(replace(event_props,'\\\xa0',' '),'item_brand',true) brand_name, json_extract_path_text(replace(event_props,'\\\xa0',' '),'item_id',true) SKU_code FROM clevertap.clevertap_master_data cmd WHERE events in ('flashsale_carousel_item_press','flashsale_carousel_view_all_press') and ts::date >= '$reportDate' ; DROP TABLE IF EXISTS raena_analytics.flash_sale_event_table; CREATE TABLE raena_analytics.flash_sale_event_table AS SELECT 'Day' Frequency, date_trunc('day',ts)::date AS created_date, tier tier_name, brand_name brand_name, SKU_code, count(CASE WHEN events = 'flashsale_carousel_item_press' THEN events END) total_flashsale_carousel_item_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_item_press' THEN phone END) total_flashsale_carousel_item_press_user, count(CASE WHEN events = 'flashsale_carousel_view_all_press' THEN events END) total_flashsale_carousel_view_all_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_view_all_press' THEN phone END) total_flashsale_carousel_view_all_press_user FROM raena_analytics.flash_base_stage1 GROUP BY 1, 2, 3, 4, 5 UNION SELECT 'Week' Frequency, date_trunc('Week',ts)::date AS created_date, tier tier_name, brand_name brand_name, SKU_code, count(CASE WHEN events = 'flashsale_carousel_item_press' THEN events END) total_flashsale_carousel_item_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_item_press' THEN phone END) total_flashsale_carousel_item_press_user, count(CASE WHEN events = 'flashsale_carousel_view_all_press' THEN events END) total_flashsale_carousel_view_all_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_view_all_press' THEN phone END) total_flashsale_carousel_view_all_press_user FROM raena_analytics.flash_base_stage1 GROUP BY 1, 2, 3, 4, 5 UNION SELECT 'Month' Frequency, date_trunc('Month',ts)::date AS created_date, tier tier_name, brand_name brand_name, SKU_code, count(CASE WHEN events = 'flashsale_carousel_item_press' THEN events END) total_flashsale_carousel_item_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_item_press' THEN phone END) total_flashsale_carousel_item_press_user, count(CASE WHEN events = 'flashsale_carousel_view_all_press' THEN events END) total_flashsale_carousel_view_all_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_view_all_press' THEN phone END) total_flashsale_carousel_view_all_press_user FROM raena_analytics.flash_base_stage1 GROUP BY 1, 2, 3, 4, 5 UNION SELECT 'Quarter' Frequency, date_trunc('Quarter',ts)::date AS created_date, tier tier_name, brand_name brand_name, SKU_code, count(CASE WHEN events = 'flashsale_carousel_item_press' THEN events END) total_flashsale_carousel_item_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_item_press' THEN phone END) total_flashsale_carousel_item_press_user, count(CASE WHEN events = 'flashsale_carousel_view_all_press' THEN events END) total_flashsale_carousel_view_all_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_view_all_press' THEN phone END) total_flashsale_carousel_view_all_press_user FROM raena_analytics.flash_base_stage1 GROUP BY 1, 2, 3, 4, 5 UNION SELECT 'Quarter' Frequency, date_trunc('Quarter',ts)::date AS created_date, tier tier_name, brand_name brand_name, SKU_code, count(CASE WHEN events = 'flashsale_carousel_item_press' THEN events END) total_flashsale_carousel_item_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_item_press' THEN phone END) total_flashsale_carousel_item_press_user, count(CASE WHEN events = 'flashsale_carousel_view_all_press' THEN events END) total_flashsale_carousel_view_all_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_view_all_press' THEN phone END) total_flashsale_carousel_view_all_press_user FROM raena_analytics.flash_base_stage1 GROUP BY 1, 2, 3, 4, 5 UNION SELECT 'Year' Frequency, date_trunc('Year',ts)::date AS created_date, tier tier_name, brand_name brand_name, SKU_code, count(CASE WHEN events = 'flashsale_carousel_item_press' THEN events END) total_flashsale_carousel_item_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_item_press' THEN phone END) total_flashsale_carousel_item_press_user, count(CASE WHEN events = 'flashsale_carousel_view_all_press' THEN events END) total_flashsale_carousel_view_all_press_event, count(DISTINCT CASE WHEN events = 'flashsale_carousel_view_all_press' THEN phone END) total_flashsale_carousel_view_all_press_user FROM raena_analytics.flash_base_stage1 GROUP BY 1, 2, 3, 4, 5; " > /home/ec2-user/cronjob/redshift/sql_code/home_page_pdp_flash.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/home_page_pdp_flash.sql