most_optimal_skill.sql

/*
 Insights from demand and salary data to pinpoint skills that are both in high demand and have salaries
 Group by skill_id, guarantees:
 - No duplicates
 - No collisions
 - Clean grouping
 - Correct aggregation of demand and salary
 But group by skills we can merge different skills and there can be duplicates 
 */
SELECT skills_dim.skill_id,
    skills_dim.skills,
    COUNT(skills_job_dim.job_id) AS demand_count,
    ROUND(AVG(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 Engineer'
    AND salary_year_avg IS NOT NULL
    AND job_work_from_home = True
GROUP BY skills_dim.skill_id
HAVING COUNT(skills_job_dim.job_id) > 10
ORDER BY avg_salary DESC,
    demand_count DESC
LIMIT 30;