TAC

view name = 'tac'

[NEW] Query

 SELECT date,
    month,
    week,
    branch,
    contact_owner,
    assigned_pic,
    last_created_by,
    convert_by,
    source,
    sremarks,
    medium,
    mremarks,
    sum(inbound) AS inbound,
    sum(leads) AS leads,
    sum(mql) AS mql,
    sum(appt_mql) AS appt_mql,
    sum(appt) AS appt,
    sum(mqlplus) AS mqlplus,
    sum(appt_mqlplus) AS appt_mqlplus,
    sum(tourplus) AS tourplus,
    sum(appt_tourplus) AS appt_tourplus,
    sum(trialplus) AS trialplus,
    sum(appt_trialplus) AS appt_trialplus,
    sum(convert) AS convert
   FROM ( SELECT tac_inbound.date,
            tac_inbound.month,
            tac_inbound.week,
            tac_inbound.branch,
            tac_inbound.contact_owner,
            tac_inbound.assigned_pic,
            NULL::text AS last_created_by,
            NULL::text AS convert_by,
            tac_inbound.source,
            tac_inbound.sremarks,
            tac_inbound.medium,
            tac_inbound.mremarks,
            tac_inbound.inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM tac_inbound
        UNION ALL
         SELECT tac_leads.date,
            tac_leads.month,
            tac_leads.week,
            tac_leads.branch,
            tac_leads.contact_owner,
            tac_leads.assigned_pic,
            NULL::text AS last_created_by,
            NULL::text AS convert_by,
            tac_leads.source,
            tac_leads.sremarks,
            tac_leads.medium,
            tac_leads.mremarks,
            0 AS inbound,
            tac_leads.leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM tac_leads
        UNION ALL
         SELECT tac_mql.date,
            tac_mql.month,
            tac_mql.week,
            tac_mql.branch,
            tac_mql.contact_owner,
            tac_mql.assigned_pic,
            tac_mql.last_created_by,
            NULL::text AS convert_by,
            tac_mql.source,
            tac_mql.sremarks,
            tac_mql.medium,
            tac_mql.mremarks,
            0 AS inbound,
            0 AS leads,
            tac_mql.mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM tac_mql
        UNION ALL
         SELECT tac_appt.date,
            tac_appt.month,
            tac_appt.week,
            tac_appt.branch,
            tac_appt.contact_owner,
            tac_appt.assigned_pic,
            tac_appt.created_by AS last_created_by,
            NULL::text AS convert_by,
            NULL::text AS source,
            NULL::text AS sremarks,
            NULL::text AS medium,
            NULL::text AS mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            tac_appt.total_mql AS appt_mql,
            tac_appt.total_appt AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM tac_appt
        UNION ALL
         SELECT "tac_mqlPlus".date,
            "tac_mqlPlus".month,
            "tac_mqlPlus".week,
            "tac_mqlPlus".branch,
            "tac_mqlPlus".contact_owner,
            "tac_mqlPlus".assigned_pic,
            "tac_mqlPlus".last_created_by,
            NULL::text AS convert_by,
            "tac_mqlPlus".source,
            "tac_mqlPlus".sremarks,
            "tac_mqlPlus".medium,
            "tac_mqlPlus".mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            "tac_mqlPlus".mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM "tac_mqlPlus"
        UNION ALL
         SELECT tac_appt_mqlplus.date,
            tac_appt_mqlplus.month,
            tac_appt_mqlplus.week,
            tac_appt_mqlplus.branch,
            tac_appt_mqlplus.contact_owner,
            tac_appt_mqlplus.assigned_pic,
            tac_appt_mqlplus.created_by AS last_created_by,
            NULL::text AS convert_by,
            NULL::text AS source,
            NULL::text AS sremarks,
            NULL::text AS medium,
            NULL::text AS mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            tac_appt_mqlplus.appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM tac_appt_mqlplus
        UNION ALL
         SELECT "tac_tourPlus".date,
            "tac_tourPlus".month,
            "tac_tourPlus".week,
            "tac_tourPlus".branch,
            "tac_tourPlus".contact_owner,
            "tac_tourPlus".assigned_pic,
            "tac_tourPlus".last_created_by,
            NULL::text AS convert_by,
            "tac_tourPlus".source,
            "tac_tourPlus".sremarks,
            "tac_tourPlus".medium,
            "tac_tourPlus".mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            "tac_tourPlus".tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM "tac_tourPlus"
        UNION ALL
         SELECT tac_appt_tourplus.date,
            tac_appt_tourplus.month,
            tac_appt_tourplus.week,
            tac_appt_tourplus.branch,
            tac_appt_tourplus.contact_owner,
            tac_appt_tourplus.assigned_pic,
            tac_appt_tourplus.created_by AS last_created_by,
            NULL::text AS convert_by,
            NULL::text AS source,
            NULL::text AS sremarks,
            NULL::text AS medium,
            NULL::text AS mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            tac_appt_tourplus.appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM tac_appt_tourplus
        UNION ALL
         SELECT "tac_trialPlus".date,
            "tac_trialPlus".month,
            "tac_trialPlus".week,
            "tac_trialPlus".branch,
            "tac_trialPlus".contact_owner,
            "tac_trialPlus".assigned_pic,
            "tac_trialPlus".last_created_by,
            NULL::text AS convert_by,
            "tac_trialPlus".source,
            "tac_trialPlus".sremarks,
            "tac_trialPlus".medium,
            "tac_trialPlus".mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            "tac_trialPlus".trialplus,
            0 AS appt_trialplus,
            0 AS convert
           FROM "tac_trialPlus"
        UNION ALL
         SELECT tac_appt_trialplus.date,
            tac_appt_trialplus.month,
            tac_appt_trialplus.week,
            tac_appt_trialplus.branch,
            tac_appt_trialplus.contact_owner,
            tac_appt_trialplus.assigned_pic,
            tac_appt_trialplus.created_by AS last_created_by,
            NULL::text AS convert_by,
            NULL::text AS source,
            NULL::text AS sremarks,
            NULL::text AS medium,
            NULL::text AS mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            tac_appt_trialplus.appt_trialplus,
            0 AS convert
           FROM tac_appt_trialplus
        UNION ALL
         SELECT tac_convert.date,
            tac_convert.month,
            tac_convert.week,
            tac_convert.branch,
            tac_convert.contact_owner,
            tac_convert.assigned_pic,
            tac_convert.last_created_by,
            tac_convert.convert_by,
            tac_convert.source,
            tac_convert.sremarks,
            tac_convert.medium,
            tac_convert.mremarks,
            0 AS inbound,
            0 AS leads,
            0 AS mql,
            0 AS appt_mql,
            0 AS appt,
            0 AS mqlplus,
            0 AS appt_mqlplus,
            0 AS tourplus,
            0 AS appt_tourplus,
            0 AS trialplus,
            0 AS appt_trialplus,
            tac_convert.convert
           FROM tac_convert) combined_data
  WHERE date IS NOT NULL
  GROUP BY date, month, week, branch, contact_owner, assigned_pic, last_created_by, convert_by, source, sremarks, medium, mremarks
  ORDER BY date, branch;

Penjelasan Inti Query

1. Tujuan Query

Query ini bertujuan untuk menghitung jumlah metrik tertentu (seperti inbound, leads, mql, convert, dll.) berdasarkan kombinasi dimensi waktu (date, month, week), lokasi (branch), dan atribut lainnya seperti contact_owner, assigned_pic, source, dan sebagainya.

---

2. Struktur Utama Query

Query memiliki dua bagian utama:

• Bagian "UNION ALL" (Subquery): Menggabungkan data dari berbagai tabel sumber (seperti tac_inbound, tac_leads, tac_mql, dll.) menjadi satu tabel gabungan bernama combined_data. Setiap tabel berkontribusi pada metrik tertentu, sementara metrik lainnya diisi dengan nilai nol (0).

• Bagian Agregasi (GROUP BY): Setelah data digabung, query mengelompokkan data berdasarkan dimensi seperti date, month, week, branch, dll., lalu menghitung total setiap metrik menggunakan fungsi SUM().

---

3. Penjelasan Bagian-Bagian Penting

• Bagian "UNION ALL":

  • Setiap tabel menyumbang metrik tertentu. Contoh:

    • tac_inbound menyumbang nilai inbound.

    • tac_leads menyumbang nilai leads.

    • Dan seterusnya.

  • Kolom yang tidak relevan untuk tabel tersebut diisi dengan nilai default seperti 0 (untuk angka) atau NULL (untuk teks).

• Bagian GROUP BY:

  • Data dikelompokkan berdasarkan dimensi seperti:

    • Waktu: date, month, week.

    • Lokasi: branch.

    • Atribut: contact_owner, assigned_pic, dll.

  • Untuk setiap kelompok, metrik seperti inbound, leads, mql, dll., dijumlahkan menggunakan SUM().

• Bagian WHERE date IS NOT NULL:

  • Memastikan hanya data yang memiliki nilai tanggal (date) yang diproses.

• Bagian ORDER BY date, branch:

  • Mengurutkan hasil akhir berdasarkan date (naik) dan branch (naik).

---

4. Hasil Akhir

Hasil query adalah tabel agregasi yang menunjukkan total metrik (inbound, leads, mql, convert, dll.) untuk setiap kombinasi:

• Waktu (tanggal, bulan, minggu).

• Lokasi (cabang/branch).

• Atribut lainnya (seperti contact_owner, assigned_pic, dll.).

Data diurutkan berdasarkan tanggal dan cabang. Output ini cocok untuk digunakan dalam analisis atau laporan.