Read job postings like data

The problem

Priya is finishing a statistics degree and has eight weeks before graduate hiring starts. She knows SQL and Excel and wants to pick the next tool to learn, but every blog post gives a different answer. She saved 300 analyst postings from a job board into one file. You will treat those postings as a dataset: pull the tools, the years of experience and the visa line out of free text with regular expressions, count them, and tell Priya what to learn next with numbers behind it.

The file data/postings.csv is synthetic, generated by make_data.py with a fixed seed. Every company, title and sentence in it is invented. data/labels.csv holds what the generator put into each posting, so the tests can check your extractor against the truth. A real feed has no labels, which is why the extractor has to be right.

What you will build from scratch

• TOOL_PATTERNS: one regex per tool, safe against the classic false matches.
• extract_tools, extract_years, visa_status: turn one posting's text into fields.
• skill_counts and experience_buckets: the frequency table and the experience distribution.
• learn_next: the most requested tools you do not know yet.

The idea

A posting is a document. To count anything you first turn each document into a row: a set of tools, a minimum and maximum number of years, and one visa label. Counting is then easy:

share(tool) = postings that name the tool / all postings

Count each tool once per posting. A posting that says SQL four times is one vote for SQL.

The hard part is extraction, and it fails in the same few ways every time:

1. Boilerplate. Postings start with a company blurb. "Our teams excel at service" contains the word excel, and "for over 20 years" contains a number of years. Cut the blurb off before you search. Here every posting has an About the role: marker, so role_text keeps the text from there on.
2. Short names inside words. The letter R is inside Reporting, and R&D is a department, not a language. Match R case sensitively, as a whole word, and not next to &: (?<![\w&])R(?![\w&]). \b (a word boundary) does the same job for longer names: \bSQL\b matches "SQL" but not "SQLite".
3. Spelling variants. Power BI appears as Power BI, PowerBI and Power-BI. One pattern covers all three: \bPower[\s-]?BI\b.
4. Ranges. "0 to 2 years" asks for zero years. A pattern that grabs the number right before "years" reads it as 2 and hides the entry level roles. Capture the low end and the high end: (\d+)\s*(?:(?:-|to)\s*(\d+))?\s*\+?\s*years?.
5. Negation. "We will not sponsor visas" contains the word sponsor. Check the refusal patterns first, then the positive ones, and call everything else silent.

Walk through

Run these from the lesson folder.

Step 1. Look at one posting.

import sys
sys.path.insert(0, "solution")
import solution as s

p = s.load_postings()[1]
print(p["text"])
print(s.extract_tools(p["text"]), s.extract_years(p["text"]), s.visa_status(p["text"]))

It prints ['Power BI', 'Python', 'SQL'] (2, None) silent. "PowerBI" was caught, "excel at service" was not, and "40 years" in the blurb was not read as experience.

Step 2. See how much the traps cost on the whole file.

import re, sys
sys.path.insert(0, "solution")
import solution as s

P = s.load_postings()
print("excel anywhere:", sum(1 for p in P if re.search(r"\bexcel\b", p["text"], re.I)))
print("Excel in the role:", s.skill_counts(P)["Excel"])
print("plain \\bR\\b:", sum(1 for p in P if re.search(r"\bR\b", p["text"])))
print("R the language:", s.skill_counts(P)["R"])
print("exact 'Power BI':", sum(1 for p in P if "Power BI" in p["text"]))
print("Power BI any spelling:", s.skill_counts(P)["Power BI"])
naive = sum(1 for p in P if re.search(r"\b0\s*(?:to|-)\s*\d+ years", s.role_text(p["text"])))
print("ranges that start at 0:", naive)

The naive Excel count is every posting, because every blurb says "excel at". The naive R count more than doubles R because of R&D. The exact string search misses most Power BI postings. On the ranges: 32 postings ask for 0 years at the low end ("0-2 years" or "0 to 2 years"), and a pattern that reads the last number before "years" would put all 32 in a higher bucket.

Step 3. Count and bucket.

import sys
sys.path.insert(0, "solution")
import solution as s

P = s.load_postings()
counts = s.skill_counts(P)
for tool, c in sorted(counts.items(), key=lambda x: -x[1]):
    print("%-9s %4d  %5.1f%%" % (tool, c, 100.0 * c / len(P)))
print(s.experience_buckets(P))

Experience by minimum years: 95 postings ask for 0 or 1, 93 for 2 or 3, 45 for 4 or 5, 22 for 6 or more, and 45 do not say.

Step 4. Visa lines: 244 postings say nothing, 32 refuse sponsorship and 24 offer or consider it. Silence is not a yes. If you need sponsorship, the 24 are where to start and the 244 need a question to the recruiter.

Step 5. Decide. Priya knows SQL and Excel.

import sys
sys.path.insert(0, "solution")
import solution as s

print(s.learn_next(s.skill_counts(s.load_postings()), {"SQL", "Excel"}))

It prints ['Python', 'Tableau']. Python is in 136 of 300 postings. Tableau (101) and Power BI (94) are close, so the honest advice is: learn Python next, then one BI tool, and pick between Tableau and Power BI by the postings in the city or country you will apply to. The counts also show that 95 postings start at 0 or 1 year, so Priya should not skip postings that ask for "0 to 2 years".

Your turn

Open exercise.py. Write the patterns at the top, then fill in extract_tools, extract_years, visa_status, skill_counts, experience_buckets and learn_next. Then run, from this folder:

python3 test_lesson.py

To see the reference solution pass: CHECK_SOLUTION=1 python3 test_lesson.py.

Once it passes, run python3 exercise.py and change the known set in main to your own tools.

Check yourself

1. Why count a tool once per posting instead of once per mention?
2. Your R count looks too high. What do you check first?
3. A posting says nothing about visas. How do you label it, and why not "yes"?
4. Why is the top tool not always the right next thing to learn?

In real job postings

In the Real Analyst Jobs live feed on 25 October 2026, of the 2,102 US analyst postings that list tools, 72% ask for SQL, 48% for Python, 36% for Tableau and 24% for Power BI; in India (914 postings that list tools) Power BI is at 39% and Tableau at 34%. Of 1,128 US analyst postings read on 7 October 2026, 87% said nothing about visa sponsorship, 10% said no and 2.6% said they sponsor or will consider it. See the US analyst job market report, the India report and the visa sponsorship study.

Next

02: SQL interview questions