Can I divide two alias columns in BigQuery?

Question

I'm trying to get the percentage of times something occurs (a task requires an outbound call)

So I made a count distinct of the occurrence "CALLED THE CUSTOMER" and aliased it as outbound_calls. And counted all the tasks alias "total_tasks"

These two steps work and I get this

Now I'm trying to divide outbound_calls by total_taks to get the percentage but get an error saying that the name oubound_calls is not recognized

This is the query I'm writing

SELECT
COUNT(DISTINCT IF(Out_CONTACT_TYPE = 'CALLED THE CUSTOMER', booking_id, NULL )) AS outbound_calls,
COUNT(task_id) AS total_tasks,
DIV(outbound_calls, total_tasks)
FROM `cs-reporting...`

Any ideas?

Happy Friday :)

Answer 1

I hope this can help.

You can use a subquery in this case :

SELECT 
   outbound_calls,
   total_tasks,
   DIV(outbound_calls, total_tasks)
FROM
(
  SELECT
   COUNT(DISTINCT IF(Out_CONTACT_TYPE = 'CALLED THE CUSTOMER', booking_id, NULL )) AS outbound_calls,
   COUNT(task_id) AS total_tasks
  FROM `cs-reporting...`
)

You can also use a WITH bloc :

WITH reporting AS
(
    SELECT
     COUNT(DISTINCT IF(Out_CONTACT_TYPE = 'CALLED THE CUSTOMER', booking_id, NULL )) AS outbound_calls,
     COUNT(task_id) AS total_tasks
    FROM `cs-reporting...`
)

SELECT 
   outbound_calls,
   total_tasks,
   DIV(outbound_calls, total_tasks)
FROM reporting;

For divisions with BigQuery, you can use SAFE_DIVIDE with ROUND functions (example with 2 digits) :

SELECT 
  outbound_calls,
  total_tasks,
  ROUND(SAFE_DIVIDE(outbound_calls, total_tasks), 2) as divres
FROM
(
SELECT 
   5 as outbound_calls,
   3 as total_tasks
);

According to the BigQuery documentation the aliases are visibles in the from not in the select bloc directly.