SELECT
  A.*,
  B.lo_event_id,
  B.lo_event_min_trophies,
  B.lo_event_max_trophies,
  B.calendar_order,
  B.lo_visible_on_homescreen,
  B.lo_entry_tickets_required
FROM
  (
    SELECT
      DISTINCT match_end_date AS INTERVAL_DATE,
      arena_group,
      CASE WHEN trophies_cnt < 125 THEN '0-125' WHEN trophies_cnt < 1400 THEN '125-1400' WHEN trophies_cnt < 2000 THEN '1400-2000' WHEN trophies_cnt < 3800 THEN '2000-3800' WHEN trophies_cnt < 7000 THEN '3800-7000' WHEN trophies_cnt < 16000 THEN '7000-16000' WHEN trophies_cnt < 30000 THEN '16000-30000' WHEN trophies_cnt >= 30000 THEN '30000+' END AS trophy_group,
CASE
WHEN trophies_cnt < 125 THEN '0-125'
WHEN trophies_cnt < 1400 THEN '125-1400'
WHEN trophies_cnt < 2000 THEN '1400-2000'
WHEN trophies_cnt < 3800 THEN '2000-3800'
WHEN trophies_cnt < 4500 THEN '3800-4500'
WHEN trophies_cnt < 5500 THEN '4500-5500'
WHEN trophies_cnt < 7000 THEN '5500-7000'
WHEN trophies_cnt < 10000 THEN '7000-10000'
WHEN trophies_cnt < 13000 THEN '10000-13000'
WHEN trophies_cnt < 16000 THEN '13000-16000'
WHEN trophies_cnt < 20000 THEN '16000-20000'
WHEN trophies_cnt < 30000 THEN '20000-30000'
WHEN trophies_cnt >= 30000 THEN '30000+'
END AS trophy_group_2,
      ltv_group,
      NULL AS engagement_group,
      is_payer,
      NULL AS is_subscriber,
      NULL AS SUBSCRIPTION_TIER,
      NULL AS seniority_bin,
      pvp_mode,
      MATCH_TYPE_ID,
      CASE WHEN is_random_mixer = TRUE THEN 'Daily_Random_Mixer' ELSE match_type END AS MATCH_TYPE,
      MATCH_MODE,
      MODIFIER_1,
      MODIFIER_2,
      match_modifier_3,
      calendar_entry_id,
      concat(
        MATCH_SUB_TYPE,
        '-',
        MATCH_MODE,
        '+',
        MODIFIER_1,
        '-',
        MODIFIER_2,
        '-',
        match_modifier_3
      ) AS match_sub_type,
      RESOURCE_SUB_TYPE,
      BOOSTER_NAME,
      RIVAL_RESOURCE_SUB_TYPE,
      RUMBLE_SIZE,
      CASE WHEN PVP_MODE NOT LIKE 'PvP' THEN 'No Rival(Solo/Rumble)' ELSE RIVAL_BOOSTER_NAME END RIVAL_BOOSTER_NAME,
      rounds_cnt,
      is_private,
      is_rival_bot,
      turn_index,
      SUM(ROUNDS_CNT) AS ROUNDS,
      SUM(MATCH_SCORE) AS MATCH_SCORE,
      SUM(MATCH_DURATION_SEC) / 60 AS MATCH_DURATION_MINS,
      SUM(ABILITY_ACTIVATED_CNT) AS BOOSTER_ACTIVATIONS,
      SUM(ifnull(CS_RESYNCS, 0)) AS RESYNCS,
      COUNT(
        DISTINCT CASE WHEN CS_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt) END
      ) AS RESYNC_MATCHES,
      SUM(ifnull(ERROR_RESYNCS, 0)) AS ERROR_RESYNCS,
      COUNT(
        DISTINCT CASE WHEN ERROR_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt) END
      ) AS ERROR_RESYNC_MATCHES,
      SUM(
        CASE WHEN is_conceded = 1
        AND is_won = 0
        AND match_duration_sec = 0 THEN 1 END
      ) AS CONCEDED_MATCHES,
      SUM(
        CASE WHEN is_conceded = 1
        AND is_won = 0
        AND match_duration_sec BETWEEN 1
        AND 10 THEN 1 END
      ) AS CONCEDED_MATCHES_1_10,
      SUM(
        CASE WHEN is_conceded = 1
        AND is_won = 0
        AND match_duration_sec BETWEEN 10
        AND 60 THEN 1 END
      ) AS CONCEDED_MATCHES_10_60,
      SUM(
        CASE WHEN is_conceded = 1
        AND is_won = 0
        AND match_duration_sec > 60 THEN 1 END
      ) AS CONCEDED_MATCHES_AFTER_60sec,
      SUM(ifnull(rival_played_out_of_time, 0)) AS OOT,
      COUNT(
        DISTINCT CASE WHEN rival_played_out_of_time > 0 THEN concat(match_id, user_id, rematch_cnt) END
      ) AS OOT_MATCHES,
      COUNT(DISTINCT concat(match_id, user_id, rematch_cnt)) AS MATCHES,
      SUM(is_won) AS wins,
      AVG(MATCH_RANK) AS AVG_MATCH_RANK
    FROM
      CANDIVORE.PROD.F_USER_MATCH
    WHERE
      is_bot = FALSE
      AND MATCH_END_DATE > DATEADD(DAY, -61, GETDATE())
    GROUP BY
      1,
      2,
      3,
      4,
      5,
      6,
      7,
      8,
      9,
      10,
      11,
      12,
      13,
      14,
      15,
      16,
      17,
      18,
      19,
      20,
      21,
      22,
      23,
      24,
      25,
      26,
27,
28
  ) A
  LEFT JOIN (
    SELECT
      A.LO_ENTRY_ID,
      B.lo_event_id,
      A.interval_date,
      A.lo_entry_name,
      A.calendar_order,
      A.lo_visible_on_homescreen,
      CASE WHEN min0 IS NULL THEN min1 ELSE min0 END AS lo_event_min_trophies,
      CASE WHEN max0 IS NULL THEN max1 ELSE max0 END AS lo_event_max_trophies,
      lo_entry_tickets_required
    FROM
      (
        (
          SELECT
            LO_ENTRY_ID,
            DATE(derived_tstamp) interval_date,
            LO_ENTRY_NAME,
            calendar_order,
            lo_visible_on_homescreen,
            lo_entry_min_trophies min0,
            lo_entry_max_trophies max0,
            lo_entry_tickets_required
          FROM
            (SELECT * FROM (SELECT *,CASE WHEN lo_entry_ts IS NULL THEN lo_entry_update_ts ELSE lo_entry_ts END lo_entry_ts2,ROW_NUMBER() OVER (partition BY lo_entry_id ORDER BY lo_entry_ts2 DESC) rn FROM CANDIVORE.PROD.F_LIVEOPS_CALENDAR) WHERE rn = 1)
        ) A
        LEFT JOIN (
          SELECT
            DATE(derived_tstamp) AS interval_date,
            lo_event_id,
            lo_event_min_trophies AS min1,
            lo_event_max_trophies AS max1
          FROM
            CANDIVORE.PROD.DIM_EVENT_CONFIG
        ) B ON A.LO_ENTRY_NAME = B.LO_EVENT_ID
      )
  ) B ON A.calendar_entry_id = B.lo_entry_id