I am trying to show a proportion of customers that signed up because he/she was being referred by other customers and customers with no referral. So far I only able to show it as numerical but I wanted to show it in percentage. Null is when the customer signs up without being referred.
The original data as follows:
x
CustomerID ReferralID
1000004 1000003
1000015 1000010
1000007 1000004
1000011 Null
1000026 1000004
The query that I have and return data as follows:
select customerID, COUNT(*) as proportion
from company123.customertable
group by (customerID)
order by customerID asc;
CustomerID proportion
1000004 1
1000015 1
1000007 1
1000011 1
1000026 1
Expected result
CustomerID referred non-referred
1000004 1 0
1000015 1 0
1000007 1 0
1000011 0 1
1000026 1 0
Any suggestion to show it as a percentage? Thank you in advance
Advertisement
Answer
Use aggregate function AVG()
:
select avg(ReferralID is not null) referred,
avg(ReferralID is null) non_referred
from customertable
See the demo.
Results:
> referred | non_referred
> -------: | -----------:
> 0.8000 | 0.2000