CREATE OR REPLACE TABLE
SNOWPLOW.DERIVED.TMP_USER_RESOURCE_TRANSACTION
  AS(
SELECT distinct t.EVENT_ID
    , t.DERIVED_TSTAMP
    , t.TRANSACTION_DATE
    , t.TRANSACTION_DT
    , t.SESSION_ID
    , t.SESSION_START_DT
    , t.USER_ID
    , t.IS_RECEIVED_RESOURCE
    , t.TRANSACTION_SOURCE
    , b.business_source
    , b.business_source_type
    , b.business_source_sub_type

    , t.RESOURCE_ID
    , t.RESOURCE_TYPE
    , r.resource_sub_type
    , r.booster_tier_group_name
    , t.RESOURCE_CNT
    , t.RESOURCE_ITEM_STATUS
    , t.RESOURCE_VERSION

    , t.MATCH_TYPE_ID
    , t.MATCH_MODE_TYPE
    , t.MATCH_ID
    , t.OFFER_INDEX
    , t.OFFER_ID
    , t.SUB_OFFER_ID
    , t.LO_EVENT_ID
    , e.lo_event_type
    , e.LO_EVENT_CONFIG_ID
    , b.event_id as event_type
    , t.LO_ENTRY_ID
    , t.TEAM_ID
    , t.TEAM_NAME
    , t.TRANSACTION_PARTICIPATE_ID
    , t.IS_REROLLED
    , IFNULL(t.IS_COIN_PAID, b.PURCHASED_COINS) as IS_COIN_PAID
    , IFNULL(t.IS_USD_PAID, b.purchase_usd) as IS_USD_PAID
    , t.prize_container
    , t.full_container
    , t.ARRAY_SIZE
    , t.prize_type
    , t.is_chest
    , t.is_sticker
    , t.is_piniata
    , t.prize_tier
    , t.is_guranteed_unique
    , t.piniata_sticker_type
    , t.piniata_sticker_tier
    , t.piniata_is_sticker_guranteed_unique


 ,t.uuid
,t.country
,t.language
,t.device_language
,t.test_name
,t.test_group_name
,t.fb_user_ID
,t.client_IP
,t.is_testing_user
,t.is_developer
,t.device_ID
,t.device_model
,t.device_os
,t.platform
,t.media_source
,t.app_version
,t.app_minor_version
,t.first_install_DT
,t.advertiser_ID
,t.appsflyer_id
,t.ad_name
,t.campaign_name
,t.subscription_tier
,t.total_IAP_amt
,t.current_arena_index
,t.trophies_cnt
,t.stars_cnt
,t.LT_matches_played_cnt
,t.LT_matches_won_cnt
,t.LT_purchases_amt
,t.event_source
,t.LTV_group
,t.ad_id
,t.adset
,t.adset_id
,t.campaign_id
,t.influencer_name
,t.user_name
, t.af_keywords
, t.site_id
, t.coin_price
,t.dollar_price
,t.video_price
,t.album_id
,t.sticker_number
from snowplow.derived.core_user_resource_transaction t
left join CANDIVORE.PROD.DIM_EVENT_CONFIG e
  on t.lo_event_id = e.lo_event_id
left join  snowplow.derived.business_transaction_sources b
  on t.transaction_source = b.transaction_source and e.lo_event_config_id = b.business_source
left join  (select * from
(select *, rank() over (partition by resource_name,resource_sub_type,resource_type order by db_create_date desc, resource_version
desc) as resource_rank
  from candivore.prod.DIM_RESOURCE_VERSION)
  where resource_rank = 1) r
  on t.resource_id = r.resource_name and t.resource_type = r.resource_type
where t.TRANSACTION_DT > (SELECT MAX_TRANSACTION_DT FROM
TMP_UM_MAX_INSERT_DATE))