LindaSQL

-- FOR THE LINDA PROJECT

Create table names(
	nam varchar(20),
	gender varchar(2),
	tot int,
	year int
);

-- Getting all of the Lindas

select
	*
from 
	names
where 
	nam = 'Linda' 
	AND gender = 'F'
order by 
	year asc
;

-- making a new column to give me the rank of each name by each year and gender (in a CTE)
WITH ranked_names AS (
    select
        name
        , gender
        , year
        , tot
        , RANK() OVER (PARTITION BY year, gender ORDER BY tot DESC) AS name_rank
    from baby_names
)
SELECT name, gender, year, tot, name_rank
FROM ranked_names
WHERE name_rank = 1;

-- Making a temp table for the data
create temp table ranked_names AS (
    SELECT
        nam
        , gender
        , year
        , tot
        , RANK() OVER (PARTITION BY year, gender ORDER BY tot DESC) AS name_rank
    FROM ssnames
);

-- First graph, the sheer number of babies born with the name linda connected line graph
-- the rank graph in a like a bar thing maybe
-- contrast linda vs mary
-- a line graph with the line showing how many girls are named the most common name 
--       and the color changes for each new name
-- Outlook for linda, last ten years

--The male Linda
select
	*
from
	ranked_names
where 1=1
	and nam = 'Linda'
	and gender = 'M'
order by
	year asc
;

--The number one names throughout the years
select
	nam
	, year
	, tot
from ranked_names
where 1=1
	and name_rank = 1
	and gender = 'F'
order by
	year asc
;