Need help with SQL query in Teradata
Seems like you need a conditional sum based on CASE:

select member_number,
   sum(case when social_activity = 'challenges' then step_count else 0 end) as challenges,
   sum(case when social_activity = 'posts' then step_count else 0 end) as posts
from ABC
group by member_number