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

1. 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).

2. 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.

3. 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;