CREATE OR REPLACE PROCEDURE CANDIVORE.SEMANTIC_LAYER.SP_T_EARN_SPENT_LIGHT()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS '

  // Insert segmented data into CANDIVORE.SEMANTIC_LAYER.T_EARN_SPENT table
  var my_sql_command = `
CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_EARN_SPENT_LIGHT AS
WITH DATA AS (SELECT *
, CASE
    WHEN is_usd_paid = TRUE THEN ''Paid''
    WHEN is_coin_paid = TRUE THEN ''Coin Paid''
    ELSE ''Unpayed''
  END AS transaction_type
, CASE
    WHEN transaction_source LIKE ''admin%'' OR transaction_source LIKE ''auto%''  OR transaction_source LIKE ''promocode'' OR transaction_source LIKE ''promote'' THEN ''admin_dashboard''
    WHEN transaction_source = ''badge'' OR transaction_source = ''best_team'' OR transaction_source LIKE ''version_upgrade'' OR transaction_source LIKE ''convert_to%'' OR transaction_source LIKE ''feature_unlock%'' OR
    transaction_source = ''fix'' OR transaction_source = ''rate_us'' OR transaction_source LIKE ''update_%''  THEN ''others''
    WHEN transaction_source = ''booster_refill'' THEN ''booster_refill''
    WHEN transaction_source = ''helper_select_popup_chest'' THEN ''booster_select''
    WHEN transaction_source = ''booster_select_popup'' THEN ''booster_select_popup''
    WHEN transaction_source = ''booster_select'' THEN ''booster_select_bargain''
    WHEN transaction_source = ''coin_packs'' THEN ''coin_packs''
    WHEN transaction_source = ''create_team'' THEN ''create_team''
    WHEN transaction_source = ''dev_master'' THEN ''dev_master''
    WHEN transaction_source = ''disconnection_refund'' THEN ''disconnection_refund''
    WHEN transaction_source = ''team_request'' THEN ''team_request''
    WHEN transaction_source LIKE ''team_%'' THEN ''donation''
    WHEN transaction_source LIKE ''expired_claimable_rewards%'' THEN ''expired_claimable_rewards''
    WHEN transaction_source LIKE ''fb_%'' OR transaction_source LIKE ''friend_joined'' THEN ''facebook''
    WHEN transaction_source LIKE ''free_gift%'' THEN ''gift''
    WHEN transaction_source = ''home_screen_offer'' THEN ''home_screen_offer''
    WHEN transaction_source = ''join_team'' THEN ''join_team''
    WHEN transaction_source LIKE ''live_event%'' THEN ''live_event''
    WHEN transaction_source LIKE ''master%'' THEN ''master_league''
    WHEN transaction_source = ''match_end'' THEN ''match_end''
    WHEN transaction_source LIKE ''no_activity%'' THEN ''no_activity''
    WHEN transaction_source LIKE ''%progression%'' THEN ''progression_triggered''
    WHEN transaction_source LIKE ''rewarded_video%'' THEN ''rewarded_video''
    WHEN transaction_source = ''season_end'' THEN ''season_end''
    WHEN transaction_source LIKE ''special_offer%'' THEN ''special_offer''
    WHEN transaction_source = ''special_sale'' THEN ''special_sale''
    WHEN transaction_source = ''spin_deal'' THEN ''spin_deal''
    WHEN transaction_source LIKE ''perk_select%'' THEN ''perk_select''
    WHEN transaction_source = ''spin'' OR transaction_source = ''spin_chest'' THEN ''spins''
    WHEN transaction_source LIKE ''sticker_album%'' OR transaction_source = ''sticker_token'' OR transaction_source = ''stickers_trading'' THEN ''sticker_album''
    WHEN transaction_source LIKE ''store_special_offer%'' THEN ''store_special_offer''
    WHEN transaction_source = ''daily_deals'' AND is_coin_paid = TRUE THEN ''store_bargain''
    WHEN transaction_source = ''daily_deals'' AND is_usd_paid = TRUE THEN ''store_payed''
    WHEN transaction_source = ''daily_deals'' AND is_coin_paid = FALSE AND is_usd_paid = FALSE THEN ''store_free''
    WHEN transaction_source LIKE ''subscription%'' THEN ''subscription''
    WHEN transaction_source = ''super_spin'' THEN ''super_spin''
    WHEN transaction_source = ''super_spin_deal'' THEN ''super_spin_deal''
    WHEN transaction_source = ''booster_help'' OR transaction_source = ''team_request'' THEN ''team_request''
    WHEN transaction_source = ''tickets_pack'' THEN ''tickets_pack''
    WHEN transaction_source LIKE ''tournament%'' THEN ''tournament''
    WHEN transaction_source = ''unknown'' THEN ''unknown''
END transaction_source_lvl_0

, CASE
    WHEN transaction_source = ''booster_select'' OR (transaction_source = ''daily_deals'' AND is_coin_paid = TRUE) OR transaction_source = ''helper_select_popup_chest'' OR transaction_source LIKE ''perk_select%'' OR transaction_source LIKE ''special_offer%'' OR transaction_source = ''spin''
    OR transaction_source = ''spin_chest'' OR transaction_source = ''super_spin'' OR transaction_source LIKE ''%donation%'' OR transaction_source = ''tickets_pack'' THEN TRUE
    ELSE FALSE
  END AS is_bargain

, CASE
    WHEN lo_event_id LIKE ''%mutation%'' THEN ''solo_mutation''
    WHEN lo_event_id LIKE ''Solo%'' OR lo_event_id LIKE ''solo%'' THEN ''solo''
    WHEN lo_event_type = ''Rumble'' THEN ''rumble''
    WHEN lo_event_type = ''AdventureMode'' THEN ''adventure''
    WHEN lo_event_type LIKE ''Leaderboard%'' THEN ''leaderboard''
    WHEN lo_event_type LIKE ''BoostersClash'' THEN ''boosters_clash''
    WHEN match_type_id = 4 OR transaction_source LIKE ''tournament%'' THEN ''tournament''
    WHEN match_type_id = 5 THEN ''daily''
    WHEN match_type_id = 2 THEN ''classic''
END AS match_type

FROM CANDIVORE.PROD.f_user_resource_transaction

WHERE DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE()))
--------------------
--end_with_statement
--------------------
SELECT A.*, B.resource_coin_value, A.resource_cnt*B.resource_coin_value AS total_coins_value FROM
((SELECT
DATE(derived_tstamp) interval_date
,CASE
    WHEN trophies_cnt < 800 THEN ''800''
    WHEN CURRENT_ARENA_INDEX<=13 THEN ''Studios''
    WHEN CURRENT_ARENA_INDEX<=22 THEN ''Master_League''
    WHEN CURRENT_ARENA_INDEX>=23 THEN ''Legends_League''
 END league
, 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 < 30000 THEN ''16000-30000''
WHEN trophies_cnt >= 30000 THEN ''30000+''
END AS trophy_group_2
, CASE
    WHEN lt_purchases_amt > 0 THEN ''paying_users''
    WHEN lt_purchases_amt = 0 THEN ''not_paying_users''
END Paying_users
, LTV_group
, LO_event_type
, event_type
, is_received_resource
, transaction_type
, transaction_source_lvl_0
, transaction_source
, is_bargain
, match_type
, match_type_id
, match_mode_type
, resource_type
, resource_sub_type
, resource_id
, resource_version
, SUM(resource_cnt) resource_cnt
FROM DATA
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
) A

LEFT JOIN

(SELECT resource_name, resource_version, resource_sub_type, resource_coin_value  FROM (SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (partition BY resource_name, resource_version ORDER BY db_create_date DESC, resource_id DESC) AS resource_rank
  FROM candivore.prod.DIM_RESOURCE_VERSION)
  WHERE resource_rank = 1)) B
ON A.resource_id = B.resource_name AND A.resource_version = B.resource_version AND A.resource_sub_type = B.resource_sub_type)

`



var statement = snowflake.createStatement({sqlText: my_sql_command});
      statement.execute();
  ';