#!/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.google_pay_split; CREATE TABLE raena_analytics.google_pay_split AS SELECT * , split_part(address ,', ',1) address1, split_part(address,', ',2) address2, split_part(address,', ',3) address3, split_part(address,', ',4) address4, split_part(address,', ',5) address5, split_part(address,', ',6) address6, split_part(address,', ',7) address7, split_part(address,', ',8) address8, split_part(address,', ',9) address9, split_part(address,', ',10) address10, split_part(address,', ',11) address11 FROM (SELECT DISTINCT lower(search_city) city, lower(address) address, CASE WHEN search_city='' AND address='' THEN 1 END flag FROM raena_crawler_management.google_map_search_results UNION SELECT DISTINCT lower(current_city) city, lower(address) address, CASE WHEN current_city='' AND address='' THEN 1 END flag FROM (SELECT fbp.profile_id, name reseller_name, mobile, Email, active_status is_account_active, current_city, hometown, WORK company, address, friends number_of_friends, followers number_of_followers, following number_of_people_following --,converted_flag FROM (SELECT facebook_profile.* FROM raena_crawler_management.facebook_profile ) fbp LEFT JOIN (SELECT DISTINCT coalesce(A.profile_id,B.profile_id) profile_id , mobile, Email FROM (SELECT profile_id , CASE WHEN detail_type= 'Mobile' THEN value END AS mobile FROM raena_crawler_management.facebook_profile_detail WHERE detail_type ='Mobile') A FULL OUTER JOIN (SELECT profile_id , CASE WHEN detail_type= 'Email' THEN value END AS Email FROM raena_crawler_management.facebook_profile_detail WHERE detail_type ='Email') B ON A.profile_id = B.profile_id) fbpd ON fbpd.profile_id = fbp.profile_id) B) A WHERE flag IS NULL; DROP TABLE raena_analytics.google_pay_mapping_table_stage; CREATE TABLE raena_analytics.google_pay_mapping_table_stage AS SELECT A.* , B.city mapping_city FROM raena_analytics.google_pay_split A LEFT JOIN (SELECT DISTINCT lower(city) city FROM raena_analytics.google_pay_split) B ON 1=1; DROP TABLE raena_analytics.city_calculated_values; CREATE TABLE raena_analytics.city_calculated_values AS SELECT address, mapping_city calculated_city FROM (SELECT *, CASE WHEN replace(replace(address1,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value1, CASE WHEN replace(replace(address2,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value2, CASE WHEN replace(replace(address3,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value3, CASE WHEN replace(replace(address4,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value4, CASE WHEN replace(replace(address5,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value5, CASE WHEN replace(replace(address6,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value6, CASE WHEN replace(replace(address7,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value7, CASE WHEN replace(replace(address8,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value8, CASE WHEN replace(replace(address9,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value9, CASE WHEN replace(replace(address10,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value10, CASE WHEN replace(replace(address11,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END map_value11, CASE WHEN replace(replace(address1,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address2,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address3,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address4,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address5,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address6,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address7,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address8,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address9,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address10,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END + CASE WHEN replace(replace(address11,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1 ELSE 0 END total FROM raena_analytics.google_pay_mapping_table_stage) A WHERE total>0 ; " > /home/ec2-user/cronjob/redshift/sql_code/city_mapping_for_online_lead_db.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/city_mapping_for_online_lead_db.sql