# TCC CAC

{% code title="tcc\_cac" overflow="wrap" lineNumbers="true" %}

```plsql
 WITH time_to_convert AS (
         WITH datawithday AS (
                 SELECT combined.branch,
                    combined.source,
                    combined.sremarks,
                    combined.medium,
                    combined.mremarks,
                    combined.contact_owner,
                    combined.approach_by,
                    combined.assigned_pic,
                    combined.convert_by,
                    combined.membership_name,
                    combined.package_name,
                    combined.convert_category,
                    date(combined."timestamp") AS inbound_date,
                    date(combined.leads_date) AS leads_date,
                    date(combined.mql_date) AS mql_date,
                    date(combined."mqlPlus_date") AS mqlplus_date,
                    date(combined."tourPlus_date") AS tourplus_date,
                    date(combined."trialPlus_date") AS trialplus_date,
                    date(combined.convert_date) AS convert_date,
                    COALESCE(GREATEST(0::double precision, date_part('day'::text, combined.convert_date - combined."timestamp")), 0::double precision) AS day,
                    COALESCE(GREATEST(0::double precision, date_part('day'::text, combined.leads_date - combined."timestamp")), 0::double precision) AS day_leads,
                    COALESCE(GREATEST(0::double precision, date_part('day'::text, combined.mql_date - combined.leads_date)), 0::double precision) AS day_mql,
                    COALESCE(GREATEST(0::double precision, date_part('day'::text, combined."mqlPlus_date" - combined.mql_date)), 0::double precision) AS day_mqlplus,
                    COALESCE(GREATEST(0::double precision, date_part('day'::text, combined."tourPlus_date" - combined."mqlPlus_date")), 0::double precision) AS day_tourplus,
                    COALESCE(GREATEST(0::double precision, date_part('day'::text, combined."trialPlus_date" - combined."tourPlus_date")), 0::double precision) AS day_trialplus,
                    COALESCE(GREATEST(0::double precision, date_part('day'::text, combined.convert_date - combined."trialPlus_date")), 0::double precision) AS day_convert,
                    combined.total_amount AS total_revenue
                   FROM combined
                  WHERE combined."timestamp" IS NOT NULL AND combined.phone IS NOT NULL
                ), groupeddata AS (
                 SELECT datawithday.branch,
                    datawithday.source,
                    datawithday.sremarks,
                    datawithday.medium,
                    datawithday.mremarks,
                    datawithday.contact_owner,
                    datawithday.approach_by,
                    datawithday.assigned_pic,
                    datawithday.convert_by,
                    datawithday.membership_name,
                    datawithday.package_name,
                    datawithday.convert_category,
                    datawithday.inbound_date,
                    datawithday.leads_date,
                    datawithday.mql_date,
                    datawithday.mqlplus_date,
                    datawithday.tourplus_date,
                    datawithday.trialplus_date,
                    datawithday.convert_date,
                    count(*) FILTER (WHERE datawithday.inbound_date IS NOT NULL) AS inbound,
                    count(*) FILTER (WHERE datawithday.leads_date IS NOT NULL) AS leads,
                    count(*) FILTER (WHERE datawithday.mql_date IS NOT NULL) AS mql,
                    count(*) FILTER (WHERE datawithday.mqlplus_date IS NOT NULL) AS mqlplus,
                    count(*) FILTER (WHERE datawithday.tourplus_date IS NOT NULL) AS tourplus,
                    count(*) FILTER (WHERE datawithday.trialplus_date IS NOT NULL) AS trialplus,
                    count(*) FILTER (WHERE datawithday.convert_date IS NOT NULL) AS convert,
                    max(datawithday.day) AS day,
                    max(datawithday.day_leads) AS day_leads,
                    max(datawithday.day_mql) AS day_mql,
                    max(datawithday.day_mqlplus) AS day_mqlplus,
                    max(datawithday.day_tourplus) AS day_tourplus,
                    max(datawithday.day_trialplus) AS day_trialplus,
                    max(datawithday.day_convert) AS day_convert,
                    sum(datawithday.total_revenue) AS total_revenue
                   FROM datawithday
                  GROUP BY datawithday.branch, datawithday.source, datawithday.sremarks, datawithday.medium, datawithday.mremarks, datawithday.contact_owner, datawithday.approach_by, datawithday.assigned_pic, datawithday.convert_by, datawithday.membership_name, datawithday.package_name, datawithday.convert_category, datawithday.inbound_date, datawithday.leads_date, datawithday.mql_date, datawithday.mqlplus_date, datawithday.tourplus_date, datawithday.trialplus_date, datawithday.convert_date
                )
         SELECT groupeddata.branch,
            groupeddata.source,
            groupeddata.sremarks,
            groupeddata.medium,
            groupeddata.mremarks,
            groupeddata.contact_owner,
            groupeddata.approach_by,
            groupeddata.assigned_pic,
            groupeddata.convert_by,
            groupeddata.membership_name,
            groupeddata.package_name,
            groupeddata.convert_category,
            groupeddata.inbound_date,
            groupeddata.leads_date,
            groupeddata.mql_date,
            groupeddata.mqlplus_date,
            groupeddata.tourplus_date,
            groupeddata.trialplus_date,
            groupeddata.convert_date,
            groupeddata.inbound,
            groupeddata.leads,
            groupeddata.mql,
            groupeddata.mqlplus,
            groupeddata.tourplus,
            groupeddata.trialplus,
            groupeddata.convert,
            groupeddata.day,
            groupeddata.day_leads,
            groupeddata.day_mql,
            groupeddata.day_mqlplus,
            groupeddata.day_tourplus,
            groupeddata.day_trialplus,
            groupeddata.day_convert,
            groupeddata.total_revenue
           FROM groupeddata
          ORDER BY groupeddata.inbound_date
        ), transaction_data AS (
         SELECT transaction.payment_date,
            transaction.branch,
            transaction.source,
            transaction.sremarks_id,
            sum(transaction.amount_idr) AS total_spent,
            count(transaction.payment_date) AS qty_transaction
           FROM transaction
          GROUP BY transaction.payment_date, transaction.branch, transaction.source, transaction.sremarks_id
        )
 SELECT COALESCE(tc.inbound_date, td.payment_date) AS inbound_date,
    COALESCE(tc.day, 0::double precision) AS day,
    COALESCE(tc.inbound, 0::bigint)::numeric AS inbound,
    COALESCE(tc.day_mql, 0::double precision) AS day_mql,
    COALESCE(tc.leads, 0::bigint)::numeric AS leads,
    COALESCE(tc.day_leads, 0::double precision) AS day_leads,
    COALESCE(tc.mql, 0::bigint)::numeric AS mql,
    COALESCE(tc.day_mqlplus, 0::double precision) AS day_mqlplus,
    COALESCE(tc.mqlplus, 0::bigint)::numeric AS mqlplus,
    COALESCE(tc.day_tourplus, 0::double precision) AS day_tourplus,
    COALESCE(tc.tourplus, 0::bigint)::numeric AS tourplus,
    COALESCE(tc.day_trialplus, 0::double precision) AS day_trialplus,
    COALESCE(tc.trialplus, 0::bigint)::numeric AS trialplus,
    COALESCE(tc.day_convert, 0::double precision) AS day_convert,
    COALESCE(tc.convert, 0::bigint)::numeric AS convert,
    tc.leads_date,
    tc.mql_date,
    tc.mqlplus_date,
    tc.tourplus_date,
    tc.trialplus_date,
    tc.convert_date,
    count(td.payment_date) AS qty_transaction,
    COALESCE(tc.branch, td.branch) AS branch,
    COALESCE(tc.source, td.source) AS source,
    COALESCE(tc.sremarks, td.sremarks_id) AS sremarks,
    COALESCE(tc.medium, NULL::text) AS medium,
    COALESCE(tc.mremarks, NULL::text) AS mremarks,
    COALESCE(tc.contact_owner, NULL::text) AS contact_owner,
    COALESCE(tc.approach_by, NULL::text) AS approach_by,
    COALESCE(tc.assigned_pic, NULL::text) AS assigned_pic,
    COALESCE(tc.convert_by, NULL::text) AS convert_by,
    COALESCE(tc.membership_name, NULL::text) AS membership_name,
    COALESCE(tc.package_name, NULL::text) AS package_name,
    COALESCE(tc.convert_category, NULL::text) AS convert_category,
    sum(COALESCE(td.total_spent, 0::numeric)) AS total_spent,
    sum(COALESCE(tc.total_revenue, 0::numeric)) AS total_revenue
   FROM time_to_convert tc
     FULL JOIN transaction_data td ON tc.inbound_date = td.payment_date AND tc.branch = td.branch AND tc.source = td.source AND tc.sremarks = td.sremarks_id
  GROUP BY (COALESCE(tc.inbound_date, td.payment_date)), tc.leads_date, tc.mql_date, tc.mqlplus_date, tc.tourplus_date, tc.trialplus_date, tc.convert_date, (COALESCE(tc.branch, td.branch)), (COALESCE(tc.source, td.source)), (COALESCE(tc.sremarks, td.sremarks_id)), tc.medium, tc.mremarks, tc.contact_owner, tc.approach_by, tc.assigned_pic, tc.convert_by, tc.membership_name, tc.package_name, tc.convert_category, (COALESCE(tc.day, 0::double precision)), (COALESCE(tc.inbound, 0::bigint)), (COALESCE(tc.day_leads, 0::double precision)), (COALESCE(tc.leads, 0::bigint)), (COALESCE(tc.day_mql, 0::double precision)), (COALESCE(tc.mql, 0::bigint)), (COALESCE(tc.day_mqlplus, 0::double precision)), (COALESCE(tc.mqlplus, 0::bigint)), (COALESCE(tc.day_tourplus, 0::double precision)), (COALESCE(tc.tourplus, 0::bigint)), (COALESCE(tc.day_trialplus, 0::double precision)), (COALESCE(tc.trialplus, 0::bigint)), (COALESCE(tc.day_convert, 0::double precision)), (COALESCE(tc.convert, 0::bigint))
  ORDER BY (COALESCE(tc.inbound_date, td.payment_date));
```

{% 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/table-view/tcc-cac.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.