HackerRank Occupations.sql

set search_path to data_sci

-- Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically 
-- and displayed underneath its corresponding Occupation. The output column headers 
-- should be Doctor, Professor, Singer, and Actor, respectively.

-- Note: Print NULL when there are no more names corresponding to an occupation.

-- We'll use employees table since it has same info

with name_agg as(
select distinct job_title,
array_agg(last_name) over (partition by job_title order by job_title)
from employees
order by job_title
	
	
select distinct job_title, last_name,
row_number() over (partition by job_title order by job_title) as row_num
from employees
order by job_title, row_num
	
select distinct job_title, count(job_title)
from employees
group by job_title
order by job_title, row_num

	
-- This is an interesting problem.
-- Just pick three job titles from employees
-- partition by job_title and assign a row number by last_name
-- The first last_name for each job_title will get row_num=1
-- The second last_name for each job_title will get row_num=2, etc.
-- The case statement puts the last_names in the aliased columns
-- The outer select does a group by row_num so all the values with same
-- row_num are on the same row.
-- group by requires an aggregation function. We need one that works on text so 
-- use min or max. There is only one value for each row_num so either will work.
with cte as(
	select	
	case when job_title='financial advisor' then last_name end as Advisor,
	case when job_title='financial analyst' then last_name end as Analyst,
	case when job_title='food chemist' then last_name end as Chemist,
	row_number() over (partition by job_title order by last_name) as row_num
	from employees
	order by row_num)
select max(Advisor) as Advisor, -- max works the same as min
	min(Analyst) as Analyst, 
	min(Chemist) as Chemist
	from cte
GROUP by row_num
order by row_num

	
-- SQLServer and Oracle have pivot clauses
-- Postgres soes have a tablefunc module that provides a crosstab function
-- that can perform pivots

	
-- Just for fun, create a stored procedure that inserts results into table
drop PROCEDURE if EXISTS AAC

drop TABLE if exists AAC;

create table AAC (
aac_id integer generated by default as identity,
advisors text,
analysts text,
chemists text
);
	
CREATE PROCEDURE aac()
LANGUAGE sql
	as $$
	set search_path to data_sci;
	

	
	insert into AAC
	(advisors, analysts, chemists)
	(with cte as(
		select	
		case when job_title='financial advisor' then last_name end as Advisor,
		case when job_title='financial analyst' then last_name end as Analyst,
		case when job_title='food chemist' then last_name end as Chemist,
		row_number() over (partition by job_title order by last_name) as row_num
		from employees
		order by row_num)
	select max(Advisor) as Advisor, -- max works the same as min
		min(Analyst) as Analyst, 
		min(Chemist) as Chemist
		from cte
	GROUP by row_num
	order by row_num);
	
	delete from AAC
	where advisors is NULL and 
	analysts is NULL and 
	chemists is NULL
	
	
	$$
	
call AAC()
	
-- Get salary stats	
select round(avg(salary), 2), round(stddev(salary),2),
	round(mode() within group (order by salary),2),
	round(percentile_cont(0.5) within group (order by salary)::numeric, 2) as median
from employees


-- Histogram with $5000 bins
select floor(salary/5000)*5000, count(*)
from employees
group by 1
order by 1