Intro.sql

SELECT
    jpf.job_id,
    jpf.job_title_short,
    cd.name AS company_name,
    cd.company_id
FROM
    job_postings_fact AS jpf
INNER JOIN
    company_dim AS cd 
    ON jpf.company_id = cd.company_id;

SELECT
    jpf.job_id,
    jpf.job_title_short,
    sjd.skill_id,
    --- sd.skills
FROM 
    job_postings_fact AS jpf
INNER JOIN
    skills_job_dim AS sjd
    ON jpf.job_id = sjd.job_id;
INNER JOIN
    skills_dim AS sd
    ON sjd.skill_id = sd.skill_id;

    /*
    find the top 10 companies for posting jobs with >3000 postings
    limit to only us only jobs
    add a comment for each step of the query
    */

EXPLAIN
SELECT
    cd.name AS company_name,
    COUNT(jpf.job_id) AS total_postings
FROM
    job_postings_fact AS jpf
LEFT JOIN
    company_dim AS cd
    ON jpf.company_id = cd.company_id
WHERE
    jpf.job_country = 'United States'
GROUP BY
    cd.name
HAVING
    COUNT(jpf.job_id) > 3000
ORDER BY
    total_postings DESC
LIMIT 10;   
comment: This query retrieves the top 10 companies with more than 3000 job postings in the US. It joins the job_postings_fact table with the company_dim table to get the company names, filters for US jobs, groups by company name, and orders the results by total postings in descending order.
here's a breakdown of the query:
1. SELECT: We select the company name and count of job postings.
2. FROM: We specify the job_postings_fact table as our main source of data.
3. INNER JOIN: We join the company_dim table to get the company names based on the company_id.
4. WHERE: We filter the results to include only job postings in the US.
5. GROUP BY: We group the results by company name to aggregate the count of job postings for each company.
6. HAVING: We filter the groups to include only those with more than 3000 job postings.
7. ORDER BY: We order the results by the total number of postings in descending order to get the companies with the most postings at the top.
8. LIMIT: We limit the results to the top 10 companies.