5_optimal_skills.sql

/*
 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 positions with specified salaries
 Why? Targets skills that offer job security (high demand) and financial benefits (high salaries), 
 offering strategic insights for career development in data analysis
 */
WITH top_demand_skills AS (
    SELECT
        COUNT(*) AS skill_count,
        sd.skills,
        ROUND(AVG(jpf.salary_year_avg), 0) AS avg_salary
    FROM
        job_postings_fact jpf
        INNER JOIN skills_job_dim sjd ON sjd.job_id = jpf.job_id
        INNER JOIN skills_dim sd ON sd.skill_id = sjd.skill_id
    WHERE
        jpf.job_title_short = 'Data Analyst'
        AND jpf.job_work_from_home = True
        AND jpf.salary_year_avg IS NOT NULL
    GROUP BY
        sd.skills
    HAVING
        COUNT(*) > 30
)
SELECT
    *
FROM
    top_demand_skills tds
ORDER BY
    tds.skill_count DESC,
    tds.avg_salary DESC;