Untitled

mail@pastecode.io avatar
unknown
plain_text
17 days ago
490 B
2
Indexable
Never
SELECT 
  e.EmployeeID, 
  e.PostalCode, 
  AVG(num_customers) AS avg_customer_count
FROM (
  SELECT 
    t.EmployeeID, 
    COUNT(DISTINCT t.customer_id) AS num_customers
  FROM 
    `project_id.dataset_name.transactions` t
  INNER JOIN 
    `project_id.dataset_name.employees` e ON t.EmployeeID = e.EmployeeID
  WHERE 
    e.PostalCode IS NOT NULL
  GROUP BY 
    t.EmployeeID, 
    e.PostalCode
) AS EmployeeCustomerCounts
GROUP BY 
  e.EmployeeID, 
  e.PostalCode;
Leave a Comment