# Insert from temp\_leads

## Select all data are exists

{% code title="check data" overflow="wrap" lineNumbers="true" %}

```plsql
SELECT t.*
FROM public.temp_leads t
WHERE 
    EXISTS (
        SELECT 1
        FROM public.leads l
        WHERE l.uid = t.id OR l.phone = t.phone
    )
    OR EXISTS (
        SELECT 1
        FROM public.family f
        WHERE f.uid = t.id  OR f.family_phone = t.phone
    )
ORDER BY t.id;
```

{% endcode %}

## Insert from temp\_leads to leads

{% code title="Insert into public.leads" overflow="wrap" lineNumbers="true" %}

```plsql
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,
        -- Ranking: tetap PARTITION BY phone, urut waktu ascending
        ROW_NUMBER() OVER (PARTITION BY t.phone ORDER BY t."timestamp") AS row_num
    FROM public.temp_leads t
    WHERE 
        -- Menyamakan dengan query pertama:
        NOT EXISTS (
            SELECT 1
            FROM public.leads l
            WHERE l.phone = t.phone
        )
        AND NOT EXISTS (
            SELECT 1
            FROM public.family f
            WHERE f.family_phone = t.phone
        )
        AND t.phone IS NOT NULL
)
INSERT INTO public.leads (
    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,
    leads_qualification,
    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
)
SELECT 
    t.id AS uid,
    t."timestamp",
    t.phone,
    t.email,
    t.name,
    t.gender,
    t.dob AS date_of_birth,
    NULL AS age,
    NULL AS age_group,
    t.branch,
    NULL AS language,
    t.contact_owner,
    t.source,
    t.sremarks,
    t.content,
    t.medium,
    t.mremarks,
    NULL AS assigned_pic,
    t.interest,
    COALESCE(t.free_trial, t.content, t.leads_notes) AS leads_notes,
    t.leads_qualification,
    NULL AS user_type,
    NULL AS kyzn_account,
    t.sleekflow_id,
    NULL AS leads_date,
    NULL AS mql_date,
    NULL AS "mqlPlus_date",
    NULL AS "tourPlus_date",
    NULL AS convert_date,
    NULL AS loss_date,
    t.tour_date,
    NULL AS "is_tour_show?",
    NULL AS trial_date,
    NULL AS "is_trial_show?",
    TRUE AS "is_inbound?",
    NULL AS "is_leads?",
    NULL AS "is_mql?",
    NULL AS "is_mqlPlus?",
    NULL AS "is_tourPlus?",
    NULL AS "is_trialPlus?",
    NULL AS "is_convert?",
    t.interest_remarks,
    NULL AS submitted_email,
    NULL AS assigned_email,
    NULL AS "trialPlus_date",
    NULL AS membership_name,
    NULL AS package_name,
    NULL AS start_membership,
    NULL AS member_id,
    NULL AS total_amount,
    NULL AS payment_method,
    NULL AS promotion,
    NULL AS ref_program,
    t.referrer AS ref_id,
    NULL AS ref_name,
    NULL AS ref_phone,
    NULL AS identity_photo,
    NULL AS status,
    NULL AS convert_by,
    NULL AS end_membership,
    NULL AS signature,
    NULL AS contract,
    t.offer_trial,
    t.reply_after_offer,
    NULL AS last_appt_category,
    t."conversationId",
    t.assigned_date,
    NULL AS last_created_by,
    NULL AS is_renewal,
    NULL AS approach_by,
    NULL AS app_status,
    NULL AS start_approach,
    NULL AS expired_approach
FROM rankedtempleads t
WHERE row_num = 1;
```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://work.bahrul.me/work/kyzn/database-management/query-checking/insert-from-temp_leads.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.