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.
CodePudding user response：
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
or, you can use below
select *,
avg(if(converted, 100, 0)) over(partition by name) ConversionRate
from your_table
with output
CodePudding user response：
This is an intuitive solution, using the traditional conversion rate formula:
SELECT Name,
COUNTIF(Converted)/COUNT(1) ConversionRate
FROM conversion_table
GROUP BY Name