How do I calculate the average weight of riders by competition?
You need to join the rider and participants tables together using the common column (name->rider), group all of the participants for a given competition together, and calculate the average, like so: 

select competition, avg(weight) as average_weight
from participants
join rider on rider.name = participants.rider
group by competition
;


Fiddle here.