prepare_tables.sql

---
--- Combine tables in preparation for CSV export.
---

-- Stage 1: contacts, accounts => full join
-- ----------------------------------------
-- We need to make a new table that consists of all contacts and all companies,
-- since not all companies have associated contacts and vice-versa.

create table all_contacts_and_companies
as
select CONTACTS.first_name,
       CONTACTS.last_name,
       CONTACTS.prefix,
       CONTACTS.street,
       CONTACTS.city,
       CONTACTS.postcode,
       CONTACTS.address,
       CONTACTS.phone,
       CONTACTS.phone_2,
       CONTACTS.email,
       CONTACTS.username,
       CONTACTS.email_2,
       CONTACTS.additional_contact_role,
       CONTACTS.linkedin_bio,
       CONTACTS.description,
       CONTACTS.contact_role,
       CONTACTS.original_source,
       CONTACTS.status,
       CONTACTS.buying_role,
       CONTACTS.active,
       CONTACTS.marketing_engaged,
       CONTACTS.external_id         as contact_eid,
       CONTACTS.company_id,
       CONTACTS.company             as contact_company,

       COMPANIES.name               as c_name,
       COMPANIES.cdomain,
       COMPANIES.street             as c_street,
       COMPANIES.city               as c_city,
       COMPANIES.postcode           as c_postcode,
       COMPANIES.address            as c_address,
       COMPANIES.phone              as c_phone,
       COMPANIES.phone_2            as c_phone_2,
       COMPANIES.email              as c_email,
       COMPANIES.email_2            as c_email_2,
       COMPANIES.website            as c_website,
       COMPANIES.current_crm        as c_current_crm,
       COMPANIES.description        as c_description,
       COMPANIES.about_us           as c_about_us,
       COMPANIES.employee_count     as c_employee_count,
       COMPANIES.revenue_band       as c_revenue_band,
       COMPANIES.current_tech_stack as c_current_tech_stack,
       COMPANIES.active             as c_active,
       COMPANIES.is_public          as c_is_public,
       COMPANIES.external_id        as company_eid
from defaultdb_public_contacts CONTACTS
         full join defaultdb_public_accounts COMPANIES
                   on CONTACTS.company_id = COMPANIES.external_id;

-- Stage 2: [contacts, accounts], deals => right join

create table contacts_with_matching_deals
as
select CONTACTS_COMPANIES.*,
       DEALS.name as dealname,
       DEALS.stage
from all_contacts_and_companies CONTACTS_COMPANIES
         right join defaultdb_public_deals DEALS
                    on CONTACTS_COMPANIES.contact_eid = DEALS.contact_id;

-- Stage 3: [[contacts, accounts], deals], deals => right join

create table contacts_companies_with_matching_deals
as
select CONTACTS_COMPANIES.*
from contacts_with_matching_deals CONTACTS_COMPANIES
         right join defaultdb_public_deals DEALS
                    on CONTACTS_COMPANIES.company_eid = DEALS.account_id and
                       CONTACTS_COMPANIES.contact_eid = DEALS.contact_id;