SPD With Loyalty Tier

create table `zalora-dwh.edw_staging.spd_source` partition by order_created_at_month as ( WITH spd_min_order_tab AS ( SELECT fk_customer , venture_code , min(order_created_at) AS first_order_date FROM `zalora-dwh.edw.view_fact_sales` where date(order_created_at) >= DATE_TRUNC(DATE_SUB(current_date('Singapore'), interval 5 month),MONTH) and date(order_created_at) < DATE_TRUNC(current_date('Singapore'), MONTH) GROUP BY 1, 2 ), spd_repeat_cohort as( SELECT vfs.fk_customer , vfs.venture_code , spd_min_order_tab.first_order_date ,max(CASE WHEN vfs.order_created_at BETWEEN first_order_date interval 1 day AND first_order_date interval 31 day THEN 1 ELSE 0 END) AS repeat_30 ,max(CASE WHEN vfs.order_created_at BETWEEN first_order_date interval 31 day AND first_order_date interval 61 day THEN 1 ELSE 0 END) AS repeat_60 ,max(CASE WHEN vfs.order_created_at BETWEEN first_order_date interval 61 day AND first_order_date interval 91 day THEN 1 ELSE 0 END) AS repeat_90 ,max(CASE WHEN vfs.order_created_at BETWEEN first_order_date interval 91 day AND first_order_date interval 121 day THEN 1 ELSE 0 END) AS repeat_120 ,max(CASE WHEN vfs.order_created_at BETWEEN first_order_date interval 121 day AND first_order_date interval 241 day THEN 1 ELSE 0 END) AS repeat_240 ,max(CASE WHEN vfs.order_created_at BETWEEN first_order_date interval 241 day AND first_order_date interval 366 day THEN 1 ELSE 0 END) AS repeat_360 FROM edw.view_fact_sales AS vfs JOIN spd_min_order_tab ON vfs.fk_customer = spd_min_order_tab.fk_customer AND vfs.venture_code = spd_min_order_tab.venture_code where date(vfs.order_created_at) >= DATE_TRUNC(DATE_SUB(current_date('Singapore'), interval 5 month),MONTH) and date(order_created_at) < DATE_TRUNC(current_date('Singapore'), MONTH) GROUP BY 1 ,2 ,3 ), spd_metrices as ( SELECT vfs.fk_customer , vfs.venture_code , CASE WHEN environment='iosApp' AND device_type IN ('tablet','phone', 'simulator') THEN 'zalora_iosApp_phone' WHEN environment='androidApp' AND device_type IN ('phone','tablet', 'android') THEN 'zalora_androidApp_phone' WHEN environment='desktop' THEN 'zalora_web' ELSE concat('zalora','_',vfs.environment,'_',vfs.device_type) END AS platform , rc.first_order_date , vfs.order_created_at , DATE_TRUNC(DATE(vfs.order_created_at), MONTH) AS order_created_at_month , DATE_TRUNC(Date(vfs.order_created_at), week(Monday)) AS order_week , repeat_30 , repeat_60 , repeat_90 , repeat_120 , repeat_240 , repeat_360 , vfs.nmv_in_eur , vfs.revenue_in_eur , vfs.item_cost * vfs.exchange_rate_wrt_euro as item_cost_in_eur , vfs.cofund_lcy * vfs.exchange_rate_wrt_euro as cofund_in_eur , vfs.fulfillment_fixed_cost_lcy * vfs.exchange_rate_wrt_euro as fulfillment_fixed_cost_in_eur , vfs.original_unit_price * vfs.exchange_rate_wrt_euro as original_unit_price_eur , vfs.cart_rule_discount as cart_rule_discount , vfs.unit_price * vfs.exchange_rate_wrt_euro as unit_price_eur , vfs.coupon_money_value , vfs.id_sales_order , SUM(CASE WHEN vfs.item_cancelled=1 THEN 1 ELSE 0 END) OVER (PARTITION BY vfs.fk_customer, vfs.venture_code) AS total_items_cancelled , SUM(CASE WHEN vfs.item_returned=1 THEN 1 ELSE 0 END) OVER (PARTITION BY vfs.fk_customer, vfs.venture_code) AS total_items_returned FROM `zalora-dwh.edw.view_fact_sales` AS vfs INNER JOIN spd_repeat_cohort rc ON vfs.fk_customer = rc.fk_customer AND vfs.venture_code=rc.venture_code WHERE vfs.item_before_cancellation=1 AND DATE(vfs.order_created_at) >= DATE_TRUNC(DATE_SUB(current_date('Singapore'), interval 5 month),MONTH) and date(vfs.order_created_at) < DATE_TRUNC(current_date('Singapore'), MONTH) ) SELECT sm.* , ltc.loyalty_tier , cesms.customer_type , cesms.segment , cesms.order_frequency_percentile , cesms.discount_percentile , cesms.low_asp_percentile , cesms.high_asp_percentile , cqd_flag.active_shoppers , cqd_flag.new_customers , cqd_flag.reactivated_customers_365d , cqd_flag.churned_customers_365d FROM spd_metrices sm LEFT JOIN `zalora-dwh.edw_staging.cust_enhanced_segmentation_monthly_snapshot_temp_new` cesms ON sm.fk_customer = cesms.fk_customer AND sm.venture_code = cesms.venture LEFT JOIN `starlit-booster-623.customer_quality.cqd_daily_customer_flags` cqd_flag ON sm.fk_customer = cqd_flag.fk_customer AND sm.venture_code = cqd_flag.venture_code AND date(sm.order_created_at) = cqd_flag.order_date LEFT JOIN `starlit-booster-623.user360.monthly_loyalty_tier_user_features` as ltc ON sm.fk_customer = ltc.fk_customer AND sm.venture_code = ltc.venture_code AND sm.order_created_at_month = ltc.segment_month );