Calculating ratio gives only 0 [SQL]-CodePudding

I am trying to calculate the ratio of returning customers vs. total customers

Sample table

customer_id   is_returning_customer 
934           1
294           0
042           0
194           1
839           1

Using the following code below

select count(distinct customer_id) as n_customers, 
  count(
    distinct (
      CASE
        WHEN is_returning_customer = 1 THEN customer_id
      END)) as n_returning_customers
from table

I get the following output:

n_customers  n_returning_customers
5            3

But when using this code to calculate the ratio of returning customers - I get as a result 0

select
  cast(
    count(
      distinct (
        CASE
          WHEN is_returning_customer = 1 THEN customer_id
        END
      )
    ) / count(distinct customer_id) as DECIMAL(9, 5)
  ) as returning_customer_ratio
from table

Result

returning_customer_ratio
0

CodePudding user response：

You can use the avg aggregate function:

select avg(is_returning_customer) as returning_customer_ratio from
(select customer_id, max(is_returning_customer) as is_returning_customer
from table_name
group by customer_id) t;

CodePudding user response：

I suppose it is doing integer division. An alternate method to calculate the average would be:

select avg(case when is_returning_customer = 1 then 1.0 else 0.0 end)
from t

Or, if your data contains multiple rows per customer:

select avg(irc)
from (
    select cast(max(is_returning_customer) as decimal(10, 2)) as irc
    from t
    group by customer_id
) as x