Первый звонок по phone_numbers

-- https://tracker.yandex.ru/ANALYTICS-3275

-- Описание логики на вики п.2 : https://wiki.yandex.ru/analytics/unificirovannye-metriki/raspredelenie-sdelok-po-menedzheram-1-linii/
--explain

WITH pupils_filtered AS (
-- выбор учеников
    SELECT
        pupils.pupil_id,
        pupils.create_time as create_time,
        amocrm_leads.created_at date_created_lead,
        pupils.amo_lead_id,
        responsible_amocrm_user_id,
        mart.pupils.ref_by

FROM mart.pupils
    JOIN mart.amocrm_leads
        ON pupils.amo_lead_id= amocrm_leads.amo_lead_id

   AND (NOT amocrm_leads.is_deleted)
        AND (NOT tutor_pipeline_presence)
        --сделки не должны относиться к преподавателям, так же они могут выйти из воронки преподавателей в другую воронку, нам нужно исключить такие сделки из поиска (ANALYTICS-1478)

WHERE TRUE
        and pupils.create_time::date >= '2024-01-17'::date
        AND "mart"."pupils"."create_date" BETWEEN timestamp with time zone '2024-12-01 00:00:00.000Z' AND timestamp with time zone '2024-12-18 00:00:00.000Z'

        --https://tracker.yandex.ru/ANALYTICS-3327


        --пример сделок с мини ВУ
        --AND mart.pupils.amo_lead_id IN (34083314,35959229,35960371,35962601,35962841,35962957,35963839)
),
needed_events AS (
    SELECT
        events_with_lessons.event_id,
        events_with_lessons.created_at,
        sales_managers_ids.email AS manager_email,
        sales_managers_ids.employee_name AS manager_name,
        sales_managers_ids.teamlead,
        sales_managers_ids.sales_department,
        events_with_lessons.planned_by_pupil,
        pupils_filtered.pupil_id,
        pupils_filtered.create_time,
        number_of_booking,
        primacy_of_finished_intro,
        start_time,
        --https://tracker.yandex.ru/ANALYTICS-3481
        --разметим мини ВУ
        mart.events_with_lessons.is_mini_group_intro_lesson,
        pupils_filtered.ref_by
    FROM pupils_filtered
    JOIN mart.events_with_lessons
        ON events_with_lessons.pupil_id = pupils_filtered.pupil_id

LEFT JOIN mart.sales_managers_ids
        ON mart.events_with_lessons.auction_or_event_created_by = sales_managers_ids.tetrika_user_id
        AND events_with_lessons.created_at >= sales_managers_ids.hire_date
        AND events_with_lessons.created_at <= sales_managers_ids.dismissal_date
    WHERE 1=1
        AND (events_with_lessons.number_of_booking = 1
    OR primacy_of_finished_intro = 1)
        AND mart.events_with_lessons.created_at::date >= '2024-01-01'
        and mart.events_with_lessons.is_intro_lesson = 1
        and mart.events_with_lessons.lesson_not_cancelled_in_1_hour
),
vy_lessons AS (
    SELECT
        needed_events.event_id,
        needed_events.created_at,
        needed_events.manager_email,
        needed_events.manager_name,
        needed_events.teamlead,
        needed_events.sales_department,
        needed_events.planned_by_pupil,
        needed_events.pupil_id,
        needed_events.create_time,
        --https://tracker.yandex.ru/ANALYTICS-3481
        is_mini_group_intro_lesson,
        needed_events.ref_by
    FROM needed_events
    WHERE needed_events.number_of_booking = 1
),
-- https://tracker.yandex.ru/ANALYTICS-3275
naumen_calls AS (
-- определяем почту первого менеджера, на которого распределился звонок в Naumen
-- учитываются только те звонки, что произошли в рамках согласованных заказчиком проектов.
    SELECT
        mart.naumen_calls.phone_numbers,
        amo_lead_id,
        pupil_id,
        email,
        attempt_start,
        ROW_NUMBER() OVER (PARTITION BY phone_numbers
ORDER BY attempt_start) AS rn
    FROM mart.naumen_calls
    WHERE 1=1
        AND project_uuid IN (   'corebo00000000000onj788922ojjjvc', -- Проект 1линия Записи УЧИ.Дома
                                'corebo00000000000oquar33hkk692ko', -- Реактивация 1линия Записи УЧИ.Дома
                                'corebo00000000000oqca15dbh5npodg', -- Обзвон Автоответчиков
                                'corebo00000000000offehadeg337ve0', -- Проект Основная воронка
                                'corebo00000000000oq6rtbo1n8omgm0', -- Реактивация Основная воронка
                                'corebo00000000000outp9p3qlva0hcs', -- Проект 1линия_Теплый_трафик_new,
                                'corebo00000000000otqmsmb32734gmo' -- реактивация Неактивного ученика --https://tracker.yandex.ru/ANALYTICS-3683
                            )
        AND email IS NOT NULL

        and mart.naumen_calls.attempt_start::date >= '2024-01-01'
    ORDER BY attempt_start
),
-- https://tracker.yandex.ru/ANALYTICS-3275
naumen_calls_with_teamlead_info AS (
-- к первому менеджеру, на которого распределился звонок в Naumen, добавляем информацию о его тимлиде
    SELECT
        naumen_calls.amo_lead_id,
        -- https://tracker.yandex.ru/ANALYTICS-3275
        naumen_calls.pupil_id,
        sales_managers_ids.email AS manager_email,
        sales_managers_ids.employee_name AS manager_name,
        sales_managers_ids.teamlead,
        sales_managers_ids.sales_department,
        naumen_calls.attempt_start
    FROM naumen_calls
    LEFT JOIN mart.sales_managers_ids
        ON sales_managers_ids.email = naumen_calls.email
        AND naumen_calls.attempt_start BETWEEN sales_managers_ids.hire_date AND sales_managers_ids.dismissal_date
    WHERE naumen_calls.rn = 1
    -- реализация с 1/0 была бы скорей всего сложнее, потому я просто выключаю кусок кода "кнопкой" empty_events_logic_filter
    -- этот кусок кода нужендля того чтобы если не нашлось звонков в Naumen, то брали бы текущего ОМ по сделке при этом не нарушая всю предыдущую логику

),
first_sales_responsibles AS (
-- первые оплаты и ответственные менеджеры по сделкам во время оплаты
    SELECT
        pupils_filtered.pupil_id,
        pupils_filtered.amo_lead_id,
        sales_managers_ids.employee_name ,
        sales_managers_ids.teamlead ,
        sales_managers_ids.email AS manager_email,
        sales_managers_ids.sales_department,
        pupils_with_payments_info.first_payment_at
    FROM pupils_filtered
    JOIN mart.pupils_with_payments_info
        ON pupils_with_payments_info.pupil_id =pupils_filtered.pupil_id
    LEFT JOIN mart.leads_responsible_users_changes_v4
        ON pupils_filtered.amo_lead_id =leads_responsible_users_changes_v4.amo_lead_id
        AND pupils_with_payments_info.first_payment_at BETWEEN leads_responsible_users_changes_v4.responsible_from AND leads_responsible_users_changes_v4.responsible_to
    LEFT JOIN mart.sales_managers_ids
        ON leads_responsible_users_changes_v4.responsible_user_id =sales_managers_ids.amocrm_user_id
        AND pupils_with_payments_info.first_payment_at BETWEEN sales_managers_ids.hire_date AND sales_managers_ids.dismissal_date
    WHERE TRUE
    and first_payment_at::date >= '2024-01-01'
),
leads_events_info AS (
    -- информация по лиду и действиям первой линии по этому лиду
    -- https://tracker.yandex.ru/ANALYTICS-3275
    -- заменил CTE 'union_events' из предыдущей версии отчета на CTE 'naumen_calls_with_teamlead_info'
    SELECT
        COALESCE(naumen_by_pupil.amo_lead_id, naumen_by_lead.amo_lead_id) AS amo_lead_id,
        date_created_lead,
        --добавим инфу по мини
        --https://tracker.yandex.ru/ANALYTICS-3481
        vy_lessons.is_mini_group_intro_lesson,
        first_sales_responsibles.first_payment_at AS first_sales_responsibles_first_payment_at,
        vy_lessons.created_at AS vy_lessons_created_at,
        naumen_by_lead.attempt_start AS naumen_by_lead_attempt_start,
        naumen_by_pupil.attempt_start AS naumen_by_pupil_attempt_start,
        COALESCE(first_sales_responsibles.teamlead, vy_lessons.teamlead, naumen_by_lead.teamlead, naumen_by_pupil.teamlead) AS teamlead,
        COALESCE(first_sales_responsibles.employee_name, vy_lessons.manager_name, naumen_by_lead.manager_name, naumen_by_pupil.manager_name) AS manager_name,
        COALESCE(first_sales_responsibles.manager_email, vy_lessons.manager_email, naumen_by_lead.manager_email, naumen_by_pupil.manager_email) AS email,
        COALESCE(naumen_by_lead.attempt_start, naumen_by_pupil.attempt_start) AS created_at,
        pupils_filtered.pupil_id,
        pupils_filtered.ref_by,
        (first_sales_responsibles.pupil_id is not null)::int payment_flag,  -- флаг о том что оплата была произведена раньше ВУ
    -- ставим отметку какой по счету менеджер работал с этим ли) чтобы новые лиды учесть только первому менеджеру
    -- https://tracker.yandex.ru/ANALYTICS-3275:
    -- здесь заменил ORDER BY union_events.created_at на COALESCE, так как порой значение в naumen_calls_with_teamlead_info.attempt_start пустое
        ROW_NUMBER() OVER (PARTITION BY pupils_filtered.pupil_id ORDER BY
                            first_sales_responsibles.first_payment_at,
                            vy_lessons.created_at,
                            naumen_by_lead.attempt_start,
                            naumen_by_pupil.attempt_start) AS num_manager
    FROM pupils_filtered
    -- https://tracker.yandex.ru/ANALYTICS-3275
    LEFT JOIN naumen_calls_with_teamlead_info AS naumen_by_lead
        ON pupils_filtered.amo_lead_id = naumen_by_lead.amo_lead_id
    LEFT JOIN naumen_calls_with_teamlead_info AS naumen_by_pupil
        ON pupils_filtered.pupil_id = naumen_by_pupil.pupil_id
    LEFT JOIN vy_lessons
        ON pupils_filtered.pupil_id = vy_lessons.pupil_id
    LEFT JOIN first_sales_responsibles
        ON pupils_filtered.pupil_id = first_sales_responsibles.pupil_id
        AND
            (
            first_sales_responsibles.first_payment_at < vy_lessons.created_at
            OR
            vy_lessons.created_at IS NULL
            )
    WHERE TRUE
    -- оставляем события по лиду только те, которые происходили после дня создания лида. есть события с датой более ранней, чем дата создания
    -- https://tracker.yandex.ru/ANALYTICS-3275:
    -- здесь заменил union_events.created_at::date на COALESCE, так как порой значение в naumen_calls_with_teamlead_info.attempt_start пустое
    AND COALESCE(first_sales_responsibles.first_payment_at, vy_lessons.created_at, naumen_by_lead.attempt_start, naumen_by_pupil.attempt_start)::date >= date_created_lead::date
    -- выбираем менеджеров, которые имеют отношение к 1-й линии
    AND COALESCE(first_sales_responsibles.sales_department, vy_lessons.sales_department, naumen_by_lead.sales_department, naumen_by_pupil.sales_department) = '1 линия'
    -- отсеиваем самозаписи, лиды у которых не было записей вообще, тоже должны оставаться
    AND (NOT planned_by_pupil OR planned_by_pupil IS NULL)
),
count_leads AS (
-- группируем по менеджеру и дате, считаем сколько он вел новых лидов
    SELECT
        teamlead,
        manager_name,
        email,
        COUNT(pupil_id) AS lead_new,
        count(pupil_id) filter (where ref_by is not null) lead_new_ref,
        sum(payment_flag) AS payment_new
    FROM leads_events_info
    WHERE num_manager = 1

GROUP BY 3,2,1
)
--select * from count_leads
,
first_vy_lessons AS (
-- первые записи по менеджерам
    SELECT
        COALESCE(vy_lessons.teamlead, 'not 1st line') as teamlead,
        COALESCE(vy_lessons.manager_name,'not 1st line') AS manager_name,
        COALESCE(vy_lessons.manager_email,'not 1st line') as email,
        --https://tracker.yandex.ru/ANALYTICS-3481
        COUNT(pupil_id) FILTER (WHERE is_mini_group_intro_lesson IS false) AS intros_new_ind,
        COUNT(pupil_id) FILTER (WHERE is_mini_group_intro_lesson) AS intros_new_mini,
        COUNT(pupil_id) FILTER (WHERE ref_by is not null) AS intros_ref
    FROM vy_lessons
    WHERE TRUE

    AND (created_at)::date <= timestamp with time zone '2024-12-19 00:00:00.000Z'
    GROUP BY 1,2,3
),
first_yvy_lessons AS (
-- первые УВУ по менеджерам
    SELECT
        needed_events.manager_name,
        needed_events.teamlead ,
        needed_events.manager_email,
        ----https://tracker.yandex.ru/ANALYTICS-3481
        COUNT(needed_events.pupil_id) FILTER (WHERE is_mini_group_intro_lesson IS false) fin_intros_new_ind,
        COUNT(needed_events.pupil_id) FILTER (WHERE is_mini_group_intro_lesson) AS fin_intros_new_mini
    FROM needed_events
    WHERE sales_department = '1 линия'
    AND primacy_of_finished_intro = 1

    AND start_time::date <= timestamp with time zone '2024-12-19 00:00:00.000Z'
    GROUP BY 1,2,3
),
first_sales AS (
-- все первые оплаты по менеджерам (не только те, которые проходили до первой записи на ВУ)
    SELECT
        first_sales_responsibles.teamlead,
        first_sales_responsibles.employee_name AS manager_name,
        first_sales_responsibles.manager_email,
        COUNT(first_sales_responsibles.pupil_id) AS first_sale_new
    FROM first_sales_responsibles
    INNER JOIN pupils_filtered
        ON pupils_filtered.pupil_id = first_sales_responsibles.pupil_id
    WHERE 1=1
    AND first_sales_responsibles.sales_department = '1 линия'

    AND first_payment_at::date <= timestamp with time zone '2024-12-19 00:00:00.000Z'
    GROUP BY 1,2,3
),
final_table AS (
    SELECT
        COALESCE(count_leads.teamlead, intros.teamlead, first_yvy_lessons.teamlead, first_sales.teamlead) as teamlead,                                    --так как по лидам у некоторых по нулям, пришлось выбирать из тимлидов и менеджеров по лидам/первым оплатам
        COALESCE(count_leads.manager_name, intros.manager_name, first_yvy_lessons.manager_name, first_sales.manager_name) as manager_name,
        COALESCE(count_leads.email, intros.email, first_yvy_lessons.manager_email, first_sales.manager_email) as manager_mail,
        COALESCE(lead_new, 0) as lead_new,
        COALESCE(count_leads.payment_new, 0) AS payment_before_vu_new,
        COALESCE(first_sales.first_sale_new, 0) AS first_sale_new,
        COALESCE(intros_new_ind, 0) as intros_new_ind,
        COALESCE(intros_new_mini, 0) as intros_new_mini,
        CASE
            WHEN lead_new > 0 THEN ROUND(intros_new_ind::NUMERIC / lead_new, 2)
            ELSE 0
        END AS convertion_intros_new_ind,
        COALESCE(fin_intros_new_ind, 0) as fin_intros_new_ind,
        COALESCE(fin_intros_new_mini, 0) as fin_intros_new_mini,
        CASE
            WHEN intros_new_ind > 0 THEN ROUND(fin_intros_new_ind::NUMERIC / intros_new_ind, 2)
            ELSE 0
        END AS convertion_fin_intros_new_ind,
        CASE
            WHEN lead_new > 0 THEN ROUND(fin_intros_new_ind::NUMERIC / lead_new, 2)
            ELSE 0
        END AS convertion_trough_new_ind,
        COALESCE(lead_new_ref, 0) as lead_new_ref,
        COALESCE(intros_ref,0) as intros_ref
    FROM count_leads
    LEFT JOIN first_vy_lessons AS intros
        ON intros.email = count_leads.email
        AND intros.teamlead = count_leads.teamlead
    LEFT JOIN first_yvy_lessons
        ON first_yvy_lessons.manager_email = COALESCE(intros.email, count_leads.email)
        and first_yvy_lessons.teamlead = COALESCE(intros.teamlead, count_leads.teamlead)
    FULL JOIN first_sales   -- здесь фулл джойн на все первые оплаты, а не только на те оплаты которые произошли до ВУ
        ON first_sales.manager_email = COALESCE(first_yvy_lessons.manager_email, intros.email, count_leads.email)
        and first_sales.teamlead = COALESCE(first_yvy_lessons.teamlead, intros.teamlead, count_leads.teamlead)
)
SELECT *
FROM naumen_calls
WHERE TRUE = TRUE
    AND phone_numbers = '+79260635655'