515 lines
21 KiB
Bash
515 lines
21 KiB
Bash
|
#!/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 if exists raena_analytics.clevertap_event_data_vishnu;
|
||
|
|
|
||
|
|
create table raena_analytics.clevertap_event_data_vishnu
|
||
|
|
as
|
||
|
|
select ts::date ,events, name , email ,phone,event_props::jsonb->>'CT Session Id' sessionid ,
|
||
|
|
event_props::jsonb->>'item_id' item_id ,
|
||
|
|
event_props::jsonb->>'isGuestUser' isGuestUser ,
|
||
|
|
event_props::jsonb->>'CT Source' Source ,
|
||
|
|
event_props::jsonb->>'id' id ,
|
||
|
|
event_props::jsonb->>'brandName' brandName ,
|
||
|
|
event_props::jsonb->>'item_name' item_name ,
|
||
|
|
event_props::jsonb->>'offenderItems' offenderItems ,
|
||
|
|
event_props::jsonb->>'items' items ,
|
||
|
|
event_props::jsonb->>'search_term' search_term ,
|
||
|
|
event_props::jsonb->>'screen' screen
|
||
|
|
from clevertap.clevertap_master_data where events in (
|
||
|
|
'brands_tab_press' ,
|
||
|
|
'brand_logo_carousel_press',
|
||
|
|
'App Launched',
|
||
|
|
'view_item',
|
||
|
|
'begin_checkout',
|
||
|
|
'search',
|
||
|
|
'Charged',
|
||
|
|
'App Installed',
|
||
|
|
'view_cart',
|
||
|
|
'add_to_cart',
|
||
|
|
'customer_search',
|
||
|
|
'Notification Clicked',
|
||
|
|
'Notification Viewed',
|
||
|
|
'Push Impressions',
|
||
|
|
'home_category',
|
||
|
|
'pdp_brandprice',
|
||
|
|
'order_items_view',
|
||
|
|
'sidebar_browsebrands',
|
||
|
|
'confirm_payment',
|
||
|
|
'finish_checkout',
|
||
|
|
'loyalty_page_visit',
|
||
|
|
'home_brand_Caraousel',
|
||
|
|
'home_brand_grid_view',
|
||
|
|
'WISHLIST',
|
||
|
|
'brand_carousel_press',
|
||
|
|
'continue_shopping',
|
||
|
|
'home_banner',
|
||
|
|
'brandpage_image_banner_press',
|
||
|
|
'skip_login',
|
||
|
|
'App Uninstalled',
|
||
|
|
'coupon_applied_successfully',
|
||
|
|
'coupon_could_not_be_applied',
|
||
|
|
'view_rewards',
|
||
|
|
'view_order_checkout',
|
||
|
|
'coupon_remove',
|
||
|
|
'homeintent_buyws',
|
||
|
|
'homepage_image_banner_press',
|
||
|
|
'home_continue_payment',
|
||
|
|
'homepage_carousel_category_press',
|
||
|
|
'flashsale_carousel_item_press',
|
||
|
|
'flashsale_carousel_view_all_press',
|
||
|
|
'homeintent_managemp'
|
||
|
|
)
|
||
|
|
and ts::date >='2023-04-01';
|
||
|
|
|
||
|
|
drop table raena_analytics.hero_sku_l6m;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.hero_sku_l6m AS
|
||
|
|
SELECT DISTINCT brand_name ,
|
||
|
|
brand_id,
|
||
|
|
SKU
|
||
|
|
FROM
|
||
|
|
(SELECT * ,
|
||
|
|
sum(contribution) over (partition BY brand_name
|
||
|
|
ORDER BY rnk) final_rnk
|
||
|
|
FROM
|
||
|
|
(SELECT * ,
|
||
|
|
sum(payment_amount) over (partition BY brand_name) running_sum,
|
||
|
|
(payment_amount*100)/nullif(sum(payment_amount) over (partition BY brand_name),0) contribution
|
||
|
|
FROM
|
||
|
|
(SELECT GD.brand_name ,
|
||
|
|
gd.sku ,
|
||
|
|
B.brand_id,
|
||
|
|
sum(quantity* discounted_price) payment_amount ,
|
||
|
|
rank() over (partition BY brand_name
|
||
|
|
ORDER BY sum(quantity* discounted_price) DESC) rnk
|
||
|
|
FROM raena_analytics.gm_dashboard gd
|
||
|
|
LEFT JOIN raena_catalog_management.product B ON gd.sku = B.sku
|
||
|
|
WHERE transaction_date BETWEEN (date_trunc('Month',CURRENT_DATE)::date + interval'-6 Month')::date AND date_trunc('Month',CURRENT_DATE)::date
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3) A) AA) BB
|
||
|
|
WHERE (final_rnk <=75)
|
||
|
|
OR contribution >= 75 ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
drop table if exists raena_analytics.whatsapp_campaign_base_table ;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.whatsapp_campaign_base_table AS
|
||
|
|
SELECT DISTINCT brand_name ,
|
||
|
|
segment_name,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile ,
|
||
|
|
reseller_name
|
||
|
|
from
|
||
|
|
(select *,row_number()over(partition by brand_name order by segment_number ) brand_limit_user
|
||
|
|
FROM
|
||
|
|
(SELECT * ,
|
||
|
|
rank()over(partition BY brand_name,reseller_email
|
||
|
|
ORDER BY segment_number) remove_duplicate_user
|
||
|
|
FROM
|
||
|
|
(SELECT DISTINCT anchor_brand_name brand_name,
|
||
|
|
'SEGMENT_PERSONAL_ANCHOR_BRAND' segment_name,
|
||
|
|
1 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
NULL reseller_name
|
||
|
|
FROM
|
||
|
|
(SELECT Transaction_date2 year_date,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
bucket revenue_cohort,
|
||
|
|
Brand_name,
|
||
|
|
avg_brand_contribution ,
|
||
|
|
avg_payment_amount avg_brand_revenue,
|
||
|
|
anchor_brand_name,
|
||
|
|
avg_anchor_brand_contribution,
|
||
|
|
avg_anchor_payment_amount avg_anchor_brand_revenue
|
||
|
|
FROM raena_analytics.reseller_level_anchor_brand_data
|
||
|
|
WHERE 1=1
|
||
|
|
AND bucket IS NOT NULL
|
||
|
|
ORDER BY year_date DESC , avg_anchor_brand_contribution DESC) A
|
||
|
|
UNION ALL SELECT DISTINCT brand_name brand_name,
|
||
|
|
'SEGMENT_PARENT_BRAND_FOR_ANCHOR' segment_name,
|
||
|
|
2 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
NULL reseller_namme
|
||
|
|
FROM
|
||
|
|
(SELECT Transaction_date2 year_date,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
bucket revenue_cohort,
|
||
|
|
Brand_name,
|
||
|
|
avg_brand_contribution ,
|
||
|
|
avg_payment_amount avg_brand_revenue,
|
||
|
|
anchor_brand_name,
|
||
|
|
avg_anchor_brand_contribution,
|
||
|
|
avg_anchor_payment_amount avg_anchor_brand_revenue
|
||
|
|
FROM raena_analytics.reseller_level_anchor_brand_data
|
||
|
|
WHERE 1=1
|
||
|
|
AND bucket IS NOT NULL
|
||
|
|
ORDER BY year_date DESC , avg_anchor_brand_contribution DESC) A
|
||
|
|
UNION ALL SELECT DISTINCT brand_name brand_name,
|
||
|
|
'SEGMENT_PERSONALIZED_RECOMMENDATIONS' segment_name,
|
||
|
|
3 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
name reseller_name
|
||
|
|
FROM
|
||
|
|
(SELECT reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
u.name,
|
||
|
|
dr.sku,
|
||
|
|
b.name brand_name,
|
||
|
|
score match_score,
|
||
|
|
after_discount post_discount_gm,
|
||
|
|
stock_type,
|
||
|
|
pi.current_inventory_ready,
|
||
|
|
pi.current_inventory_pre_order,
|
||
|
|
p.name product_name
|
||
|
|
FROM
|
||
|
|
(SELECT *
|
||
|
|
FROM raena_recommendation_engine.daily_recommendation
|
||
|
|
ORDER BY reseller_email,
|
||
|
|
score DESC) dr
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT *
|
||
|
|
FROM raena_user_management.user
|
||
|
|
WHERE status = 'active') u ON u.mobile = dr.reseller_mobile
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT id,
|
||
|
|
sku,
|
||
|
|
stock_type,
|
||
|
|
name,
|
||
|
|
brand_id
|
||
|
|
FROM raena_catalog_management.product
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND is_delisted = 'false') p ON p.sku = dr.sku
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT product_id,
|
||
|
|
sum(CASE WHEN stock_type = 'READY_SKU' THEN stock_limit - reserve_stock END) AS current_inventory_ready ,
|
||
|
|
sum(CASE WHEN stock_type = 'PRE_ORDER' THEN stock_limit - reserve_stock END) AS current_inventory_pre_order
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pi ON pi.product_id = p.id
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT *
|
||
|
|
FROM raena_catalog_management.brand) b ON b.id = p.brand_id
|
||
|
|
ORDER BY match_score DESC) A
|
||
|
|
UNION ALL SELECT DISTINCT name brand_name,
|
||
|
|
'SEGMENT_PROVINCE_BASED_RECOMMENDATION' segment_name,
|
||
|
|
4 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
name reseller_name
|
||
|
|
FROM
|
||
|
|
( SELECT shipping_province ,
|
||
|
|
dr.sku ,
|
||
|
|
b.name ,
|
||
|
|
score ,
|
||
|
|
after_discount ,
|
||
|
|
stock_type,
|
||
|
|
pi.current_inventory_ready,
|
||
|
|
pi.current_inventory_pre_order,
|
||
|
|
p.name product_name
|
||
|
|
FROM
|
||
|
|
(SELECT shipping_province,
|
||
|
|
sku,
|
||
|
|
score,
|
||
|
|
after_discount
|
||
|
|
FROM raena_recommendation_engine.daily_recommendation_new_user
|
||
|
|
ORDER BY score DESC, rank) dr
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT id,
|
||
|
|
sku,
|
||
|
|
stock_type,
|
||
|
|
name,
|
||
|
|
brand_id
|
||
|
|
FROM raena_catalog_management.product
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND is_delisted = 'false') p ON p.sku = dr.sku
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT product_id,
|
||
|
|
sum(CASE WHEN stock_type = 'READY_SKU' THEN stock_limit - reserve_stock END) AS current_inventory_ready ,
|
||
|
|
sum(CASE WHEN stock_type = 'PRE_ORDER' THEN stock_limit - reserve_stock END) AS current_inventory_pre_order
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pi ON pi.product_id = p.id
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT id,
|
||
|
|
name
|
||
|
|
FROM raena_catalog_management.brand) b ON b.id = p.brand_id
|
||
|
|
ORDER BY Score DESC) A
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
reseller_name ,
|
||
|
|
shipping_province,
|
||
|
|
sku,
|
||
|
|
brand_name
|
||
|
|
FROM raena_analytics.gm_dashboard gd
|
||
|
|
WHERE transaction_date::date >='2023-02-01'
|
||
|
|
AND reseller_email IS NOT NULL)B ON lower(A.shipping_province) = lower(B.shipping_province)
|
||
|
|
AND A.name = B.brand_name
|
||
|
|
AND A.sku = B.sku
|
||
|
|
UNION ALL SELECT DISTINCT AA.Brand_name brand_name,
|
||
|
|
'SEGMENT_REVENUE_COHORT_ANCHOR_BRAND' segment_name,
|
||
|
|
4 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
reseller_name
|
||
|
|
FROM
|
||
|
|
(SELECT Transaction_date2 year_date,
|
||
|
|
bucket revenue_cohort,
|
||
|
|
Brand_name,
|
||
|
|
avg_brand_contribution ,
|
||
|
|
avg_payment_amount avg_brand_revenue,
|
||
|
|
anchor_brand_name,
|
||
|
|
avg_anchor_brand_contribution,
|
||
|
|
avg_anchor_payment_amount avg_anchor_brand_revenue
|
||
|
|
FROM raena_analytics.anchor_brand_data
|
||
|
|
ORDER BY year_date DESC, avg_anchor_brand_contribution DESC) AA
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT brand_name ,
|
||
|
|
bucket,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile ,
|
||
|
|
reseller_name
|
||
|
|
FROM raena_analytics.gm_dashboard gd
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT DISTINCT reseller_id ,
|
||
|
|
CASE WHEN max_payment_amount BETWEEN 0 AND 2000000 THEN '0 to 2M' WHEN max_payment_amount BETWEEN 2000000 AND 10000000 THEN '2M to 10M' 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',transaction_date)::date ,
|
||
|
|
sum(discounted_price*quantity) payment_amount
|
||
|
|
FROM raena_analytics.gm_dashboard gd2
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
GROUP BY 1) B)C ON gd.reseller_id = C.reseller_id
|
||
|
|
WHERE transaction_date::date BETWEEN '2023-05-01' AND '2023-07-31') BB ON AA.brand_name = BB.brand_name
|
||
|
|
AND AA.revenue_cohort = BB.bucket
|
||
|
|
UNION ALL SELECT DISTINCT brand_name,
|
||
|
|
'SEGMENT_PERSONALIZED_RECOMMENDATIONS_SIMILAR_USER' segment_name,
|
||
|
|
5 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
name reseller_name
|
||
|
|
FROM
|
||
|
|
(SELECT reseller_email,
|
||
|
|
mobile Reseller_mobile,
|
||
|
|
bb.name,
|
||
|
|
dr.sku,
|
||
|
|
b.name brand_name,
|
||
|
|
score match_score,
|
||
|
|
after_discount post_discount_gm,
|
||
|
|
stock_type,
|
||
|
|
pi.current_inventory_ready,
|
||
|
|
pipreorder.current_inventory_pre_order,
|
||
|
|
p.name product_name
|
||
|
|
FROM
|
||
|
|
(SELECT *
|
||
|
|
FROM raena_recommendation_engine.daily_similar_user_v2
|
||
|
|
ORDER BY reseller_email,
|
||
|
|
score DESC) dr
|
||
|
|
LEFT JOIN
|
||
|
|
( SELECT id,
|
||
|
|
sku,
|
||
|
|
stock_type,
|
||
|
|
name,
|
||
|
|
brand_id
|
||
|
|
FROM raena_catalog_management.product
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND is_delisted = 'false') p ON p.sku = dr.sku
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT product_id,
|
||
|
|
sum(stock_limit - reserve_stock) AS current_inventory_ready
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND stock_type = 'READY_SKU'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pi ON pi.product_id = p.id
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT product_id,
|
||
|
|
sum(stock_limit - reserve_stock) AS current_inventory_pre_order,
|
||
|
|
max(estimated_arrival_date) AS estimated_arrival_date
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND stock_type = 'PRE_ORDER'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pipreorder ON pipreorder.product_id = p.id
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT id,
|
||
|
|
name
|
||
|
|
FROM raena_catalog_management.brand) b ON b.id = p.brand_id
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT *
|
||
|
|
FROM raena_user_management.user) bb ON bb.email = dr.reseller_email
|
||
|
|
ORDER BY reseller_email,
|
||
|
|
match_score DESC) AA
|
||
|
|
UNION ALL SELECT DISTINCT brand_name,
|
||
|
|
'SEGMENT_PERSONALIZED_RECOMMENDATIONS_SHIPPING_PROVINCE' segment_name,
|
||
|
|
6 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
name reseller_name
|
||
|
|
FROM
|
||
|
|
(SELECT dr.reseller_email,
|
||
|
|
mobile Reseller_mobile,
|
||
|
|
bb.name,
|
||
|
|
Shipping_Province,
|
||
|
|
dr.sku,
|
||
|
|
b.name brand_name,
|
||
|
|
score match_score,
|
||
|
|
after_discount post_discount_gm,
|
||
|
|
stock_type,
|
||
|
|
pi.current_inventory_ready,
|
||
|
|
pipreorder.current_inventory_pre_order,
|
||
|
|
p.name product_name
|
||
|
|
FROM
|
||
|
|
(SELECT *
|
||
|
|
FROM raena_recommendation_engine.daily_recommendation_shipping_province
|
||
|
|
ORDER BY reseller_email,
|
||
|
|
score DESC) dr
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT id,
|
||
|
|
sku,
|
||
|
|
stock_type,
|
||
|
|
name,
|
||
|
|
brand_id
|
||
|
|
FROM raena_catalog_management.product
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND is_delisted = 'false') p ON p.sku = dr.sku
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT product_id,
|
||
|
|
sum(stock_limit - reserve_stock) AS current_inventory_ready
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND stock_type = 'READY_SKU'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pi ON pi.product_id = p.id
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT product_id,
|
||
|
|
sum(stock_limit - reserve_stock) AS current_inventory_pre_order,
|
||
|
|
max(estimated_arrival_date) AS estimated_arrival_date
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND stock_type = 'PRE_ORDER'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pipreorder ON pipreorder.product_id = p.id
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT id,
|
||
|
|
name
|
||
|
|
FROM raena_catalog_management.brand) b ON b.id = p.brand_id
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT *
|
||
|
|
FROM raena_user_management.user) bb ON bb.email = dr.reseller_email
|
||
|
|
ORDER BY match_score DESC) AA
|
||
|
|
UNION ALL SELECT DISTINCT brand_name,
|
||
|
|
'SEGMENT_PERSONALIZED_RECOMMENDATIONS_REVENUE' segment_name,
|
||
|
|
7 segment_number,
|
||
|
|
reseller_email,
|
||
|
|
reseller_mobile,
|
||
|
|
name reseller_name
|
||
|
|
FROM
|
||
|
|
(SELECT dr.reseller_email,
|
||
|
|
mobile Reseller_mobile,
|
||
|
|
bb.name,
|
||
|
|
dr.sku,
|
||
|
|
b.name brand_name,
|
||
|
|
score match_score,
|
||
|
|
after_discount post_discount_gm,
|
||
|
|
stock_type,
|
||
|
|
pi.current_inventory_ready,
|
||
|
|
pipreorder.current_inventory_pre_order,
|
||
|
|
p.name product_name
|
||
|
|
FROM
|
||
|
|
( SELECT *
|
||
|
|
FROM raena_recommendation_engine.daily_recommendation_revenue
|
||
|
|
ORDER BY reseller_email,
|
||
|
|
score DESC) dr
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT id,
|
||
|
|
sku,
|
||
|
|
stock_type,
|
||
|
|
name,
|
||
|
|
brand_id
|
||
|
|
FROM raena_catalog_management.product
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND is_delisted = 'false') p ON p.sku = dr.sku
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT product_id,
|
||
|
|
sum(stock_limit - reserve_stock) AS current_inventory_ready
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND stock_type = 'READY_SKU'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pi ON pi.product_id = p.id
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT product_id,
|
||
|
|
sum(stock_limit - reserve_stock) AS current_inventory_pre_order,
|
||
|
|
max(estimated_arrival_date) AS estimated_arrival_date
|
||
|
|
FROM raena_catalog_management.product_inventory
|
||
|
|
WHERE is_archived = 'false'
|
||
|
|
AND stock_type = 'PRE_ORDER'
|
||
|
|
AND (stock_limit - reserve_stock) > 0
|
||
|
|
GROUP BY product_id) pipreorder ON pipreorder.product_id = p.id
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT id,
|
||
|
|
name
|
||
|
|
FROM raena_catalog_management.brand) b ON b.id = p.brand_id
|
||
|
|
INNER JOIN
|
||
|
|
( SELECT *
|
||
|
|
FROM raena_user_management.user) bb ON bb.email = dr.reseller_email
|
||
|
|
ORDER BY match_score DESC) A) AA) BB
|
||
|
|
WHERE remove_duplicate_user =1) CC
|
||
|
|
where brand_limit_user <=2000;
|
||
|
|
|
||
|
|
|
||
|
|
update raena_analytics.whatsapp_campaign_base_table
|
||
|
|
set reseller_name = name
|
||
|
|
from raena_user_management.user
|
||
|
|
where reseller_email = email ;
|
||
|
|
|
||
|
|
|
||
|
|
update raena_analytics.whatsapp_campaign_base_table
|
||
|
|
set reseller_name = name
|
||
|
|
from raena_user_management.user
|
||
|
|
where reseller_mobile = mobile
|
||
|
|
and reseller_name is null ;
|
||
|
|
|
||
|
|
|
||
|
|
update raena_analytics.whatsapp_campaign_base_table
|
||
|
|
set reseller_email = email
|
||
|
|
from raena_user_management.user
|
||
|
|
where reseller_mobile = mobile
|
||
|
|
and reseller_email is null ;
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/postgresql/am_recommendation/whatsapp_campaign.sql
|
||
|
|
|
||
|
|
psql "host=analytics-db-instance-1.cd7qipz3esdx.ap-southeast-1.rds.amazonaws.com user=dbadmin dbname=analytics port=5432 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/postgresql/am_recommendation/whatsapp_campaign.sql
|
||
|
|
|