-- uv、pv、留存率（按⽇）统计

-- pv 按日分组，统计 count behavior_type = 1
-- uv 按日统计，统计 count distinct user_id
-- pv/uv 
SELECT
    dates,
    COUNT(IF(behavior_type = 1,user_id,null)) AS 'pv',
    COUNT( DISTINCT user_id ) AS 'uv',
     COUNT(IF(behavior_type = 1,user_id,null))/COUNT( DISTINCT user_id ) AS 'pv/uv'
FROM
    tmp_table 
GROUP BY
    dates;
    
DROP view   user_remain;
-- 活跃用户留存率
CREATE VIEW user_remain AS SELECT
a.dates,
COUNT( DISTINCT a.user_id ) AS 'user_count',
COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 1,
            b.user_id,
        NULL 
        )) AS 'remain1',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 2,
            b.user_id,
        NULL 
        )) AS 'remain2',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 3,
            b.user_id,
        NULL 
        )) AS 'remain3',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 4,
            b.user_id,
        NULL 
        )) AS 'remain4',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 5,
            b.user_id,
        NULL 
        )) AS 'remain5',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 6,
            b.user_id,
        NULL 
        )) AS 'remain6',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 7,
            b.user_id,
        NULL 
        )) AS 'remain7',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 15,
            b.user_id,
        NULL 
        )) AS 'remain15',
    COUNT(
    DISTINCT
    IF
        (
            DATEDIFF( b.dates, a.dates )= 30,
            b.user_id,
        NULL 
        )) AS 'remain30' 
FROM
    (
    SELECT
        user_id,
        dates 
    FROM
        tmp_table 
    GROUP BY
        user_id,
        dates 
    ) a
    LEFT JOIN (
    SELECT
        user_id,
        dates 
    FROM
        tmp_table 
    GROUP BY
        user_id,
        dates 
    ) b ON a.user_id = b.user_id 
WHERE
    b.dates > a.dates 
GROUP BY
    a.dates;


SELECT 
dates,
user_count,
CONCAT(cast((remain1/user_count*100) as DECIMAL(10,2)),'%') as 'day1',
CONCAT(cast((remain2/user_count*100) as DECIMAL(10,2)),'%') as 'day2',
CONCAT(cast((remain3/user_count*100) as DECIMAL(10,2)),'%') as 'day3',
CONCAT(cast((remain4/user_count*100) as DECIMAL(10,2)),'%') as 'day4',
CONCAT(cast((remain5/user_count*100) as DECIMAL(10,2)),'%') as 'day5',
CONCAT(cast((remain6/user_count*100) as DECIMAL(10,2)),'%') as 'day6',
CONCAT(cast((remain7/user_count*100) as DECIMAL(10,2)),'%') as 'day7',
CONCAT(cast((remain15/user_count*100) as DECIMAL(10,2)),'%') as 'day15',
CONCAT(cast((remain30/user_count*100) as DECIMAL(10,2)),'%') as 'day30'
FROM 
user_remain;