-
Notifications
You must be signed in to change notification settings - Fork 0
/
5_optimal_skills.sql
50 lines (46 loc) · 1.62 KB
/
5_optimal_skills.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/*
Answer:What are the most optimal skills to learn (aka it's in high demand and a high-paying skill
- Identify skills in high demand and associated with high average salaries for Data Analyst roles
- Concentrates on remote skills that offer job security (high demand) and financial benefits(high salaries),offering strategic insights for career development in data analysis
*/
WITH skills_demand AS(
SELECT
skills_dim.skill_id,
skills_dim.skills,
COUNT(skills_job_dim.job_id) AS demand_count
FROM job_postings_fact
INNER JOIN skills_job_dim ON job_postings_fact.job_id = skills_job_dim.job_id
INNER JOIN skills_dim ON skills_job_dim.skill_id = skills_dim.skill_id
WHERE
job_title_short = 'Data Analyst'
AND salary_year_avg IS NOT NULL
AND job_work_from_home = True
GROUP BY
skills_dim.skill_id
), average_salary AS (
SELECT
skills_job_dim.skill_id,
ROUND(AVG(job_postings_fact.salary_year_avg), 0) AS avg_salary
FROM job_postings_fact
INNER JOIN skills_job_dim ON job_postings_fact.job_id = skills_job_dim.job_id
INNER JOIN skills_dim ON skills_job_dim.skill_id = skills_dim.skill_id
WHERE
job_title_short = 'Data Analyst' AND salary_year_avg IS NOT NULL
AND job_work_from_home = TRUE
GROUP BY
skills_job_dim.skill_id
)
SELECT
skills_demand.skill_id,
skills_demand.skills,
demand_count,
avg_salary
FROM
skills_demand
INNER JOIN average_salary ON skills_demand.skill_id = average_salary.skill_id
WHERE
demand_count > 10
ORDER BY
avg_salary DESC,
demand_count DESC
LIMIT 25