case

when moment_stats_full:metadata:playerPosition = 'RB' then 'Running Back (RB)'

when moment_stats_full:metadata:playerPosition = 'LB' then 'Linebacker (LB)'

when moment_stats_full:metadata:playerPosition = 'WR' then 'Wide Receiver (WR)'

when moment_stats_full:metadata:playerPosition = 'DB' then 'Defensive Backs (DB)'

when moment_stats_full:metadata:playerPosition = 'QB' then 'Quarterback (QB)'

when moment_stats_full:metadata:playerPosition = 'K' then 'Kicker (K)'

when moment_stats_full:metadata:playerPosition = 'P' then 'Punter (P)'

when moment_stats_full:metadata:playerPosition = 'TE' then 'Tight End (TE)'

when moment_stats_full:metadata:playerPosition = 'OL' then 'Offensive Linemen (OL)'

when moment_stats_full:metadata:playerPosition = 'DL' then 'Defensive Line (DL)'

else 'Others'

end as player_position,

sum(price) as volume_of_sale,

max(price) as maximum_price,

min(price) as minimum_price,

median(price) as median_price,

avg(price) as avg_price,

count(tx_id) as number_of_sale,

'touchdoen' as result

flow.core.dim_allday_metadata m left join flow.core.ez_nft_sales s on s.nft_id = m.nft_id

where (moment_description ilike '%touchdown%' or moment_description ilike '%-yard%' or moment_description ilike '%score%') and

tx_succeeded = TRUE and

player_position != 'others'

group by 1

union all

select

case

when moment_stats_full:metadata:playerPosition = 'RB' then 'Running Back (RB)'

when moment_stats_full:metadata:playerPosition = 'LB' then 'Linebacker (LB)'

when moment_stats_full:metadata:playerPosition = 'WR' then 'Wide Receiver (WR)'

when moment_stats_full:metadata:playerPosition = 'DB' then 'Defensive Backs (DB)'