MGTF495 SQL Data Science For Finance
select count(*)*1.0/(select count(*) from “Airbnb_listings” where amenities @>ARRAY [‘Cable TV’, ‘Smoke Detector’]) as freq, zipcode from “Airbnb_listings”
where amenities @>ARRAY [‘Cable TV’, ‘Smoke Detector’]
Copyright By PowCoder代写 加微信 powcoder
group by zipcode
select R.region_name, avg(salary) as avg, variance(salary) as variance, percentile_cont(0.5) within group (order by salary) as median
from “Airbnb_listings” A, ag_class_employees E, ag_class_departments D, ag_class_locations L, ag_class_countries C,
ag_class_regions R
where R.region_id = C.region_id
and C.country_id = L.country_id
and L.location_id = D.location_id
and D.department_id = E.department_id
group by R.region_name
having count(distinct E.employee_id)>5;
select id,
ltrim(regexp_replace(price,’,’,”),’$’)::numeric as daily_price,
description
from “Airbnb_listings”
where description ~ ‘(((O|o)ne|(T|t)wo|(T|t)hree|(F|f)our)\s+(bedrooms?|BR)) | (\d+(-)?\s*((b|B)edrooms?|BR))’
or description ~ ‘(((O|o)ne|(T|t)wo|(T|t)hree|(F|f)our)\s+(bathrooms?|BA)) | (\d+(-)?\s*((b|B)athrooms?|BA))’
order by daily_price desc
with temp as (select id, src, publishdate, news, title
from usnewspaper
where src ilike ‘%nytimes%’
and publishdate > ‘2020-10-01’::date
and publishdate < '2021-01-01'::date
and news ilike '%Trump%'
words as (select id, title, trim(regexp_split_to_table(title, '[.,:;?]*\s+'))as word from temp)
select title, word, count(*) from words where word not in ('A','An','The','Comments','Editorial','Opinion') and word~'^[A-Z]' group by title, word;
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com