SQL_WEEK_II_Answer_key

SQL_WEEK_II_Answer_key.sql

-- Output a count of jobs by the organization name and id

SELECT
  jobs.organization_id
  ,organizations.name
  ,COUNT(jobs.id) as jobs
FROM jobs
LEFT JOIN organizations ON jobs.organization_id = organizations.id
GROUP BY 1,2

-- Output a count of jobs by the Account name and id

SELECT
  jobs.account_id
  ,accounts.name
  ,COUNT(jobs.id) as jobs
FROM jobs
LEFT JOIN accounts ON jobs.organization_id = accounts.id
GROUP BY 1,2

-- Having clause, only accounts having 5 / 10 /  15  jobs or more

SELECT
  jobs.account_id
  ,accounts.name
  ,COUNT(jobs.id) as jobs
FROM jobs
LEFT JOIN accounts ON jobs.organization_id = accounts.id
GROUP BY 1,2
HAVING COUNT(*) > 15

-- What is the median of Prices Paid - (DB Fiddle)

SELECT
  percentile_cont(.5) within group(order by costs)
FROM jobs


--What is the syntax to output clean customer Data

initcap(First_name) || ' ' ||  initcap(last_name)

--What jobs have unexpected State Abbs?

WHERE length(state_abb) <> 2

--With example of how to update a job

UPDATE public.jobs SET state_abb = 'NY' WHERE state_abb = 'New York'

--How Many “Matt’s” are in the Customer table?  - Practice table

SELECT COUNT(*)
FROM public.practice
WHERE lower(name) like 'matt%'

--Output just the numbers from the phone records

SELECT name, regexp_replace(name, '[^0-9]','','g') as cleanednumber
FROM public.practice
WHERE tier = 'Number'

--Output the cities in X that in parenthesis 

SELECT name, substring(name from '\((.+)\)') as city
FROM public.practice
WHERE tier = 'City'

--How many Jobs in 2018? Without using >= or Between

SELECT COUNT(*)
FROM public.jobs
WHERE extract('year' from created_at) = 2018

--Avg amount of Jobs weekdays vs Weekends?

SELECT 
  CASE WHEN extract('ISODOW' from created_at) IN (6,7) then 'weekend' ELSE 'weekday' END as DOW
    ,COUNT(*)::numeric/COUNT(distinct created_at::date)
FROM public.jobs
GROUP BY 1


--Output jobs per month from 2018, but exclude current month

SELECT 
  DATE_TRUNC('month', created_at)::date
    ,COUNT(*)
FROM public.jobs
WHERE jobs.created_at < DATE_TRUNC('month', current_timestamp)
  and jobs.created_at >= '2018-01-01'
GROUP BY 1
ORDER BY 1


-- Rank the states based on number of Jobs (last part only outputs top org per state remove for ranking)


with t1 as (
SELECT 
  state_abb
  ,o.name
  ,COUNT(*) as jobs
  , RANK() OVER (PARTITION BY state_abb ORDER BY COUNT(*) DESC)
FROM public.jobs
LEFT JOIN public.organizations o ON o.id = jobs.organization_id

GROUP BY 1,2
ORDER BY 2 DESC
)
SELECT *
FROM t1 
WHERE rank = 1