SQL Explorer

111

near "AT": syntax error

Title

Connection

Description

SQL

SELECT segmentationindex, segmentationname, time0 as starttime, sum(tag0) as tag0, sum(tag1) as tag1, sum(tag2) as tag2  FROM ( SELECT segmentation.___id___ as ___id___, 0 as segmentationindex, '所有人' as segmentationname, funnel.record.tag0 as tag0, format_datetime(funnel.record.time0 AT TIME ZONE 'GMT+8', 'yyyy/MM/dd') as time0, funnel.record.tag1 as tag1, format_datetime(funnel.record.time1 AT TIME ZONE 'GMT+8', 'yyyy/MM/dd') as time1, funnel.record.tag2 as tag2, format_datetime(funnel.record.time2 AT TIME ZONE 'GMT+8', 'yyyy/MM/dd') as time2  FROM ( select distinct(id) as ___id___ from
(
 select COALESCE(table_1710316380249,0) as table_1710316380249,  t.___id___ as id from
 cdp.dcdp_ads_cd_uid_df t
 left join
 (
    select ___id___ as id,  sum(1) as table_1710316380249 from
     ( SELECT ads_cd_cust_info_df.unify_id as ___id___ , ads_cd_cust_info_df .unify_id  as unify_id , ads_cd_cust_info_df .bpartner_id  as bpartner_id , ads_cd_cust_info_df .no_bpartner  as no_bpartner , ads_cd_cust_info_df .first_name_cn_prewash  as first_name_cn_prewash , ads_cd_cust_info_df .last_name_cn_prewash  as last_name_cn_prewash , ads_cd_cust_info_df .first_name_ori  as first_name_ori , ads_cd_cust_info_df .last_name_ori  as last_name_ori , ads_cd_cust_info_df .name_flag  as name_flag , ads_cd_cust_info_df . title  as title , ads_cd_cust_info_df . gender  as gender , ads_cd_cust_info_df .gender_flag  as gender_flag , ads_cd_cust_info_df .birth_dt  as birth_dt , ads_cd_cust_info_df .curr_year_bday  as curr_year_bday , ads_cd_cust_info_df .is_bday_curr_month  as is_bday_curr_month , ads_cd_cust_info_df .is_bday_next_month  as is_bday_next_month , ads_cd_cust_info_df .next_bday  as next_bday , ads_cd_cust_info_df .bday_month  as bday_month , ads_cd_cust_info_df .partner_birth_dt  as partner_birth_dt , ads_cd_cust_info_df . age  as age , ads_cd_cust_info_df .spoken_language  as spoken_language , ads_cd_cust_info_df .written_language  as written_language , ads_cd_cust_info_df .mobile_verified  as mobile_verified , ads_cd_cust_info_df .mobile_unverified  as mobile_unverified , ads_cd_cust_info_df . email  as email , ads_cd_cust_info_df . province  as province , ads_cd_cust_info_df . city  as city , ads_cd_cust_info_df . district  as district , ads_cd_cust_info_df .province_cn  as province_cn , ads_cd_cust_info_df .city_cn  as city_cn , ads_cd_cust_info_df .district_cn  as district_cn , ads_cd_cust_info_df . road  as road , ads_cd_cust_info_df . country  as country , ads_cd_cust_info_df .bpartner_status  as bpartner_status , ads_cd_cust_info_df .marital_status  as marital_status , ads_cd_cust_info_df .wedding_dt  as wedding_dt , ads_cd_cust_info_df .wedding_ann_curr_year  as wedding_ann_curr_year , ads_cd_cust_info_df .next_wedding_ann  as next_wedding_ann , ads_cd_cust_info_df .wedding_ann_month  as wedding_ann_month , ads_cd_cust_info_df .order_amt_wo_tax  as order_amt_wo_tax , ads_cd_cust_info_df .order_amt_wo_tax_eur  as order_amt_wo_tax_eur , ads_cd_cust_info_df .order_amt_wo_tax_r12m  as order_amt_wo_tax_r12m , ads_cd_cust_info_df .order_amt_wo_tax_r12m_eur  as order_amt_wo_tax_r12m_eur , ads_cd_cust_info_df .order_amt_wo_tax_r36m  as order_amt_wo_tax_r36m , ads_cd_cust_info_df .order_amt_wo_tax_r36m_eur  as order_amt_wo_tax_r36m_eur , ads_cd_cust_info_df .ref_btq_sap  as ref_btq_sap , ads_cd_cust_info_df .ref_sa_sap  as ref_sa_sap , ads_cd_cust_info_df .ref_ambas_sap  as ref_ambas_sap , ads_cd_cust_info_df .reg_btq  as reg_btq , ads_cd_cust_info_df .reg_sa  as reg_sa , ads_cd_cust_info_df .reg_dt  as reg_dt , ads_cd_cust_info_df .join_dt  as join_dt , ads_cd_cust_info_df .next_reg_annv  as next_reg_annv , ads_cd_cust_info_df .last_order_dt  as last_order_dt , ads_cd_cust_info_df .woa_status  as woa_status , ads_cd_cust_info_df .last_webtq_visit_duration  as last_webtq_visit_duration , ads_cd_cust_info_df .is_wecom_friend  as is_wecom_friend , ads_cd_cust_info_df .contactbility_sap  as contactbility_sap , ads_cd_cust_info_df . contactbility  as contactbility , ads_cd_cust_info_df .privacy_policy_dt  as privacy_policy_dt , ads_cd_cust_info_df .is_consent_privacy_policy  as is_consent_privacy_policy , ads_cd_cust_info_df .is_consent_cbdt  as is_consent_cbdt , ads_cd_cust_info_df .is_consent_sms_marketing  as is_consent_sms_marketing , ads_cd_cust_info_df .is_consent_email_marketing  as is_consent_email_marketing , ads_cd_cust_info_df .is_consent_phone_marketing  as is_consent_phone_marketing , ads_cd_cust_info_df .is_consent_postal_marketing  as is_consent_postal_marketing , ads_cd_cust_info_df .is_consent_social_media_marketing  as is_consent_social_media_marketing , ads_cd_cust_info_df .join_channel  as join_channel , ads_cd_cust_info_df .join_sub_channel  as join_sub_channel , ads_cd_cust_info_df .reg_channel  as reg_channel , ads_cd_cust_info_df .reg_sub_channel  as reg_sub_channel , ads_cd_cust_info_df .system_update_time  as system_update_time , ads_cd_cust_info_df .is_basket_customer  as is_basket_customer , ads_cd_cust_info_df .authorised_brand  as authorised_brand , ads_cd_cust_info_df .last_order_age  as last_order_age , ads_cd_cust_info_df .ref_sa_list  as ref_sa_list , ads_cd_cust_info_df .ref_btq_list  as ref_btq_list , ads_cd_cust_info_df .mobile_list  as mobile_list , ads_cd_cust_info_df .email_list  as email_list , ads_cd_cust_info_df .last_watch_order_age  as last_watch_order_age , ads_cd_cust_info_df .last_watch_order_dt  as last_watch_order_dt , ads_cd_cust_info_df . constellation  as constellation , ads_cd_cust_info_df . pt  as pt FROM datalake.ads_cd_cust_info_df WHERE  ads_cd_cust_info_df . pt >='20250512' and  ads_cd_cust_info_df . pt <='20250512' )  as dcdp_ads_cd_cust_info_df
     where  1=1  AND (( ( (bpartner_id is not null and bpartner_id <> '')  and ( authorised_brand = 'VCA' ))))
   group by ___id___
 )t1
 on t.___id___ = t1.id
)tt
 where (( (COALESCE(table_1710316380249,0) > COALESCE(0,0) ))) and exists (
select 1
from  cdp.id_access_data ta
cross join unnest(org) t(org_id)
where  type = 'user' and t.org_id = 19
and ta.id = tt.id )  ) segmentation JOIN ( SELECT ___id___, record  FROM ( SELECT t0.___id___, cast(ROW( CASE WHEN t0.___time___ is not null THEN 1 ELSE 0 END, t0.___time___, CASE WHEN t1.___time___ is not null THEN 1 ELSE 0 END, t1.___time___, CASE WHEN t2.___time___ is not null THEN 1 ELSE 0 END, t2.___time___ ) AS ROW( tag0 int, time0 timestamp, tag1 int, time1 timestamp, tag2 int, time2 timestamp )) as record FROM ( SELECT  ___id___ as ___id___, case when cast ( format_datetime (reg_dt,'HHmmssSSS') as bigint) > 0 then reg_dt else to_timestamp ( concat( format_datetime (reg_dt,'yyyy-MM-dd'), ' 22:00:00') ,'yyyy-mm-dd hh24:mi:ss') end   as ___time___ FROM     ( SELECT ads_cd_cust_info_df.unify_id as ___id___ , ads_cd_cust_info_df .unify_id  as unify_id , ads_cd_cust_info_df .bpartner_id  as bpartner_id , ads_cd_cust_info_df .no_bpartner  as no_bpartner , ads_cd_cust_info_df .first_name_cn_prewash  as first_name_cn_prewash , ads_cd_cust_info_df .last_name_cn_prewash  as last_name_cn_prewash , ads_cd_cust_info_df .first_name_ori  as first_name_ori , ads_cd_cust_info_df .last_name_ori  as last_name_ori , ads_cd_cust_info_df .name_flag  as name_flag , ads_cd_cust_info_df . title  as title , ads_cd_cust_info_df . gender  as gender , ads_cd_cust_info_df .gender_flag  as gender_flag , ads_cd_cust_info_df .birth_dt  as birth_dt , ads_cd_cust_info_df .curr_year_bday  as curr_year_bday , ads_cd_cust_info_df .is_bday_curr_month  as is_bday_curr_month , ads_cd_cust_info_df .is_bday_next_month  as is_bday_next_month , ads_cd_cust_info_df .next_bday  as next_bday , ads_cd_cust_info_df .bday_month  as bday_month , ads_cd_cust_info_df .partner_birth_dt  as partner_birth_dt , ads_cd_cust_info_df . age  as age , ads_cd_cust_info_df .spoken_language  as spoken_language , ads_cd_cust_info_df .written_language  as written_language , ads_cd_cust_info_df .mobile_verified  as mobile_verified , ads_cd_cust_info_df .mobile_unverified  as mobile_unverified , ads_cd_cust_info_df . email  as email , ads_cd_cust_info_df . province  as province , ads_cd_cust_info_df . city  as city , ads_cd_cust_info_df . district  as district , ads_cd_cust_info_df .province_cn  as province_cn , ads_cd_cust_info_df .city_cn  as city_cn , ads_cd_cust_info_df .district_cn  as district_cn , ads_cd_cust_info_df . road  as road , ads_cd_cust_info_df . country  as country , ads_cd_cust_info_df .bpartner_status  as bpartner_status , ads_cd_cust_info_df .marital_status  as marital_status , ads_cd_cust_info_df .wedding_dt  as wedding_dt , ads_cd_cust_info_df .wedding_ann_curr_year  as wedding_ann_curr_year , ads_cd_cust_info_df .next_wedding_ann  as next_wedding_ann , ads_cd_cust_info_df .wedding_ann_month  as wedding_ann_month , ads_cd_cust_info_df .order_amt_wo_tax  as order_amt_wo_tax , ads_cd_cust_info_df .order_amt_wo_tax_eur  as order_amt_wo_tax_eur , ads_cd_cust_info_df .order_amt_wo_tax_r12m  as order_amt_wo_tax_r12m , ads_cd_cust_info_df .order_amt_wo_tax_r12m_eur  as order_amt_wo_tax_r12m_eur , ads_cd_cust_info_df .order_amt_wo_tax_r36m  as order_amt_wo_tax_r36m , ads_cd_cust_info_df .order_amt_wo_tax_r36m_eur  as order_amt_wo_tax_r36m_eur , ads_cd_cust_info_df .ref_btq_sap  as ref_btq_sap , ads_cd_cust_info_df .ref_sa_sap  as ref_sa_sap , ads_cd_cust_info_df .ref_ambas_sap  as ref_ambas_sap , ads_cd_cust_info_df .reg_btq  as reg_btq , ads_cd_cust_info_df .reg_sa  as reg_sa , ads_cd_cust_info_df .reg_dt  as reg_dt , ads_cd_cust_info_df .join_dt  as join_dt , ads_cd_cust_info_df .next_reg_annv  as next_reg_annv , ads_cd_cust_info_df .last_order_dt  as last_order_dt , ads_cd_cust_info_df .woa_status  as woa_status , ads_cd_cust_info_df .last_webtq_visit_duration  as last_webtq_visit_duration , ads_cd_cust_info_df .is_wecom_friend  as is_wecom_friend , ads_cd_cust_info_df .contactbility_sap  as contactbility_sap , ads_cd_cust_info_df . contactbility  as contactbility , ads_cd_cust_info_df .privacy_policy_dt  as privacy_policy_dt , ads_cd_cust_info_df .is_consent_privacy_policy  as is_consent_privacy_policy , ads_cd_cust_info_df .is_consent_cbdt  as is_consent_cbdt , ads_cd_cust_info_df .is_consent_sms_marketing  as is_consent_sms_marketing , ads_cd_cust_info_df .is_consent_email_marketing  as is_consent_email_marketing , ads_cd_cust_info_df .is_consent_phone_marketing  as is_consent_phone_marketing , ads_cd_cust_info_df .is_consent_postal_marketing  as is_consent_postal_marketing , ads_cd_cust_info_df .is_consent_social_media_marketing  as is_consent_social_media_marketing , ads_cd_cust_info_df .join_channel  as join_channel , ads_cd_cust_info_df .join_sub_channel  as join_sub_channel , ads_cd_cust_info_df .reg_channel  as reg_channel , ads_cd_cust_info_df .reg_sub_channel  as reg_sub_channel , ads_cd_cust_info_df .system_update_time  as system_update_time , ads_cd_cust_info_df .is_basket_customer  as is_basket_customer , ads_cd_cust_info_df .authorised_brand  as authorised_brand , ads_cd_cust_info_df .last_order_age  as last_order_age , ads_cd_cust_info_df .ref_sa_list  as ref_sa_list , ads_cd_cust_info_df .ref_btq_list  as ref_btq_list , ads_cd_cust_info_df .mobile_list  as mobile_list , ads_cd_cust_info_df .email_list  as email_list , ads_cd_cust_info_df .last_watch_order_age  as last_watch_order_age , ads_cd_cust_info_df .last_watch_order_dt  as last_watch_order_dt , ads_cd_cust_info_df . constellation  as constellation , ads_cd_cust_info_df . pt  as pt FROM datalake.ads_cd_cust_info_df WHERE  ads_cd_cust_info_df . pt >='20250512' and  ads_cd_cust_info_df . pt <='20250512' )  as dcdp_ads_cd_cust_info_df
    WHERE 1 = 1  and format_datetime(reg_dt, 'yyyy-MM-dd') >= '2017-05-01'  and format_datetime(reg_dt, 'yyyy-MM-dd') <= '2026-05-29'  and (  (unify_id is not null and unify_id <> '') )  and ( authorised_brand = 'VCA' )) t0 LEFT JOIN ( SELECT  ___id___ as ___id___, case when cast ( format_datetime (event_time,'HHmmssSSS') as bigint) > 0 then event_time else to_timestamp ( concat( format_datetime (event_time,'yyyy-MM-dd'), ' 22:00:00') ,'yyyy-mm-dd hh24:mi:ss') end   as ___time___ FROM     ( SELECT ads_cd_woa_event_df.unify_id as ___id___ , ads_cd_woa_event_df .unify_id  as unify_id , ads_cd_woa_event_df .union_id  as union_id , ads_cd_woa_event_df .woa_open_id  as woa_open_id , ads_cd_woa_event_df .sensor_user_id  as sensor_user_id , ads_cd_woa_event_df .event_type  as event_type , ads_cd_woa_event_df .behavior_type  as behavior_type , ads_cd_woa_event_df .event_dt  as event_dt , ads_cd_woa_event_df .event_time  as event_time , ads_cd_woa_event_df .event_channel  as event_channel , ads_cd_woa_event_df .event_sub_channel  as event_sub_channel , ads_cd_woa_event_df .subscribe_scene  as subscribe_scene , ads_cd_woa_event_df .subscribe_status  as subscribe_status , ads_cd_woa_event_df .msg_type  as msg_type , ads_cd_woa_event_df .app_name  as app_name , ads_cd_woa_event_df .authorised_brand  as authorised_brand , ads_cd_woa_event_df . pt  as pt FROM datalake.ads_cd_woa_event_df WHERE  ads_cd_woa_event_df . pt >='20250512' and  ads_cd_woa_event_df . pt <='20250512' AND ads_cd_woa_event_df .event_time  is not null  )  as dcdp_ads_cd_woa_event_df
    WHERE 1 = 1  and ( behavior_type in ('WOA Follow') )  and ( authorised_brand = 'VCA' )) t1 ON t1.___id___ = t0.___id___ AND t1.___time___ > t0.___time___ LEFT JOIN ( SELECT  ___id___ as ___id___, case when cast ( format_datetime (order_transaction_dt,'HHmmssSSS') as bigint) > 0 then order_transaction_dt else to_timestamp ( concat( format_datetime (order_transaction_dt,'yyyy-MM-dd'), ' 22:00:00') ,'yyyy-mm-dd hh24:mi:ss') end   as ___time___ FROM     ( SELECT ads_dd_order_item_df.unify_id as ___id___ , ads_dd_order_item_df .unify_id  as unify_id , ads_dd_order_item_df .bpartner_id  as bpartner_id , ads_dd_order_item_df .order_no  as order_no , ads_dd_order_item_df .order_type  as order_type , ads_dd_order_item_df .related_order_no  as related_order_no , ads_dd_order_item_df .order_channel  as order_channel , ads_dd_order_item_df .order_sub_channel  as order_sub_channel , ads_dd_order_item_df .order_line_no  as order_line_no , ads_dd_order_item_df .order_guest  as order_guest , ads_dd_order_item_df . article  as article , ads_dd_order_item_df .article_model  as article_model , ads_dd_order_item_df .item_qty  as item_qty , ads_dd_order_item_df .article_sn  as article_sn , ads_dd_order_item_df .amt_w_tax  as amt_w_tax , ads_dd_order_item_df .amt_w_tax_eur  as amt_w_tax_eur , ads_dd_order_item_df .amt_wo_tax  as amt_wo_tax , ads_dd_order_item_df .amt_wo_tax_eur  as amt_wo_tax_eur , ads_dd_order_item_df .discount_w_tax  as discount_w_tax , ads_dd_order_item_df .unit_price_w_tax  as unit_price_w_tax , ads_dd_order_item_df .unit_price_wo_tax  as unit_price_wo_tax , ads_dd_order_item_df .order_status  as order_status , ads_dd_order_item_df .order_item_status  as order_item_status , ads_dd_order_item_df .order_dt  as order_dt , ads_dd_order_item_df .order_time  as order_time , ads_dd_order_item_df .order_no_swse  as order_no_swse , ads_dd_order_item_df .order_source_channel  as order_source_channel , ads_dd_order_item_df .order_transaction_dt  as order_transaction_dt , ads_dd_order_item_df .order_payment_type  as order_payment_type , ads_dd_order_item_df .order_payment_dt  as order_payment_dt , ads_dd_order_item_df .order_shipping_dt  as order_shipping_dt , ads_dd_order_item_df .swse_id  as swse_id , ads_dd_order_item_df .staff_seller_id  as staff_seller_id , ads_dd_order_item_df .order_btq_cd  as order_btq_cd , ads_dd_order_item_df .order_btq_name  as order_btq_name , ads_dd_order_item_df .order_btq_city  as order_btq_city , ads_dd_order_item_df .btq_region  as btq_region , ads_dd_order_item_df .article_flag  as article_flag , ads_dd_order_item_df .article_name  as article_name , ads_dd_order_item_df .article_size_type  as article_size_type , ads_dd_order_item_df .article_gender  as article_gender , ads_dd_order_item_df .article_stone_type  as article_stone_type , ads_dd_order_item_df .article_main_material  as article_main_material , ads_dd_order_item_df .article_material_type  as article_material_type , ads_dd_order_item_df .article_colour  as article_colour , ads_dd_order_item_df .is_material_incl_diamond  as is_material_incl_diamond , ads_dd_order_item_df .article_collection  as article_collection , ads_dd_order_item_df .article_group  as article_group , ads_dd_order_item_df .article_hier_lev_1  as article_hier_lev_1 , ads_dd_order_item_df .article_hier_lev_2  as article_hier_lev_2 , ads_dd_order_item_df .article_hier_lev_3  as article_hier_lev_3 , ads_dd_order_item_df .article_hier_lev_4  as article_hier_lev_4 , ads_dd_order_item_df .article_hier_lev_5  as article_hier_lev_5 , ads_dd_order_item_df .article_hier_lev_6  as article_hier_lev_6 , ads_dd_order_item_df .article_hier_lev_7  as article_hier_lev_7 , ads_dd_order_item_df .order_category_ori  as order_category_ori , ads_dd_order_item_df .order_type_cd_ori  as order_type_cd_ori , ads_dd_order_item_df .order_type_desc_ori  as order_type_desc_ori , ads_dd_order_item_df .order_sub_channel_desc_ori  as order_sub_channel_desc_ori , ads_dd_order_item_df .is_basket_order  as is_basket_order , ads_dd_order_item_df .is_bi_valid_order  as is_bi_valid_order , ads_dd_order_item_df .authorised_brand  as authorised_brand , ads_dd_order_item_df .update_time  as update_time , ads_dd_order_item_df .order_btq_id  as order_btq_id , ads_dd_order_item_df . pt  as pt FROM datalake.ads_dd_order_item_df WHERE  ads_dd_order_item_df . pt >='20250512' and  ads_dd_order_item_df . pt <='20250512' AND ads_dd_order_item_df .order_dt  is not null  )  as dcdp_ads_dd_order_item_df
    WHERE 1 = 1  and ( order_type in ('Exchange_Sale','Sale') )  and ( authorised_brand = 'VCA' )) t2 ON t2.___id___ = t1.___id___ AND t2.___time___ > t1.___time___ ) GROUP BY ___id___ ,record ) funnel ON segmentation.___id___ = funnel.___id___ ) temp GROUP BY segmentationindex, segmentationname, time0

Assistant Example

SQL Assistant