How to calculate conversion rate of names (strings) appearing in multiple rows with a column stating TRUE or FASLE for a conversion on each row?

user_19105904 0
Viewed 33

I am trying to write a query that will calculate the conversion rate associated with each name in a table such as the one below (assume the real table has thousands of different names)

Name Converted
abc TRUE
abc FALSE
xyz FALSE
dhk TRUE
dhk TRUE
dhk TRUE
dhk FALSE

Since abc has one true and one false the query should display a conversion rate of 50% (1 TRUE / 2 Total = 50%). For dhk the conversion rate would be 75% (3 TRUEs/ 4 Total), and for xyz it would be 0% since there are no TRUEs.

The final output of the query should look like the following:

Name Conversion Rate
abc 50%
xyz 0%
dhk 75%

OR if I could figure out how to get the output below that would also be enough for me to figure out the rest

Name Converted Conversion Rate
abc TRUE 50%
abc FALSE 50%
xyz FALSE 0%
dhk TRUE 75%
dhk TRUE 75%
dhk TRUE 75%
dhk FALSE 75%

Any help would be appreciated, thank you.

google-bigquery sql
2 Answers

Use below

select name, 
  avg(if(converted, 100, 0)) ConversionRate
from your_table
group by name

if applied to sample data in your question - output is

enter image description here

or, you can use below

select *, 
  avg(if(converted, 100, 0)) over(partition by name) ConversionRate
from your_table

with output

enter image description here

user_52219440

This is an intuitive solution, using the traditional conversion rate formula:

SELECT Name, 
  COUNTIF(Converted)/COUNT(1) ConversionRate
FROM conversion_table
GROUP BY Name
user_193078290
Related