CODEWITHSUNDEEP
postgresql
AGuyHasNoName
AGuyHasNoName

Reputation: 109

Wrong Output in Postgres

I am joining 3 tables to get the retention rate. Here is my query:

select first_visit.first_month as first_month, 
       new_users.new_users as new_users, 
       count(distinct visit_tracker.customer__id) as retained, 
       cast(count(distinct visit_tracker.customer__id) / new_users.new_users as float) as retention_percent 
from first_visit
left join visit_tracker
on visit_tracker.customer__id=first_visit.customer__id 
left join new_users 
on new_users.first_month=first_visit.first_month 
group by 1,2;

I get the following output: 

first_month new_users retained retention_percent
0           93        34       0
1           119       42       0
2           188       102      0
3           223       71       0

and so on

What I want is this:

first_month new_users retained retention_percent
    0           93        34       0.37
    1           119       42       0.35
    2           188       102      0.54
    3           223       71       0.32

I am not sure why it's not producing the results I want. Any inputs?

Upvotes: 0

Views: 26

Answers (1)

Daniel Hill
Daniel Hill

Reputation: 900

This looks like a classic case of an integer division problem.

In this case count(distinct visit_tracker.customer__id) will return an integer which is then divided by a float. It looks like the float is cast into an integer and the result of the division is therefore an integer. Because the expected answer is less than one, it truncates to zero. The as float part of your query will not help as this happens after the truncation has already occured.

Try making sure both the numerator and the denominator are floats before performing the division or multiply by 100 beforehand as this stackoverflow answer suggests.

Upvotes: 1

Related Questions