select case when drop_number = '3' then '6'

when drop_number in ('2','1') then '3' end as score_1,

case when rarity = 'Common' then '3'

when rarity = 'Rare' then '6'

when rarity = 'Epic' then '9' end as score_2,

case when world_cup_type='Mens' then '6'

when world_cup_type='Womens' then '3' end as score_3,

case when event_type = 'Goal' then '10'

when event_Type = 'Save' then '7'

when event_type = 'Pass' then '5' end as score_4,

case when minute ilike '%90+%' then '6'

when minute ilike '8%' then '4'

else '2' end as score_5,

score_1 + score_2 + score_3 + score_4 + score_5 as score,

ASSET_ADDED_AT as buy_time, nft_asset_name as name, nft_asset_id, initcap(Drop_Name) as drop_name, drop_number, item_no, Rarity, external_url,

year ||' '|| host ||' '|| country_1 ||' '|| country_1_score ||' '|| country_2 ||' '|| country_2_score ||' '|| event_type ||' '|| player ||' '|| minute as temp

from algorand.core.fact_account_asset a

join algorand.nft.ez_nft_metadata_fifa b on a.asset_id = b.nft_asset_id

where address ilike '{{Wallet_Address}}'

and a.amount > 0

)

select sum(score) as total_user_score, sum(score_1) as drop_number_score, sum(score_2) as rarity_score, sum(score_3) as world_cup_type_score,

sum(score_4) as event_type_score, sum(score_5) as minute_score

from temp