# Time to Convert

{% code title="time\_to\_convert" overflow="wrap" lineNumbers="true" %}

```plsql
 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,
            GREATEST(0::double precision, date_part('day'::text, combined.convert_date - combined."timestamp")) AS day,
            GREATEST(0::double precision, date_part('day'::text, combined.leads_date - combined."timestamp")) AS day_leads,
            GREATEST(0::double precision, date_part('day'::text, combined.mql_date - combined.leads_date)) AS day_mql,
            GREATEST(0::double precision, date_part('day'::text, combined."mqlPlus_date" - combined.mql_date)) AS day_mqlplus,
            GREATEST(0::double precision, date_part('day'::text, combined."tourPlus_date" - combined."mqlPlus_date")) AS day_tourplus,
            GREATEST(0::double precision, date_part('day'::text, combined."trialPlus_date" - combined."tourPlus_date")) AS day_trialplus,
            GREATEST(0::double precision, date_part('day'::text, combined.convert_date - combined."trialPlus_date")) 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,
            datawithday.day,
            count(*) FILTER (WHERE datawithday.inbound_date IS NOT NULL) AS inbound,
            datawithday.day_leads,
            count(*) FILTER (WHERE datawithday.leads_date IS NOT NULL) AS leads,
            datawithday.day_mql,
            count(*) FILTER (WHERE datawithday.mql_date IS NOT NULL) AS mql,
            datawithday.day_mqlplus,
            count(*) FILTER (WHERE datawithday.mqlplus_date IS NOT NULL) AS mqlplus,
            datawithday.day_tourplus,
            count(*) FILTER (WHERE datawithday.tourplus_date IS NOT NULL) AS tourplus,
            datawithday.day_trialplus,
            count(*) FILTER (WHERE datawithday.trialplus_date IS NOT NULL) AS trialplus,
            datawithday.day_convert,
            count(*) FILTER (WHERE datawithday.convert_date IS NOT NULL) AS 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, datawithday.day, datawithday.day_leads, datawithday.day_mql, datawithday.day_mqlplus, datawithday.day_tourplus, datawithday.day_trialplus, datawithday.day_convert
        )
 SELECT branch,
    source,
    sremarks,
    medium,
    mremarks,
    contact_owner,
    approach_by,
    assigned_pic,
    convert_by,
    membership_name,
    package_name,
    convert_category,
    inbound_date,
    leads_date,
    mql_date,
    mqlplus_date,
    tourplus_date,
    trialplus_date,
    convert_date,
    day,
    inbound,
    day_leads,
    leads,
    day_mql,
    mql,
    day_mqlplus,
    mqlplus,
    day_tourplus,
    tourplus,
    day_trialplus,
    trialplus,
    day_convert,
    convert,
    total_revenue
   FROM groupeddata
  ORDER BY inbound_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/time-to-convert.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.