SQL , retention rate

Question

SQL , retention rate

Viki Ученик (87), на голосовании 1 год назад

Как можно подсчитать не количество а долю пользователей и
перевести делимое в тип numeric для корректного деления.
я делаю но все не получается. Подскажите, где моя ошибка

WITH p AS (SELECT user_id, COUNT(user_id) OVER() AS user_count
FROM tools_shop.users u
WHERE CAST(created_at AS date) BETWEEN '2020-01-01' AND '2020-01-31'
GROUP BY user_id),

m AS (SELECT *
FROM tools_ shop.events e
WHERE platform= 'android' AND CAST(event_time AS date) BETWEEN '2020-01-01' AND '2020-03-31')

SELECT (COUNT(m.user_id::numeric/user_count::numeric)*100,2) AS R_R, user_count, CAST(DATE_TRUNC('month', event_time) AS timestamp) AS month
FROM p
JOIN m ON m.user_id = p.user_id
GROUP BY month, user_count
ORDER BY month, user_count

Answer 1

 WITH p AS ( 
    SELECT  
        user_id,  
        COUNT(user_id) OVER() AS user_count  
    FROM  
        tools_shop.users u  
    WHERE  
        CAST(created_at AS date) BETWEEN '2020-01-01' AND '2020-01-31'  
    GROUP BY  
        user_id 
),  
 
m AS ( 
    SELECT  
        *  
    FROM  
        tools_shop.events  e  
    WHERE  
        platform = 'android' AND CAST(event_time AS date) BETWEEN '2020-01-01' AND '2020-03-31' 
)  
 
SELECT  
    ROUND(COUNT(m.user_id)::numeric / p.user_count::numeric * 100, 2) AS R_R,  
    p.user_count,  
    DATE_TRUNC('month', event_time) AS month  
FROM  
    p  
JOIN  
    m ON m.user_id = p.user_id  
GROUP BY  
    month,  
    p.user_count  
ORDER BY  
    month,  
    p.user_count;