-- 也优化不了
select to_char(to_timestamp(event_time_min), 'YYYY-MM')               as reg_month,
       DATE_TRUNC('month', to_timestamp(event_time_min))              as reg_month_date,
       case when tg_play.tag is null then '未知' else tg_play.tag end as play_tag,
       uniq(mi.user_id)                                               as acc_count
from public.ods_newly_account acc
         inner join (select user_id, min(event_time) as event_time_min
                     from public.ods_newly_account acc
                              inner join (select *
                                          from public.gm_apply
                                          WHERE bind_game_type = 1 -- 传奇游戏
                     ) app
                                         on acc.appid = app.id
                     group by user_id) mi ---从玩家的众多游戏记录中选取最早的一条
                    on acc.user_id = mi.user_id and acc.event_time = mi.event_time_min
         inner join (SELECT id   as app_id,
                            name as app_name,
                            bind_game_id,
                            bind_game_type ---1 :传奇游戏 , 2:传世 , 3：传3
                     FROM public.gm_apply
                     WHERE bind_type = 1 -- 默认应用
) gm
                    on acc.appid = gm.app_id
         left outer join
     (select game_id, tag
      from public.game_tags
      where tag in
            ('神器', '复古', '沉默', '火龙', '合击', '迷失', '小极品', '大极品', '二合一', '铭文', '冰雪', '宠物',
             '战神', '暗黑', '元素', '忘忧', 'BUFF', '天罡')) tg_play
     on gm.bind_game_id = tg_play.game_id
group by 1, 2, 3 limit 10000