Data Science Jobs SQL Case Study
收藏资源简介：
该数据集包含跨国公司薪资数据，涉及不同国家、工作条件和公司特征的薪资、工作条件和公司特征。关键属性包括工作年份、经验水平、雇佣类型、职位名称、薪资、薪资货币、以美元计的薪资、员工居住地、远程工作比例、公司位置和公司规模。
This dataset contains salary data from multinational corporations, covering salary information, working conditions and company characteristics across different countries. Key attributes include work year, experience level, employment type, job title, salary, salary currency, salary denominated in USD, employee residence, remote work ratio, company location, and company size.
Data Science Jobs SQL Case Study
数据集描述
该数据集包含跨国公司薪酬数据，涉及多个国家的薪资、工作条件和公司特征。关键属性如下：
-
work_year: 薪资支付的年份，包括：
- 2020: 过去年份的确定金额。
- 2024: 当前年份的估计金额。
-
experience_level: 工作年份中的经验水平，包括：
- EN: 初级/初级。
- MI: 中级/中级。
- SE: 高级/专家。
- EX: 执行级/总监。
-
employment_type: 雇佣类型，包括：
- PT: 兼职。
- FT: 全职。
- CT: 合同。
- FL: 自由职业。
-
job_title: 工作年份中的角色（例如，数据科学家、机器学习工程师）。
-
Salary: 支付的总薪资金额。
-
salary_currency: 薪资支付的货币，遵循ISO 4217货币代码标准。
-
salary_in_usd: 基于当年平均汇率转换为美元的薪资。
-
employee_residence: 员工工作年份中的主要居住国家，表示为ISO 3166国家代码。
-
remote_ratio: 远程工作比例，包括：
- 0: 无远程工作（少于20%）。
- 50: 部分远程工作。
- 100: 完全远程工作（超过80%）。
-
company_location: 雇主主要办公室或合同分支所在国家，表示为ISO 3166国家代码。
-
company_size: 公司年平均员工人数，包括：
- S: 小型公司（少于50名员工）。
- M: 中型公司（50至250名员工）。
- L: 大型公司（超过250名员工）。
案例研究问题及SQL查询
1. 识别提供全远程管理职位且薪资超过$90,000 USD的国家
SQL SELECT DISTINCT (t.company_location) FROM ( SELECT company_location, job_title, remote_ratio, AVG(salary_in_usd), COUNT(remote_ratio) FROM salaries GROUP BY company_location, job_title, remote_ratio HAVING remote_ratio = 100 AND job_title LIKE %manager% AND AVG(salary_in_usd) > 90000 ) t;
结果：
- US (United States)
- IN (India)
- MX (Mexico)
- AU (Australia)
- FR (France)
2. 识别拥有最多大型公司（公司规模L）的前5个国家
SQL select company_location,count(company_size) from salaries where company_size = L and experience_level = EN group by company_location order by count(company_size) desc limit 5;
结果：
- United States (US) – 53家大型公司
- Germany (DE) – 10家大型公司
- Canada (CA) – 10家大型公司
- United Kingdom (GB) – 8家大型公司
- India (IN) – 6家大型公司
3. 计算享受全远程工作且薪资超过$100,000 USD的员工比例
SQL select ((select count() from salaries where salary > 100000 and remote_ratio =100) /(select count() from salaries where salary > 100000) * 100) as enjoying_remote_position_with_100k_salary;
结果：
- 32.35%的员工享受全远程工作且薪资超过$100,000 USD。
4. 识别初级职位平均薪资超过市场平均薪资的国家
SQL select t1.job_title,t2.company_location,t1.average,t2.average_per_country from ( select job_title,avg(salary_in_usd) as average from salaries where experience_level = EN GROUP BY job_title ) t1 join (select company_location,job_title,avg(salary_in_usd) as average_per_country from salaries where experience_level = EN group by company_location,job_title) t2 on t1.job_title = t2.job_title and t2.average_per_country > t1.average;
5. 识别每个职位在不同国家支付的最高平均薪资
SQL select t2.company_location,t2.job_title,t1.max_avg from (select job_title,max(avg_sal) as max_avg from (select company_location,job_title,avg(salary_in_usd) as avg_sal from salaries GROUP BY company_location,job_title order by job_title)t group by job_title order by job_title) t1 join (select company_location,job_title,avg(salary_in_usd) as avg_sal from salaries GROUP BY company_location,job_title order by job_title) t2 on t1.job_title = t2.job_title where t1.max_avg = avg_sal order by job_title;
6. 识别过去三年（2022-2024）平均薪资持续增长的国家
SQL with temp as ( select * from salaries where company_location in ( select company_location from ( select company_location,avg(salary),count(distinct work_year) from salaries where work_year >= (year(current_date()))-2 GROUP BY company_location having count(DISTINCT work_year) =3 order by company_location ) t )) select company_location, max(case when work_year = 2022 then avg_salary end) as Average_2022, max(case when work_year = 2023 then avg_salary end) as Average_2023, max(case when work_year = 2024 then avg_salary end) as Average_2024 from ( select company_location,work_year,avg(salary_in_usd) as avg_salary from temp group by company_location,work_year order by company_location) t group by company_location having Average_2024 > Average_2023 and Average_2023 > Average_2022;
7. 2021与2024年各经验水平的全远程工作比例
sql SELECT experience_level, SUM(CASE WHEN work_year = 2021 THEN remote_ratio ELSE 0 END) / COUNT() AS remote_2021, SUM(CASE WHEN work_year = 2024 THEN remote_ratio ELSE 0 END) / COUNT() AS remote_2024 FROM salaries GROUP BY experience_level;
8. 2023-2024年各经验水平和职位的平均薪资增长百分比
sql WITH salary_2023 AS ( SELECT job_title, experience_level, AVG(salary_in_usd) AS avg_salary_2023 FROM salaries WHERE work_year = 2023 GROUP BY job_title, experience_level ), salary_2024 AS ( SELECT job_title, experience_level, AVG(salary_in_usd) AS avg_salary_2024 FROM salaries WHERE work_year = 2024 GROUP BY job_title, experience_level ) SELECT s2023.job_title, s2023.experience_level, ((s2024.avg_salary_2024 - s2023.avg_salary_2023) / s2023.avg_salary_2023) * 100 AS salary_increase_percentage FROM salary_2023 s2023 JOIN salary_2024 s2024 ON s2023.job_title = s2024.job_title AND s2023.experience_level = s2024.experience_level;
9. 为经验水平实施基于角色的访问控制（RBAC）
sql -- 伪代码 CREATE ROLE entry_level_access; CREATE ROLE mid_level_access; CREATE ROLE senior_level_access; GRANT SELECT ON salaries TO entry_level_access WHERE experience_level = EN; GRANT SELECT ON salaries TO mid_level_access WHERE experience_level = MI; GRANT SELECT ON salaries TO senior_level_access WHERE experience_level = SE;
