游戏商业化运营-活动推送转化报表 Cracking the activity conversion report task

WITH A AS (
    SELECT
        ,t.uid
        ,level
        ,createtime
        ,endtime
        ,SUM(IF(login_logout_time >= createtime AND login_logout_time <= endtime, 1, 0) as n_login
        ,IF(SUM(IF(login_logout_time >= createtime AND login_logout_time <= endtime, 1, 0)) > 0, 1, 0) AS bv_loggedin
    FROM
        db.ods_game_activity_push_log t
    LEFT JOIN (
        SELECT 
            uid
            ,logintime AS login_logout_time
        FROM db.ods_user_login_log 
        WHERE dt BETWEEN '2023-10-10' AND '2023-10-17'
        UNION
        SELECT 
            uid
            ,logouttime AS login_logout_time
        FROM db.ods_user_login_log 
        WHERE dt BETWEEN '2023-10-10' AND '2023-10-17'
    ) tt ON t.uid = tt.uid 
    WHERE
        t.dt = '2023-10-10'
    GROUP BY
        t.uid
        ,level
        ,createtime
        ,endtime
),

INSERT OVERWRITE TABLE db.activity_push_uid_batch_logedin_order_result 
PARTITION (push_date = '2023-10-10')
SELECT
    A.*
    ,tt.createtime AS ordertime
    ,tt.price
    ,tt.goodid
FROM
    A
LEFT JOIN (
    SELECT
        uid
        ,createtime
        ,price
        ,goodid
    FROM
        db.ods_order_log tt
    WHERE
        dt BETWEEN '2023-10-10' AND '2023-10-17'
) tt ON A.uid = tt.uid AND tt.createtime >= A.createtime AND tt.createtime <= A.endtime
;

-- SQL 1
CREATE TABLE db.activity_push_batch_uid AS
SELECT
    uid
    ,level
    ,createtime
    ,endtime
    ,dense_rank() over(order by hour(createtime), minute(createtime)) as batch
FROM
    db.ods_game_activity_push_log
WHERE
    dt = '2023-10-10'
order by
    createtime asc;

-- SQL 2
CREATE TABLE if not exists db.activity_push_batch_createtime AS
SELECT
    batch
    ,createtime
    ,endtime
FROM
    (
        SELECT
            createtime
            ,endtime
            ,batch
            ,row_number() over (partition by hour(createtime), minute(createtime) order by createtime asc) as rank
        FROM
            db.activity_push_batch_uid T
            ) t
WHERE
    t.rank = 1
;

-- SQL 3
CREATE TABLE db.bv_logint_logout uid AS
WITH A as (
SELECT 
    uid
    ,logintime as login_logout_time
FROM
    db.ods_user_login_log
WHERE
    dt >= '2023-10-10' and dt < '2023-10-13'
UNION
SELECT 
    uid
    ,logouttime as login_logout_time
FROM
    db.ods_user_login_log
WHERE
    dt >= '2023-10-10' and dt < '2023-10-13'
)

SELECT
    DISTINCT 
    T.batch
    ,A.uid
FROM
    A
INNER JOIN
    db.activity_push_batch_createtime T
on
    A.login_logout_time >= T.createtime
    and A.login_logout_time <= T.endtime
;

-- SQL 4
CREATE TABLE guozizhun.game_god_grant_push_batch_order_20231020 AS
WITH A AS (
    SELECT
        uid
        ,createtime
        ,price
        ,goodid
    FROM
        db.ods_order_log
    WHERE
        dt >= '2023-10-10' and dt < '2023-10-13'
)
,
B AS (
    SELECT
        A.uid
        ,T.batch
        ,A.createtime
        ,A.price
        ,A.goodid
    FROM
        A
    INNER JOIN
        db.activity_push_batch_createtime T
    on
        A.createtime >= T.createtime
        and A.createtime <= T.endtime
)

SELECT
    A.*
    ,B.batch
    ,B.uid
    ,B.createtime as ordertime
    ,B.price
    ,B.goodid
    ,DATEDIFF(DATE(B.createtime), DATE(A.createtime)) as days_from_createtime
FROM
    guozizhun.bv_logint_logout A
LEFT JOIN
    B
on
    A.uid = B.uid
    and A.batch = B.batch
;