TAC Combined

view name = 'combined'

[NEW] Query

 WITH rankedtempleads AS (
         SELECT t.id,
            t."timestamp",
            t.branch,
            t.name,
            t.phone,
            t.email,
            t.dob,
            t.source,
            t.sremarks,
            t.medium,
            t.mremarks,
            t.content,
            t.pic,
            t.referrer,
            t."currentPath",
            t."agree_privacyPolicy",
            t.agree_newsletter,
            t.gclid,
            t.google_key,
            t.ttclid,
            t.form_id,
            t.gcampaign_id,
            t.gadgroup_id,
            t.gcreative_id,
            t.category_program,
            t.trial_day,
            t.free_trial,
            t.fbclid,
            t.fadset_id,
            t.fcampaign_id,
            t.fadset_name,
            t.fcampaign_name,
            t.tour_date,
            t.tour_time,
            t."dup?",
            t.contact_owner,
            t.sleekflow_id,
            t."conversationId",
            t.leads_qualification,
            t.interest,
            t.interest_remarks,
            t.offer_trial,
            t.reply_after_offer,
            t.sremarks_id,
            t.uid,
            t."assigned?",
            t.assigned_date,
            t.member_id,
            t.gender,
            t.hide,
            t.leads_notes,
            t.family_id,
            row_number() OVER (PARTITION BY t.phone ORDER BY t."timestamp") AS row_num
           FROM temp_leads t
          WHERE NOT (EXISTS ( SELECT 1
                   FROM leads l
                  WHERE l.phone = t.phone)) AND NOT (EXISTS ( SELECT 1
                   FROM family f
                  WHERE f.family_phone = t.phone)) AND t.phone IS NOT NULL
        ), combined AS (
         SELECT leads.uid,
            leads."timestamp",
            leads.phone,
            leads.email,
            leads.name,
            leads.gender,
            leads.date_of_birth,
            leads.age,
            leads.age_group,
            leads.branch,
            leads.language,
            leads.contact_owner,
            leads.source,
            leads.sremarks,
            leads.content,
            leads.medium,
            leads.mremarks,
            leads.assigned_pic,
            leads.interest,
            leads.leads_notes,
            leads.user_type,
            leads.kyzn_account,
            leads.sleekflow_id,
            leads.leads_date,
            leads.mql_date,
            leads."mqlPlus_date",
            leads."tourPlus_date",
            leads.convert_date,
            leads.loss_date,
            leads.tour_date,
            leads."is_tour_show?",
            leads.trial_date,
            leads."is_trial_show?",
            leads."is_inbound?",
            leads."is_leads?",
            leads."is_mql?",
            leads."is_mqlPlus?",
            leads."is_tourPlus?",
            leads."is_trialPlus?",
            leads."is_convert?",
            leads.interest_remarks,
            leads.submitted_email,
            leads.assigned_email,
            leads."trialPlus_date",
            leads.membership_name,
            leads.package_name,
            leads.start_membership,
            leads.member_id,
            leads.total_amount,
            leads.payment_method,
            leads.promotion,
            leads.ref_program,
            leads.ref_id,
            leads.ref_name,
            leads.ref_phone,
            leads.identity_photo,
            leads.status,
            leads.convert_by,
            leads.end_membership,
            leads.signature,
            leads.contract,
            leads.offer_trial,
            leads.reply_after_offer,
            leads.last_appt_category,
            leads."conversationId",
            leads.assigned_date,
            leads.last_created_by,
            leads.is_renewal,
            leads.approach_by,
            leads.app_status,
            leads.start_approach,
            leads.expired_approach,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END AS appt_date,
            (to_char(date_trunc('week'::text, leads."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            (to_char(date_trunc('week'::text, leads.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql,
            (to_char(date_trunc('week'::text, leads."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus,
            (to_char(date_trunc('week'::text,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date,
            (to_char(date_trunc('week'::text, leads.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert,
            NULL::text AS family_id,
            leads.hide,
            leads.id,
            NULL::text AS family_relationship,
            leads.renewal_category,
            leads.convert_category,
            leads.is_package
           FROM leads
          WHERE leads.hide IS NULL OR leads.hide = false
        UNION ALL
         SELECT family.uid,
            family.created_date AS "timestamp",
            family.family_phone AS phone,
            family.email,
            family.family_name AS name,
            family.gender,
            family.date_of_birth,
            family.age,
            family.age_group,
            family.branch,
            NULL::text AS language,
            family.contact_owner,
            family.source,
            family.sremarks,
            NULL::text AS content,
            family.medium,
            family.mremarks,
            family.assigned_pic,
            NULL::text AS interest,
            NULL::text AS leads_notes,
            NULL::text AS user_type,
            family.kyzn_account,
            NULL::text AS sleekflow_id,
            family.leads_date,
            family.mql_date,
            family."mqlPlus_date",
            family."tourPlus_date",
            family.convert_date,
            NULL::timestamp without time zone AS loss_date,
            NULL::timestamp without time zone AS tour_date,
            NULL::boolean AS "is_tour_show?",
            family.trial_date,
            family."is_trial_show?",
            family."is_inbound?",
            family."is_leads?",
            family."is_mql?",
            family."is_mqlPlus?",
            family."is_tourPlus?",
            family."is_trialPlus?",
            family."is_convert?",
            NULL::text AS interest_remarks,
            NULL::text AS submitted_email,
            NULL::text AS assigned_email,
            family."trialPlus_date",
            family.membership_name,
            family.package_name,
            family.start_membership,
            family.member_id,
            family.total_amount,
            family.payment_method,
            family.promotion,
            NULL::text AS ref_program,
            NULL::text AS ref_id,
            NULL::text AS ref_name,
            family.family_phone AS ref_phone,
            NULL::text AS identity_photo,
            family.status,
            family.convert_by,
            family.end_membership,
            NULL::text AS signature,
            NULL::text AS contract,
            NULL::boolean AS offer_trial,
            NULL::boolean AS reply_after_offer,
            NULL::text AS last_appt_category,
            NULL::text AS "conversationId",
            family.assigned_date,
            NULL::text AS last_created_by,
            family.is_renewal,
            family.approach_by,
            NULL::text AS app_status,
            family.start_approach,
            family.expired_approach,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END AS appt_date,
            (to_char(date_trunc('week'::text, family.created_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.created_date) + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            (to_char(date_trunc('week'::text, family.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql,
            (to_char(date_trunc('week'::text, family."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus,
            (to_char(date_trunc('week'::text,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date,
            (to_char(date_trunc('week'::text, family.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert,
            family.family_id,
            family.hide,
            family.id,
            family.family_relationship,
            family.renewal_category,
            family.convert_category,
            family.is_package
           FROM family
          WHERE family.hide IS NULL OR family.hide = false
        UNION ALL
         SELECT t.uid,
            t."timestamp",
            t.phone,
            t.email,
            t.name,
            NULL::text AS gender,
            t.dob AS date_of_birth,
            NULL::text AS age,
            NULL::text AS age_group,
            t.branch,
            NULL::text AS language,
            t.contact_owner,
            t.source,
            t.sremarks,
            t.content,
            t.medium,
            t.mremarks,
            t.pic AS assigned_pic,
            t.interest,
            t.free_trial AS leads_notes,
            NULL::text AS user_type,
            NULL::text AS kyzn_account,
            t.sleekflow_id,
            NULL::timestamp without time zone AS leads_date,
            NULL::timestamp without time zone AS mql_date,
            NULL::timestamp without time zone AS "mqlPlus_date",
            NULL::timestamp without time zone AS "tourPlus_date",
            NULL::timestamp without time zone AS convert_date,
            NULL::timestamp without time zone AS loss_date,
            NULL::timestamp without time zone AS tour_date,
            NULL::boolean AS "is_tour_show?",
            NULL::timestamp without time zone AS trial_date,
            NULL::boolean AS "is_trial_show?",
            NULL::boolean AS "is_inbound?",
            NULL::boolean AS "is_leads?",
            NULL::boolean AS "is_mql?",
            NULL::boolean AS "is_mqlPlus?",
            NULL::boolean AS "is_tourPlus?",
            NULL::boolean AS "is_trialPlus?",
            NULL::boolean AS "is_convert?",
            t.interest_remarks,
            NULL::text AS submitted_email,
            NULL::text AS assigned_email,
            NULL::timestamp without time zone AS "trialPlus_date",
            NULL::text AS membership_name,
            NULL::text AS package_name,
            NULL::date AS start_membership,
            NULL::text AS member_id,
            NULL::bigint AS total_amount,
            NULL::text AS payment_method,
            NULL::text AS promotion,
            NULL::text AS ref_program,
            t.referrer AS ref_id,
            NULL::text AS ref_name,
            NULL::bigint AS ref_phone,
            NULL::text AS identity_photo,
            NULL::text AS status,
            NULL::text AS convert_by,
            NULL::date AS end_membership,
            NULL::text AS signature,
            NULL::text AS contract,
            t.offer_trial,
            t.reply_after_offer,
            NULL::text AS last_appt_category,
            t."conversationId",
            t.assigned_date,
            NULL::text AS last_created_by,
            NULL::boolean AS is_renewal,
            NULL::text AS approach_by,
            NULL::text AS app_status,
            NULL::timestamp without time zone AS start_approach,
            NULL::timestamp without time zone AS expired_approach,
            NULL::timestamp without time zone AS appt_date,
            (to_char(date_trunc('week'::text, t."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, t."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            NULL::text AS week_mql,
            NULL::text AS week_mqlplus,
            NULL::text AS week_appt_date,
            NULL::text AS week_convert,
            NULL::text AS family_id,
            t.hide,
            t.id,
            NULL::text AS family_relationship,
            NULL::text AS renewal_category,
            NULL::text AS convert_category,
            NULL::boolean AS is_package
           FROM rankedtempleads t
          WHERE t.row_num = 1
        )
 SELECT uid,
    "timestamp",
    phone,
    email,
    name,
    gender,
    date_of_birth,
    age,
    age_group,
    branch,
    language,
    contact_owner,
    source,
    sremarks,
    content,
    medium,
    mremarks,
    assigned_pic,
    interest,
    leads_notes,
    user_type,
    kyzn_account,
    sleekflow_id,
    leads_date,
    mql_date,
    "mqlPlus_date",
    "tourPlus_date",
    convert_date,
    loss_date,
    tour_date,
    "is_tour_show?",
    trial_date,
    "is_trial_show?",
    "is_inbound?",
    "is_leads?",
    "is_mql?",
    "is_mqlPlus?",
    "is_tourPlus?",
    "is_trialPlus?",
    "is_convert?",
    interest_remarks,
    submitted_email,
    assigned_email,
    "trialPlus_date",
    membership_name,
    package_name,
    start_membership,
    member_id,
    total_amount,
    payment_method,
    promotion,
    ref_program,
    ref_id,
    ref_name,
    ref_phone,
    identity_photo,
    status,
    convert_by,
    end_membership,
    signature,
    contract,
    offer_trial,
    reply_after_offer,
    last_appt_category,
    "conversationId",
    assigned_date,
    last_created_by,
    is_renewal,
    approach_by,
    app_status,
    start_approach,
    expired_approach,
    appt_date,
    week_inbound,
    week_mql,
    week_mqlplus,
    week_appt_date,
    week_convert,
    family_id,
    hide,
    id,
        CASE
            WHEN family_id IS NULL THEN uid
            ELSE family_id
        END AS cid,
    family_relationship,
    renewal_category,
    convert_category,
    is_package
   FROM combined
  WHERE phone IS NOT NULL;

Penjelasan Inti Query

WITH rankedtempleads AS (...)
- Membuat tabel sementara rankedtempleads dengan data dari tabel temp_leads.
- Hanya menyertakan nomor telepon yang tidak ada di tabel leads dan family.
- Menambahkan kolom row_num untuk memberi nomor urut berdasarkan phone dan timestamp.
- Hanya menyimpan satu data per nomor telepon (data dengan row_num = 1).
combined AS (...)
- Menggabungkan data dari tiga sumber: leads, family, dan rankedtempleads.
- Data digabung menggunakan operasi UNION ALL.
- Menambahkan beberapa kolom tambahan, seperti:
  - appt_date: Mengambil tanggal tour_date atau trial_date yang lebih besar.
  - Kolom mingguan (week_inbound, week_mql, dll.) untuk interval mingguan dari beberapa tanggal penting.
Bagian SELECT Utama
- Mengambil data dari tabel gabungan (combined).
- Menambahkan kolom cid:
  - Jika family_id kosong, gunakan uid.
  - Jika tidak, gunakan family_id.
- Hanya menyertakan data yang memiliki nomor telepon (phone IS NOT NULL).

Hasil Akhir:

Query ini menghasilkan gabungan data dari berbagai sumber (leads, family, temp_leads) yang unik per nomor telepon, dengan informasi tambahan seperti tanggal, status, hubungan keluarga, dan kategori konversi. Data diatur untuk mendukung analisis lebih lanjut.

[OLD] Query

 WITH combined AS (
         SELECT leads.uid,
            leads."timestamp",
            leads.phone,
            leads.email,
            leads.name,
            leads.gender,
            leads.date_of_birth,
            leads.age,
            leads.age_group,
            leads.branch,
            leads.language,
            leads.contact_owner,
            leads.source,
            leads.sremarks,
            leads.content,
            leads.medium,
            leads.mremarks,
            leads.assigned_pic,
            leads.interest,
            leads.leads_notes,
            leads.user_type,
            leads.kyzn_account,
            leads.sleekflow_id,
            leads.leads_date,
            leads.mql_date,
            leads."mqlPlus_date",
            leads."tourPlus_date",
            leads.convert_date,
            leads.loss_date,
            leads.tour_date,
            leads."is_tour_show?",
            leads.trial_date,
            leads."is_trial_show?",
            leads."is_inbound?",
            leads."is_leads?",
            leads."is_mql?",
            leads."is_mqlPlus?",
            leads."is_tourPlus?",
            leads."is_trialPlus?",
            leads."is_convert?",
            leads.interest_remarks,
            leads.submitted_email,
            leads.assigned_email,
            leads."trialPlus_date",
            leads.membership_name,
            leads.package_name,
            leads.start_membership,
            leads.member_id,
            leads.total_amount,
            leads.payment_method,
            leads.promotion,
            leads.ref_program,
            leads.ref_id,
            leads.ref_name,
            leads.ref_phone,
            leads.identity_photo,
            leads.status,
            leads.convert_by,
            leads.end_membership,
            leads.signature,
            leads.contract,
            leads.offer_trial,
            leads.reply_after_offer,
            leads.last_appt_category,
            leads."conversationId",
            leads.assigned_date,
            leads.last_created_by,
            leads.is_renewal,
            leads.approach_by,
            leads.app_status,
            leads.start_approach,
            leads.expired_approach,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END AS appt_date,
            (to_char(date_trunc('week'::text, leads."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            (to_char(date_trunc('week'::text, leads.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql,
            (to_char(date_trunc('week'::text, leads."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus,
            (to_char(date_trunc('week'::text,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text,
                CASE
                    WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date
                    WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date
                    WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date)
                    ELSE NULL::timestamp without time zone
                END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date,
            (to_char(date_trunc('week'::text, leads.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert,
            NULL::text AS family_id,
            leads.hide,
            leads.id,
            NULL::text AS family_relationship,
            leads.renewal_category,
            leads.convert_category,
            leads.is_package
           FROM leads
          WHERE leads.hide IS NULL OR leads.hide = false
        UNION ALL
         SELECT family.uid,
            family.created_date AS "timestamp",
            family.family_phone AS phone,
            family.email,
            family.family_name AS name,
            family.gender,
            family.date_of_birth,
            family.age,
            family.age_group,
            family.branch,
            NULL::text AS language,
            family.contact_owner,
            family.source,
            family.sremarks,
            NULL::text AS content,
            family.medium,
            family.mremarks,
            family.assigned_pic,
            NULL::text AS interest,
            NULL::text AS leads_notes,
            NULL::text AS user_type,
            family.kyzn_account,
            NULL::text AS sleekflow_id,
            family.leads_date,
            family.mql_date,
            family."mqlPlus_date",
            family."tourPlus_date",
            family.convert_date,
            NULL::timestamp without time zone AS loss_date,
            NULL::timestamp without time zone AS tour_date,
            NULL::boolean AS "is_tour_show?",
            family.trial_date,
            family."is_trial_show?",
            family."is_inbound?",
            family."is_leads?",
            family."is_mql?",
            family."is_mqlPlus?",
            family."is_tourPlus?",
            family."is_trialPlus?",
            family."is_convert?",
            NULL::text AS interest_remarks,
            NULL::text AS submitted_email,
            NULL::text AS assigned_email,
            family."trialPlus_date",
            family.membership_name,
            family.package_name,
            family.start_membership,
            family.member_id,
            family.total_amount,
            family.payment_method,
            family.promotion,
            NULL::text AS ref_program,
            NULL::text AS ref_id,
            NULL::text AS ref_name,
            family.family_phone AS ref_phone,
            NULL::text AS identity_photo,
            family.status,
            family.convert_by,
            family.end_membership,
            NULL::text AS signature,
            NULL::text AS contract,
            NULL::boolean AS offer_trial,
            NULL::boolean AS reply_after_offer,
            NULL::text AS last_appt_category,
            NULL::text AS "conversationId",
            family.assigned_date,
            NULL::text AS last_created_by,
            family.is_renewal,
            family.approach_by,
            NULL::text AS app_status,
            family.start_approach,
            family.expired_approach,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END AS appt_date,
            (to_char(date_trunc('week'::text, family.created_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.created_date) + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            (to_char(date_trunc('week'::text, family.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql,
            (to_char(date_trunc('week'::text, family."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus,
            (to_char(date_trunc('week'::text,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text,
                CASE
                    WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date
                    WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date
                    WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date)
                    ELSE NULL::timestamp without time zone
                END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date,
            (to_char(date_trunc('week'::text, family.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert,
            family.family_id,
            family.hide,
            family.id,
            family.family_relationship,
            family.renewal_category,
            family.convert_category,
            family.is_package
           FROM family
          WHERE family.hide IS NULL OR family.hide = false
        UNION ALL
         SELECT temp_leads.uid,
            temp_leads."timestamp",
            temp_leads.phone,
            temp_leads.email,
            temp_leads.name,
            NULL::text AS gender,
            temp_leads.dob AS date_of_birth,
            NULL::text AS age,
            NULL::text AS age_group,
            temp_leads.branch,
            NULL::text AS language,
            temp_leads.contact_owner,
            temp_leads.source,
            temp_leads.sremarks,
            temp_leads.content,
            temp_leads.medium,
            temp_leads.mremarks,
            temp_leads.pic AS assigned_pic,
            temp_leads.interest,
            temp_leads.free_trial AS leads_notes,
            NULL::text AS user_type,
            NULL::text AS kyzn_account,
            temp_leads.sleekflow_id,
            NULL::timestamp without time zone AS leads_date,
            NULL::timestamp without time zone AS mql_date,
            NULL::timestamp without time zone AS "mqlPlus_date",
            NULL::timestamp without time zone AS "tourPlus_date",
            NULL::timestamp without time zone AS convert_date,
            NULL::timestamp without time zone AS loss_date,
            NULL::timestamp without time zone AS tour_date,
            NULL::boolean AS "is_tour_show?",
            NULL::timestamp without time zone AS trial_date,
            NULL::boolean AS "is_trial_show?",
            NULL::boolean AS "is_inbound?",
            NULL::boolean AS "is_leads?",
            NULL::boolean AS "is_mql?",
            NULL::boolean AS "is_mqlPlus?",
            NULL::boolean AS "is_tourPlus?",
            NULL::boolean AS "is_trialPlus?",
            NULL::boolean AS "is_convert?",
            temp_leads.interest_remarks,
            NULL::text AS submitted_email,
            NULL::text AS assigned_email,
            NULL::timestamp without time zone AS "trialPlus_date",
            NULL::text AS membership_name,
            NULL::text AS package_name,
            NULL::date AS start_membership,
            NULL::text AS member_id,
            NULL::bigint AS total_amount,
            NULL::text AS payment_method,
            NULL::text AS promotion,
            NULL::text AS ref_program,
            temp_leads.referrer AS ref_id,
            NULL::text AS ref_name,
            NULL::bigint AS ref_phone,
            NULL::text AS identity_photo,
            NULL::text AS status,
            NULL::text AS convert_by,
            NULL::date AS end_membership,
            NULL::text AS signature,
            NULL::text AS contract,
            temp_leads.offer_trial,
            temp_leads.reply_after_offer,
            NULL::text AS last_appt_category,
            temp_leads."conversationId",
            temp_leads.assigned_date,
            NULL::text AS last_created_by,
            NULL::boolean AS is_renewal,
            NULL::text AS approach_by,
            NULL::text AS app_status,
            NULL::timestamp without time zone AS start_approach,
            NULL::timestamp without time zone AS expired_approach,
            NULL::timestamp without time zone AS appt_date,
            (to_char(date_trunc('week'::text, temp_leads."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, temp_leads."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound,
            NULL::text AS week_mql,
            NULL::text AS week_mqlplus,
            NULL::text AS week_appt_date,
            NULL::text AS week_convert,
            NULL::text AS family_id,
            temp_leads.hide,
            temp_leads.id,
            NULL::text AS family_relationship,
            NULL::text AS renewal_category,
            NULL::text AS convert_category,
            NULL::boolean AS is_package
           FROM temp_leads
          WHERE temp_leads.phone IS NOT NULL AND temp_leads.uid IS NULL AND NOT (temp_leads.phone IN ( SELECT leads.phone
                   FROM leads)) AND NOT (temp_leads.phone IN ( SELECT family.family_phone
                   FROM family)) AND (temp_leads.hide IS NULL OR temp_leads.hide = false)
        )
 SELECT uid,
    "timestamp",
    phone,
    email,
    name,
    gender,
    date_of_birth,
    age,
    age_group,
    branch,
    language,
    contact_owner,
    source,
    sremarks,
    content,
    medium,
    mremarks,
    assigned_pic,
    interest,
    leads_notes,
    user_type,
    kyzn_account,
    sleekflow_id,
    leads_date,
    mql_date,
    "mqlPlus_date",
    "tourPlus_date",
    convert_date,
    loss_date,
    tour_date,
    "is_tour_show?",
    trial_date,
    "is_trial_show?",
    "is_inbound?",
    "is_leads?",
    "is_mql?",
    "is_mqlPlus?",
    "is_tourPlus?",
    "is_trialPlus?",
    "is_convert?",
    interest_remarks,
    submitted_email,
    assigned_email,
    "trialPlus_date",
    membership_name,
    package_name,
    start_membership,
    member_id,
    total_amount,
    payment_method,
    promotion,
    ref_program,
    ref_id,
    ref_name,
    ref_phone,
    identity_photo,
    status,
    convert_by,
    end_membership,
    signature,
    contract,
    offer_trial,
    reply_after_offer,
    last_appt_category,
    "conversationId",
    assigned_date,
    last_created_by,
    is_renewal,
    approach_by,
    app_status,
    start_approach,
    expired_approach,
    appt_date,
    week_inbound,
    week_mql,
    week_mqlplus,
    week_appt_date,
    week_convert,
    family_id,
    hide,
    id,
        CASE
            WHEN family_id IS NULL THEN uid
            ELSE family_id
        END AS cid,
    family_relationship,
    renewal_category,
    convert_category,
    is_package
   FROM combined
  WHERE phone IS NOT NULL;

PreviousQuery View NextTAC

Last updated 5 months ago

WITH rankedtempleads AS ( SELECT t.id, t."timestamp", t.branch, t.name, t.phone, t.email, t.dob, t.source, t.sremarks, t.medium, t.mremarks, t.content, t.pic, t.referrer, t."currentPath", t."agree_privacyPolicy", t.agree_newsletter, t.gclid, t.google_key, t.ttclid, t.form_id, t.gcampaign_id, t.gadgroup_id, t.gcreative_id, t.category_program, t.trial_day, t.free_trial, t.fbclid, t.fadset_id, t.fcampaign_id, t.fadset_name, t.fcampaign_name, t.tour_date, t.tour_time, t."dup?", t.contact_owner, t.sleekflow_id, t."conversationId", t.leads_qualification, t.interest, t.interest_remarks, t.offer_trial, t.reply_after_offer, t.sremarks_id, t.uid, t."assigned?", t.assigned_date, t.member_id, t.gender, t.hide, t.leads_notes, t.family_id, row_number() OVER (PARTITION BY t.phone ORDER BY t."timestamp") AS row_num FROM temp_leads t WHERE NOT (EXISTS ( SELECT 1 FROM leads l WHERE l.phone = t.phone)) AND NOT (EXISTS ( SELECT 1 FROM family f WHERE f.family_phone = t.phone)) AND t.phone IS NOT NULL ), combined AS ( SELECT leads.uid, leads."timestamp", leads.phone, leads.email, leads.name, leads.gender, leads.date_of_birth, leads.age, leads.age_group, leads.branch, leads.language, leads.contact_owner, leads.source, leads.sremarks, leads.content, leads.medium, leads.mremarks, leads.assigned_pic, leads.interest, leads.leads_notes, leads.user_type, leads.kyzn_account, leads.sleekflow_id, leads.leads_date, leads.mql_date, leads."mqlPlus_date", leads."tourPlus_date", leads.convert_date, leads.loss_date, leads.tour_date, leads."is_tour_show?", leads.trial_date, leads."is_trial_show?", leads."is_inbound?", leads."is_leads?", leads."is_mql?", leads."is_mqlPlus?", leads."is_tourPlus?", leads."is_trialPlus?", leads."is_convert?", leads.interest_remarks, leads.submitted_email, leads.assigned_email, leads."trialPlus_date", leads.membership_name, leads.package_name, leads.start_membership, leads.member_id, leads.total_amount, leads.payment_method, leads.promotion, leads.ref_program, leads.ref_id, leads.ref_name, leads.ref_phone, leads.identity_photo, leads.status, leads.convert_by, leads.end_membership, leads.signature, leads.contract, leads.offer_trial, leads.reply_after_offer, leads.last_appt_category, leads."conversationId", leads.assigned_date, leads.last_created_by, leads.is_renewal, leads.approach_by, leads.app_status, leads.start_approach, leads.expired_approach, CASE WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date) ELSE NULL::timestamp without time zone END AS appt_date, (to_char(date_trunc('week'::text, leads."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound, (to_char(date_trunc('week'::text, leads.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql, (to_char(date_trunc('week'::text, leads."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus, (to_char(date_trunc('week'::text, CASE WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date) ELSE NULL::timestamp without time zone END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, CASE WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date) ELSE NULL::timestamp without time zone END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date, (to_char(date_trunc('week'::text, leads.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert, NULL::text AS family_id, leads.hide, leads.id, NULL::text AS family_relationship, leads.renewal_category, leads.convert_category, leads.is_package FROM leads WHERE leads.hide IS NULL OR leads.hide = false UNION ALL SELECT family.uid, family.created_date AS "timestamp", family.family_phone AS phone, family.email, family.family_name AS name, family.gender, family.date_of_birth, family.age, family.age_group, family.branch, NULL::text AS language, family.contact_owner, family.source, family.sremarks, NULL::text AS content, family.medium, family.mremarks, family.assigned_pic, NULL::text AS interest, NULL::text AS leads_notes, NULL::text AS user_type, family.kyzn_account, NULL::text AS sleekflow_id, family.leads_date, family.mql_date, family."mqlPlus_date", family."tourPlus_date", family.convert_date, NULL::timestamp without time zone AS loss_date, NULL::timestamp without time zone AS tour_date, NULL::boolean AS "is_tour_show?", family.trial_date, family."is_trial_show?", family."is_inbound?", family."is_leads?", family."is_mql?", family."is_mqlPlus?", family."is_tourPlus?", family."is_trialPlus?", family."is_convert?", NULL::text AS interest_remarks, NULL::text AS submitted_email, NULL::text AS assigned_email, family."trialPlus_date", family.membership_name, family.package_name, family.start_membership, family.member_id, family.total_amount, family.payment_method, family.promotion, NULL::text AS ref_program, NULL::text AS ref_id, NULL::text AS ref_name, family.family_phone AS ref_phone, NULL::text AS identity_photo, family.status, family.convert_by, family.end_membership, NULL::text AS signature, NULL::text AS contract, NULL::boolean AS offer_trial, NULL::boolean AS reply_after_offer, NULL::text AS last_appt_category, NULL::text AS "conversationId", family.assigned_date, NULL::text AS last_created_by, family.is_renewal, family.approach_by, NULL::text AS app_status, family.start_approach, family.expired_approach, CASE WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date) ELSE NULL::timestamp without time zone END AS appt_date, (to_char(date_trunc('week'::text, family.created_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.created_date) + '6 days'::interval, 'DD Mon'::text) AS week_inbound, (to_char(date_trunc('week'::text, family.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql, (to_char(date_trunc('week'::text, family."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus, (to_char(date_trunc('week'::text, CASE WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date) ELSE NULL::timestamp without time zone END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, CASE WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date) ELSE NULL::timestamp without time zone END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date, (to_char(date_trunc('week'::text, family.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert, family.family_id, family.hide, family.id, family.family_relationship, family.renewal_category, family.convert_category, family.is_package FROM family WHERE family.hide IS NULL OR family.hide = false UNION ALL SELECT t.uid, t."timestamp", t.phone, t.email, t.name, NULL::text AS gender, t.dob AS date_of_birth, NULL::text AS age, NULL::text AS age_group, t.branch, NULL::text AS language, t.contact_owner, t.source, t.sremarks, t.content, t.medium, t.mremarks, t.pic AS assigned_pic, t.interest, t.free_trial AS leads_notes, NULL::text AS user_type, NULL::text AS kyzn_account, t.sleekflow_id, NULL::timestamp without time zone AS leads_date, NULL::timestamp without time zone AS mql_date, NULL::timestamp without time zone AS "mqlPlus_date", NULL::timestamp without time zone AS "tourPlus_date", NULL::timestamp without time zone AS convert_date, NULL::timestamp without time zone AS loss_date, NULL::timestamp without time zone AS tour_date, NULL::boolean AS "is_tour_show?", NULL::timestamp without time zone AS trial_date, NULL::boolean AS "is_trial_show?", NULL::boolean AS "is_inbound?", NULL::boolean AS "is_leads?", NULL::boolean AS "is_mql?", NULL::boolean AS "is_mqlPlus?", NULL::boolean AS "is_tourPlus?", NULL::boolean AS "is_trialPlus?", NULL::boolean AS "is_convert?", t.interest_remarks, NULL::text AS submitted_email, NULL::text AS assigned_email, NULL::timestamp without time zone AS "trialPlus_date", NULL::text AS membership_name, NULL::text AS package_name, NULL::date AS start_membership, NULL::text AS member_id, NULL::bigint AS total_amount, NULL::text AS payment_method, NULL::text AS promotion, NULL::text AS ref_program, t.referrer AS ref_id, NULL::text AS ref_name, NULL::bigint AS ref_phone, NULL::text AS identity_photo, NULL::text AS status, NULL::text AS convert_by, NULL::date AS end_membership, NULL::text AS signature, NULL::text AS contract, t.offer_trial, t.reply_after_offer, NULL::text AS last_appt_category, t."conversationId", t.assigned_date, NULL::text AS last_created_by, NULL::boolean AS is_renewal, NULL::text AS approach_by, NULL::text AS app_status, NULL::timestamp without time zone AS start_approach, NULL::timestamp without time zone AS expired_approach, NULL::timestamp without time zone AS appt_date, (to_char(date_trunc('week'::text, t."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, t."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound, NULL::text AS week_mql, NULL::text AS week_mqlplus, NULL::text AS week_appt_date, NULL::text AS week_convert, NULL::text AS family_id, t.hide, t.id, NULL::text AS family_relationship, NULL::text AS renewal_category, NULL::text AS convert_category, NULL::boolean AS is_package FROM rankedtempleads t WHERE t.row_num = 1 ) SELECT uid, "timestamp", phone, email, name, gender, date_of_birth, age, age_group, branch, language, contact_owner, source, sremarks, content, medium, mremarks, assigned_pic, interest, leads_notes, user_type, kyzn_account, sleekflow_id, leads_date, mql_date, "mqlPlus_date", "tourPlus_date", convert_date, loss_date, tour_date, "is_tour_show?", trial_date, "is_trial_show?", "is_inbound?", "is_leads?", "is_mql?", "is_mqlPlus?", "is_tourPlus?", "is_trialPlus?", "is_convert?", interest_remarks, submitted_email, assigned_email, "trialPlus_date", membership_name, package_name, start_membership, member_id, total_amount, payment_method, promotion, ref_program, ref_id, ref_name, ref_phone, identity_photo, status, convert_by, end_membership, signature, contract, offer_trial, reply_after_offer, last_appt_category, "conversationId", assigned_date, last_created_by, is_renewal, approach_by, app_status, start_approach, expired_approach, appt_date, week_inbound, week_mql, week_mqlplus, week_appt_date, week_convert, family_id, hide, id, CASE WHEN family_id IS NULL THEN uid ELSE family_id END AS cid, family_relationship, renewal_category, convert_category, is_package FROM combined WHERE phone IS NOT NULL;

WITH combined AS ( SELECT leads.uid, leads."timestamp", leads.phone, leads.email, leads.name, leads.gender, leads.date_of_birth, leads.age, leads.age_group, leads.branch, leads.language, leads.contact_owner, leads.source, leads.sremarks, leads.content, leads.medium, leads.mremarks, leads.assigned_pic, leads.interest, leads.leads_notes, leads.user_type, leads.kyzn_account, leads.sleekflow_id, leads.leads_date, leads.mql_date, leads."mqlPlus_date", leads."tourPlus_date", leads.convert_date, leads.loss_date, leads.tour_date, leads."is_tour_show?", leads.trial_date, leads."is_trial_show?", leads."is_inbound?", leads."is_leads?", leads."is_mql?", leads."is_mqlPlus?", leads."is_tourPlus?", leads."is_trialPlus?", leads."is_convert?", leads.interest_remarks, leads.submitted_email, leads.assigned_email, leads."trialPlus_date", leads.membership_name, leads.package_name, leads.start_membership, leads.member_id, leads.total_amount, leads.payment_method, leads.promotion, leads.ref_program, leads.ref_id, leads.ref_name, leads.ref_phone, leads.identity_photo, leads.status, leads.convert_by, leads.end_membership, leads.signature, leads.contract, leads.offer_trial, leads.reply_after_offer, leads.last_appt_category, leads."conversationId", leads.assigned_date, leads.last_created_by, leads.is_renewal, leads.approach_by, leads.app_status, leads.start_approach, leads.expired_approach, CASE WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date) ELSE NULL::timestamp without time zone END AS appt_date, (to_char(date_trunc('week'::text, leads."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound, (to_char(date_trunc('week'::text, leads.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql, (to_char(date_trunc('week'::text, leads."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus, (to_char(date_trunc('week'::text, CASE WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date) ELSE NULL::timestamp without time zone END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, CASE WHEN leads.tour_date IS NULL AND leads.trial_date IS NOT NULL THEN leads.trial_date WHEN leads.trial_date IS NULL AND leads.tour_date IS NOT NULL THEN leads.tour_date WHEN leads.tour_date IS NOT NULL AND leads.trial_date IS NOT NULL THEN GREATEST(leads.tour_date, leads.trial_date) ELSE NULL::timestamp without time zone END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date, (to_char(date_trunc('week'::text, leads.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, leads.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert, NULL::text AS family_id, leads.hide, leads.id, NULL::text AS family_relationship, leads.renewal_category, leads.convert_category, leads.is_package FROM leads WHERE leads.hide IS NULL OR leads.hide = false UNION ALL SELECT family.uid, family.created_date AS "timestamp", family.family_phone AS phone, family.email, family.family_name AS name, family.gender, family.date_of_birth, family.age, family.age_group, family.branch, NULL::text AS language, family.contact_owner, family.source, family.sremarks, NULL::text AS content, family.medium, family.mremarks, family.assigned_pic, NULL::text AS interest, NULL::text AS leads_notes, NULL::text AS user_type, family.kyzn_account, NULL::text AS sleekflow_id, family.leads_date, family.mql_date, family."mqlPlus_date", family."tourPlus_date", family.convert_date, NULL::timestamp without time zone AS loss_date, NULL::timestamp without time zone AS tour_date, NULL::boolean AS "is_tour_show?", family.trial_date, family."is_trial_show?", family."is_inbound?", family."is_leads?", family."is_mql?", family."is_mqlPlus?", family."is_tourPlus?", family."is_trialPlus?", family."is_convert?", NULL::text AS interest_remarks, NULL::text AS submitted_email, NULL::text AS assigned_email, family."trialPlus_date", family.membership_name, family.package_name, family.start_membership, family.member_id, family.total_amount, family.payment_method, family.promotion, NULL::text AS ref_program, NULL::text AS ref_id, NULL::text AS ref_name, family.family_phone AS ref_phone, NULL::text AS identity_photo, family.status, family.convert_by, family.end_membership, NULL::text AS signature, NULL::text AS contract, NULL::boolean AS offer_trial, NULL::boolean AS reply_after_offer, NULL::text AS last_appt_category, NULL::text AS "conversationId", family.assigned_date, NULL::text AS last_created_by, family.is_renewal, family.approach_by, NULL::text AS app_status, family.start_approach, family.expired_approach, CASE WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date) ELSE NULL::timestamp without time zone END AS appt_date, (to_char(date_trunc('week'::text, family.created_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.created_date) + '6 days'::interval, 'DD Mon'::text) AS week_inbound, (to_char(date_trunc('week'::text, family.mql_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.mql_date) + '6 days'::interval, 'DD Mon'::text) AS week_mql, (to_char(date_trunc('week'::text, family."mqlPlus_date"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family."mqlPlus_date") + '6 days'::interval, 'DD Mon'::text) AS week_mqlplus, (to_char(date_trunc('week'::text, CASE WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date) ELSE NULL::timestamp without time zone END), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, CASE WHEN family.tour_date IS NULL AND family.trial_date IS NOT NULL THEN family.trial_date WHEN family.trial_date IS NULL AND family.tour_date IS NOT NULL THEN family.tour_date WHEN family.tour_date IS NOT NULL AND family.trial_date IS NOT NULL THEN GREATEST(family.tour_date, family.trial_date) ELSE NULL::timestamp without time zone END) + '6 days'::interval, 'DD Mon'::text) AS week_appt_date, (to_char(date_trunc('week'::text, family.convert_date), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, family.convert_date) + '6 days'::interval, 'DD Mon'::text) AS week_convert, family.family_id, family.hide, family.id, family.family_relationship, family.renewal_category, family.convert_category, family.is_package FROM family WHERE family.hide IS NULL OR family.hide = false UNION ALL SELECT temp_leads.uid, temp_leads."timestamp", temp_leads.phone, temp_leads.email, temp_leads.name, NULL::text AS gender, temp_leads.dob AS date_of_birth, NULL::text AS age, NULL::text AS age_group, temp_leads.branch, NULL::text AS language, temp_leads.contact_owner, temp_leads.source, temp_leads.sremarks, temp_leads.content, temp_leads.medium, temp_leads.mremarks, temp_leads.pic AS assigned_pic, temp_leads.interest, temp_leads.free_trial AS leads_notes, NULL::text AS user_type, NULL::text AS kyzn_account, temp_leads.sleekflow_id, NULL::timestamp without time zone AS leads_date, NULL::timestamp without time zone AS mql_date, NULL::timestamp without time zone AS "mqlPlus_date", NULL::timestamp without time zone AS "tourPlus_date", NULL::timestamp without time zone AS convert_date, NULL::timestamp without time zone AS loss_date, NULL::timestamp without time zone AS tour_date, NULL::boolean AS "is_tour_show?", NULL::timestamp without time zone AS trial_date, NULL::boolean AS "is_trial_show?", NULL::boolean AS "is_inbound?", NULL::boolean AS "is_leads?", NULL::boolean AS "is_mql?", NULL::boolean AS "is_mqlPlus?", NULL::boolean AS "is_tourPlus?", NULL::boolean AS "is_trialPlus?", NULL::boolean AS "is_convert?", temp_leads.interest_remarks, NULL::text AS submitted_email, NULL::text AS assigned_email, NULL::timestamp without time zone AS "trialPlus_date", NULL::text AS membership_name, NULL::text AS package_name, NULL::date AS start_membership, NULL::text AS member_id, NULL::bigint AS total_amount, NULL::text AS payment_method, NULL::text AS promotion, NULL::text AS ref_program, temp_leads.referrer AS ref_id, NULL::text AS ref_name, NULL::bigint AS ref_phone, NULL::text AS identity_photo, NULL::text AS status, NULL::text AS convert_by, NULL::date AS end_membership, NULL::text AS signature, NULL::text AS contract, temp_leads.offer_trial, temp_leads.reply_after_offer, NULL::text AS last_appt_category, temp_leads."conversationId", temp_leads.assigned_date, NULL::text AS last_created_by, NULL::boolean AS is_renewal, NULL::text AS approach_by, NULL::text AS app_status, NULL::timestamp without time zone AS start_approach, NULL::timestamp without time zone AS expired_approach, NULL::timestamp without time zone AS appt_date, (to_char(date_trunc('week'::text, temp_leads."timestamp"), 'DD Mon'::text) || ' - '::text) || to_char(date_trunc('week'::text, temp_leads."timestamp") + '6 days'::interval, 'DD Mon'::text) AS week_inbound, NULL::text AS week_mql, NULL::text AS week_mqlplus, NULL::text AS week_appt_date, NULL::text AS week_convert, NULL::text AS family_id, temp_leads.hide, temp_leads.id, NULL::text AS family_relationship, NULL::text AS renewal_category, NULL::text AS convert_category, NULL::boolean AS is_package FROM temp_leads WHERE temp_leads.phone IS NOT NULL AND temp_leads.uid IS NULL AND NOT (temp_leads.phone IN ( SELECT leads.phone FROM leads)) AND NOT (temp_leads.phone IN ( SELECT family.family_phone FROM family)) AND (temp_leads.hide IS NULL OR temp_leads.hide = false) ) SELECT uid, "timestamp", phone, email, name, gender, date_of_birth, age, age_group, branch, language, contact_owner, source, sremarks, content, medium, mremarks, assigned_pic, interest, leads_notes, user_type, kyzn_account, sleekflow_id, leads_date, mql_date, "mqlPlus_date", "tourPlus_date", convert_date, loss_date, tour_date, "is_tour_show?", trial_date, "is_trial_show?", "is_inbound?", "is_leads?", "is_mql?", "is_mqlPlus?", "is_tourPlus?", "is_trialPlus?", "is_convert?", interest_remarks, submitted_email, assigned_email, "trialPlus_date", membership_name, package_name, start_membership, member_id, total_amount, payment_method, promotion, ref_program, ref_id, ref_name, ref_phone, identity_photo, status, convert_by, end_membership, signature, contract, offer_trial, reply_after_offer, last_appt_category, "conversationId", assigned_date, last_created_by, is_renewal, approach_by, app_status, start_approach, expired_approach, appt_date, week_inbound, week_mql, week_mqlplus, week_appt_date, week_convert, family_id, hide, id, CASE WHEN family_id IS NULL THEN uid ELSE family_id END AS cid, family_relationship, renewal_category, convert_category, is_package FROM combined WHERE phone IS NOT NULL;