On this month’s “from the data” post we’re taking a look at the top technology jobs in the United States!
The Query
- Used the dataset that powers the NetWise platform.
- We have geographic fields for a contact’s personal address and their company’s address. For this analysis, we used the ‘personal_country’ field, which is also targetable in our UI via the advanced filters section.
- Other fields used in this analysis: b2b_job_function, title, and personal_country
- Query filters used in this analysis:
- personal_country=”United States
- b2b_job_function=”technology”
- personal_state IS NOT NULL
- title IS NOT NULL
- Used LOWER() string function to clean the titles
- ROW_NUMBER window function
- Query composed of three CTEs :
- A CTE, or common table expression, is a way to break down a complex query into smaller queries.
- For the first CTE, I selected the personal_state, title, and person_id columns. I aggregated the person_id column using the COUNT() function and grouped by personal_state and title.
- For the second CTE, I selected the data from the first CTE and applied the ROW_NUMBER() function (link: https://docs.aws.amazon.com/redshift/latest/dg/r_WF_ROW_NUMBER.html). I partitioned the data by the personal_state column and ordered by the count of contacts column in descending order.
- For the third CTE, I selected the data from the first CTE to get a count of total contacts by state. To do this, I aggregated the contact count column using SUM() and grouped by personal state.
- The main query uses data from the second and third CTEs:
- I selected the columns from the second CTE
- I selected the total_contacts column from the third CTE. I joined this CTE to the second CTE using the personal_state column.
- I created a new column based on the proportion of contacts with a given title divided by the total number of contacts.
- I added a WHERE clause to filter the row number to five or less.
- The result of this query is the top 5 technology titles per state.
Insights & Takeaways
Before looking at the top 5 titles per state, I wanted to drill down to the top 1. To do this, I pasted the data into Excel, and filtered the row number column to 1. You can also get to this point by modifying the WHERE clause on the row number to one instead of five or less. Usually for this type of analysis, we filter out minor US territories such as Puerto Rico, Guam, and others. We don’t have a lot of scale for these small geographic regions, and we don’t want the data to be skewed. For this analysis, we filtered out these territories as usual except for Washington D.C. because the scale was similar to other U.S. states. For 84% of U.S. states (43/51), software engineer was the top title. The states with the highest proportion of software engineer titles relative to other tech titles in the state are California (9%), Washington (11%), and Massachusetts, Utah, and New York (all at 6%). For five states, project engineer was the top title. Interestingly, Chief Technology Officer was the top title for two states, Arkansas and Wyoming. The second top title for many states is ‘senior software engineer.’ The top titles for all states are: software engineer, project engineer, senior software engineer, software developer, and chief technology officer. Top title means that these are the top 5 titles that appeared in each state’s list of top 5 titles with the highest proportion of contacts in the technology job function. Titles that aren’t as common across multiple states but still are top titles in certain states are: web developer, quality manager, network administrator, data analyst, and network engineer.
See you next month ✌
Want play with the data on your own? Sign up here.
More NetWise:
YouTube | Twitter | Facebook | Linkedin | Web I Blog+Newsletter
