user_info.login_countの修正 #4596

ゲソてんのユーザーのみを対象にuser_info.login_countを正しいものにするように

スタートダッシュログインボーナスで表示されることを踏まえ、修正後のlogin_countが7より上のユーザーのみを対象
周年ログインボーナスなどでヤマダのlogin_countを既存ユーザーは確認している可能性があるため、ゲソてんのみを対象

UPDATE jrye.user_info ui
INNER JOIN (
    SELECT
        t.user_no,
        SUM(t.cnt) AS total_cnt
    FROM (
        /* DB1 */
        SELECT
            dl.user_no,
            COUNT(*) AS cnt
        FROM jrye.dau_login dl
        INNER JOIN jrye.user_setting us
            ON us.user_no = dl.user_no
        WHERE
            us.tutorial_fin_time > 0
            AND dl.date >= FROM_UNIXTIME(us.tutorial_fin_time, '%Y-%m-%d')
            AND EXISTS (
                SELECT 1
                FROM jrye.user_platform up
                WHERE up.user_no = dl.user_no
                  AND up.pid = 'gesoten'
            )
        GROUP BY dl.user_no

        UNION ALL

        /* DB2 */
        SELECT
            dl.user_no,
            COUNT(*) AS cnt
        FROM jrye_log.dau_login dl
        INNER JOIN jrye.user_setting us
            ON us.user_no = dl.user_no
        WHERE
            us.tutorial_fin_time > 0
            AND dl.date >= FROM_UNIXTIME(us.tutorial_fin_time, '%Y-%m-%d')
            AND EXISTS (
                SELECT 1
                FROM jrye.user_platform up
                WHERE up.user_no = dl.user_no
                  AND up.pid = 'gesoten'
            )
        GROUP BY dl.user_no
    ) t
    GROUP BY t.user_no
    HAVING SUM(t.cnt) > 7
) x
    ON ui.user_no = x.user_no
SET ui.login_count = x.total_cnt
WHERE COALESCE(ui.login_count, 0) <> COALESCE(x.total_cnt, 0);