FirstNameScores.SQL

create temp table
	first_scores
as select
	first_name,
	ln(cast(sum(case when t.gender = 'M' then 1 else 0 end) + 1 as float) / max(num)) as male_score,
	ln(cast(sum(case when t.gender = 'F' then 1 else 0 end) + 1 as float) / max(num)) as female_score
from
	train as t
left join
	(
		select
			gender,
			cast(count(*) as float) as num
		from
			train
		group by
			gender
	) as n
on
	t.gender = n.gender
group by
	first_name;