SQL Calculating the subscription rate based on entries-CodePudding

I am given a list of data shown as the following:

user_id	created_at	sign_up_at	platform
1	2021-01-01 00:01:01	NULL	ios
2	2021-01-10 07:13:42	2021-01-11 08:00:00	web
3	2021-02-01 12:11:44	2021-02-01 13:11:44	android
4	2021-02-28 04:32:12	2021-02-28 05:32:12	ios
5	2021-03-22 01:12:11	2021-03-22 02:12:11	android

(name of dataset) users

I'm supposed to extract the number of subscribers and the subscription rate. I have managed to extract the number of subscribers but I am confused on how to extract the subscription rate, since it is tracked by either NULL or a date. I have tried the following code:

SELECT sign_up_at  COUNT(*) * 100.0/sum(count(*))Over() as subscription_rate 
FROM users

However, I know the code is incorrect because I am not identifying whether or not each user is NULL or not. How could I fix this?

CodePudding user response：

Try with:

SELECT 
    (
        SELECT 
            COUNT(*)
        FROM 
            users 
        WHERE 
            sign_up_at IS NOT NULL
    ) / COUNT(*) as subscription_rate,
FROM 
    users

CodePudding user response：

The count function counts only the not null values, so COUNT(sign_up_at) will count the number of users where 'sign_up_at' is not null. COUNT(*) will count all the rows in the table.

SELECT COUNT(sign_up_at) / COUNT(*) * 100.0 AS subscription_rate 
FROM users

See a demo.