计算成功转化之前联系的平均客户数量

Question

I 三个表：

表名称：客户

Column Name:        Data Type:              Description:
id                   integer        Unique id for customer
company_id         integer(FK to company.id) Unique id for a company that the customer works for
contact_name           varchar           Full name of customer
contact_email          varchar           Email of customer
create_dt              date          Date when customer added to database

表名称：公司

Column Name:          Data Type:              Description:
id                    integer        Unique id for company
company_name          varchar             Name of company
create_dt             date     Date when company added to database
converted             boolean        if customer was converted
converted_dt          date       Date company purchased the product Value is null if the company did not

表名称：已联系(操作/联系客户的方式(

Column Name:          Data Type:              Description:
customer_id       integer(FK to Customer.id)    Unique id for contact
rep_name          varchar      Name of the rep who made the contact with the customer
rep_email         varchar      Email of rep who made the contact
contact_channel   varchar      Channel used to contact the customer(email,phone, in-person...)
contact_dt         date        Date of contact

我正在尝试编写一个查询，以显示在公司级别成功转换为客户之前联系的客户的平均数量。我能够在客户成功转换之前获取计数数，但不确定如何获取最终转换实例的平均计数和因子。

COUNT(CASE WHEN converted = FALSE AND converted_dt is NULL THEN 1 ELSE 0 END) as contacts_made_before_conversion,
CASE WHEN Company.converted_dt is NOT null THEN count(contact_dt) over(partition by company_name order by contact_dt DESC END) as contacted_counts
FROM Contacted
LEFT JOIN 
  (SELECT Customer.id as customer_id , company_name, 
   Company.converted, 
   FROM Customer LEFT JOIN Company
   ON Company.id = Customer.company_id) cc
ON cc.customer_id = Contacted.customer_id
GROUP BY company_name, customer_id

Answer 1

这

基本上是一个带有聚合left join：

select c.company_id,
       count(distinct c.id) as num_customers,
       count(co.id) as num_contacts,
       count(co.id) * 1.0 / count(distinct c.id) as num_per_customer
from customer c left join
     contacted co
     on co.customer_id = c.id and
        co.converted_dt < c.created_dt
group by c.company_id