SQLize Online / PHPize Online  /  SQLtest Online

SELECT user_id, GA_Date as Date,mobile_number as Mobile_Number,user_first_name as First_Name,user_last_name as Last_Name, coalesce(Add_to_Cart, 0) as Add_to_Cart, coalesce(Product_view, 0) as Viewed_Products, coalesce(orders.Total_Orders, 0) as Total_Orders, coalesce(cd2.CD_Uploads, 0) as CD_Uploads, coalesce(kv2.KV_Posts, 0) as KV_Posts, NULLIF(crop_names, ARRAY[]) as Interested_Crops FROM ((SELECT ga.user_id, ga.GA_Date, ga.Add_to_cart, ga.Product_view, mdb_users.ecommerce_id, mdb_users.mobile_number,mdb_users.user_first_name,mdb_users.user_last_name, mdb_users.crop_names FROM (SELECT date(CAST(from_unixtime(CAST(event_timestamp / 1000000 AS BIGINT)) AS TIMESTAMP)AT TIME ZONE 'UTC' AT TIME ZONE '+05:30')as GA_Date, ait.user_id, sum(case when ait.event_name = 'add_to_cart' then 1 else 0 end) AS Add_to_cart, sum(case when ait.event_name = 'view_item' then 1 else 0 end) AS Product_view FROM adls2.ga_analytics.app_installations_tracker44 ait --where event_name='add_to_cart' or event_name='view_item' GROUP BY ait.user_id, date(CAST(from_unixtime(CAST(event_timestamp / 1000000 AS BIGINT)) AS TIMESTAMP)AT TIME ZONE 'UTC' AT TIME ZONE '+05:30') )as ga LEFT OUTER JOIN (SELECT au11.user_id, au11.ecommerce_id, au11.mobile_number,au11.user_first_name,au11.user_last_name, au11.crop_names FROM adls2.app_engagement.app_users11 au11) AS mdb_users ON (ga.user_id = mdb_users.user_id OR ga.user_id = cast(mdb_users.ecommerce_id AS VARCHAR)) --WHERE mdb_users.ecommerce_id = 6537196371 ) AS ga_users LEFT OUTER JOIN (SELECT cd_user_id, au11.ecommerce_id, cd_date, CD_Uploads FROM ((select cd.userid as cd_user_id, date(cd.createdon) as cd_date,count(cd.userid) as CD_Uploads FROM adls2.app_engagement.app_cd5 cd --WHERE cd.userid = '5f4f80f271a0b4478c55ce1d' GROUP BY cd.userid,date(cd.createdon)) AS cd1 LEFT OUTER JOIN adls2.app_engagement.app_users11 au11 ON cd1.cd_user_id = au11.user_id)) AS cd2 ON ga_users.GA_Date = cd2.cd_date AND (ga_users.user_id = cast(cd2.ecommerce_id AS VARCHAR)) LEFT OUTER JOIN (SELECT kv_user_id, au11.ecommerce_id AS kvecommid, kv_date, KV_Posts FROM ((select kv.userid as kv_user_id, date(kv.postedtime) as kv_date,count(kv.userid) as KV_Posts FROM adls2.app_engagement.app_posts12 kv --WHERE kv.userid = '5f4f80f271a0b4478c55ce1d' GROUP BY kv.userid,date(kv.postedtime)) AS kv1 LEFT OUTER JOIN adls2.app_engagement.app_users11 au11 ON kv1.kv_user_id = au11.user_id)) AS kv2 ON ga_users.GA_Date = kv2.kv_date AND (ga_users.user_id = cast(kv2.kvecommid AS VARCHAR)) LEFT OUTER JOIN (SELECT DISTINCT cast(from_iso8601_timestamp(created_at) AS DATE) AS order_date, customer_id, count(sh1name) as Total_Orders from (SELECT t1.sh1name, t2.customer_id, t2.created_at FROM ((select DISTINCT sh1.name AS sh1name,MAX (sh1.updated_at) AS sh1updated_at FROM adls2.shopifynovorders.shopifyorders32 sh1 GROUP BY sh1.name) AS t1 LEFT OUTER JOIN (SELECT DISTINCT sh2.name AS sh2name, sh2.created_at, sh2.updated_at AS sh2updated_at, sh2.customer.id AS customer_id FROM adls2.shopifynovorders.shopifyorders32 sh2) AS t2 ON t1.sh1name = t2.sh2name AND t1.sh1updated_at = t2.sh2updated_at)) --WHERE customer_id = 6537196371 GROUP BY customer_id, cast(from_iso8601_timestamp(created_at) AS DATE)) AS orders ON ga_users.user_id=cast(orders.customer_id as VARCHAR ) and ga_users.GA_Date = orders.order_date) --WHERE user_id = '5f4f80f271a0b4478c55ce1d' order by date desc