Working with data is becoming an increasingly important skill in the modern workplace.

Data is no longer the domain of analysts and software engineers. With today's technology, anyone can work with data to analyse trends and inform their decision making.

A fundamental concept when working with data is 'querying' a data set. This is to literally ask questions about a set of data. A query language is a software language that provides a syntax for asking such questions.

If you don't have any experience writing queries, they can appear a little intimidating. However, with a little practice, you can master the basics.

Here's how you can get started in Google Sheets.

Google Visualization API Query Language

You may already be using Google Sheets for much of your day-to-day work. Perhaps you are familiar with using it to generate charts and graphs.

The Google Visualization API Query Language is the magic that works behind the scenes to make this possible.

But did you know you can access this language through the QUERY() function? It can be a powerful tool for working with large sheets of data.

There are a lot of similarities between the query language and SQL.

In both cases, you define a data set of columns and rows, and choose different columns and rows by specifying various criteria and conditions.

In this article, the example data will come from a large CSV file containing international football results between 1872 and 2019. You can download the data from Kaggle.

In a new Google Sheet, upload the CSV file. You can select all the data with Ctrl+A (or Cmd+A on Mac).

From the menu ribbon, choose Data > Named ranges... and call the range selected something like 'data'. This will make it easier to work with.

Now, you are ready to start querying the data. Create a new tab in the spreadsheet, and in cell A1, create a new QUERY() formula.

Get all England matches

This first query finds all the rows in the data set where England are either the home team or the away team.

The QUERY() formula takes at least two arguments. The first is the named range, which will be the data set queried. The second is a string that contains the actual query.

=QUERY(data, "SELECT * WHERE B = 'England' OR C = 'England'")

Let's break this down.

SELECT * asks to return all columns in the data set. If you only wanted columns A, B and C, you would write SELECT A, B, C.

Next, you include a filter to find only rows where column B or column C contain the team 'England'. Make sure to use single-quotes for strings inside the query. Double-quotes are used to open and close the query itself.

This formula returns all the rows where England have played. If you want to search for another team, simply change the condition in the filter.

Count all friendly matches

Next, let's count how many friendly matches are in the data set.

=QUERY(data, "SELECT COUNT(A) WHERE F = 'Friendly'")

This makes use of the Query Language's COUNT() function. This is an example of an aggregate function. Aggregate functions summarise many rows into one.

For example, in this data set there are 16,716 rows where column F equals 'Friendly'. Instead of returning all these rows, the query returns a single row - which counts them instead.

Other examples of aggregate functions include MAX(), MIN() and AVG(). Instead of returning all the rows matching the query, it finds their maximum, minimum and average values instead.

Group by tournament

Aggregate functions can do more if you use a GROUP BY statement alongside them. This query finds out how many matches have been played by each tournament type.

=QUERY(data, "SELECT F, COUNT(A) GROUP BY F")

This query groups the data set by each of the values in column F. It then counts how many rows there are in each group.

You can use GROUP BY on more than one column. For example, to find how many matches have been played in each country by tournament, use the query below:

=QUERY(data, "SELECT H, F, COUNT(A) GROUP BY H, F")

Let's try some more advanced filtering.

Get all England vs Germany matches

You can specify more complex filter logic using the AND and OR keywords. For readability, it can help to use brackets around each part of the filter.

For example, to find all the matches between England and Germany:

=QUERY(data, "SELECT * WHERE (B = 'England' AND C = 'Germany') OR (C = 'England' AND B ='Germany')")

This filter has two criteria - one where England are the home team and Germany are away, and the other vice versa.

Using data validation makes it easy to pick any two teams in the data set.

Then, you can write a query that uses the values of different cells in its filter. Remember to use single-quotes for identifying strings within the query, and double-quotes to open and close different pieces of the query.

=QUERY(data, "SELECT * WHERE (B = '"&B1&"' AND C = '"&B2&"') OR (C = '"&B1&"' AND B ='"&B2&"')")

Looking for trends

Aggregate functions and filters make powerful tools when used in combination. Once you are comfortable with how they work, you can start searching for all kinds of interesting trends in your data set.

For example, the query below finds the average goals per game, by each year since 1900.

=QUERY(data, "SELECT YEAR(A), AVG(D) + AVG(E) WHERE YEAR(A) >= 1900 GROUP BY YEAR(A)")

If you plot the query result as a line graph, you can immediately start seeing trends over time.

Ordering the results

Sometimes, you are not interested in finding all the matching rows in a data set. Often, you will want to sort them according to some criteria. Perhaps you only wish to find the top ten records.

This query finds the top ten highest scoring matches in the data set.

=QUERY(data, "SELECT * ORDER BY (D+E) DESC LIMIT 10")

Notice the ORDER BY statement. This sorts the rows according to the columns specified. Here, the query sorts the output by the number of goals scored in the game.

The DESC keyword indicates to sort in descending order (the ASC keyword would have sorted them in ascending order).

Finally, the LIMIT keyword restricts the output to a given number of rows (in this case, ten).

Looks like there have been some pretty one-sided games in Oceania!

Which cities have hosted the most World Cup matches?

And now for one final example to bring everything together and get your imagination going.

This query finds the top ten cities that have hosted the most FIFA World Cup matches.

=QUERY(data, "SELECT G, COUNT(A) WHERE F = 'FIFA World Cup' GROUP BY G ORDER BY COUNT(A) DESC LIMIT 10")

Now it's your turn

Hopefully you have found this article useful. If you are comfortable with the logic in each example, then you are ready to try out real SQL.

This will introduce concepts such as JOINS, nested queries and WINDOW functions. When you master these, your power to manipulate data will go through the roof.

There are a number of places to start with learning SQL. Try out the interactive examples at w3schools!

Profiling the successful new coder

During my time in freeCodeCamp, I have read many posts and articles from students without previous coding experience who eventually found jobs as developers.

As a strong supporter of free online self-education platforms, those stories have more than grounded my belief in the effectiveness of this kind of education alternative.

However, only attributing that success to the platforms would give an incomplete picture: part of that success lies on what those students did along with their studies and eventual job hunting.

A quick overview of the stories of new coders finding jobs suggests that they made a diverse range of decisions and took various actions to increase their chances. What were those decisions and actions? In other words, I wondered:

What did they do since starting to learn coding that allowed them to get a job at the end?

For this project, I analyzed the writings of 65 authors found at freeCodeCamp's Forum and on freeCodeCamp's Medium to answer that question, mainly focusing on the freeCodeCamp program.

Here is what I found.

1. Not starting totally from zero

General demographics coincided with what was found in the 2016 and 2017 New Coder Surveys.

• Authors were mostly males.
  Only six of them mentioned their age, which ranged between 20 and 35 years.
• Those who mentioned a background said they had a degree in something and or had been working at some point, either part-time or full-time.
  That suggests that they usually went after a career change due to a newly discovered vocation, because of lifestyle aspirations or because of better salary expectations and job opportunities.
• The change was a risky step for some.
  Some of them already had debts and family.

2. Learning web dev means dealing with complexity

The amount of information around web development is vast, complex and fast-changing. For some of the authors, web development was synonymous with “confusion.”

Some authors argued that the best way to face that complexity was to become strong in the fundamentals: HTML, CSS, vanilla JavaScript, algorithms and probably design. An argument behind that claim was that understanding the basics would help to cope with the difficult parts and give a lot of flexibility.

Several authors also stressed the importance of development environment tools. According to them — the version controllers, editors, testers, package managers, module blunders and other similar tools were as relevant as the fundamentals and should be practiced right from the start.

When going further through the learning path, focusing appeared to be useful.

A large group chose to specialize on either the technologies in the program or on those that better suited the potential job market.

The few generalists saw themselves as adaptable.

Choosing between specialist vs generalist approach (19 authors)

Some authors also wrote about how far to go on exercises. The majority suggested to focus on minimal requirement and good minimum viable products.

“functionality [higher than] structure [higher than] performance” — patricksevat, freeCodeCamp Forum

Those going beyond the requirements likely possessed exceptional problem solving skills and exhibited talents. This was eventually reflected in their portfolios.

3. Commitment above all

Only a few suggested a explicit and thoughtful plan. However, reading carefully through the articles reveals that most of them did follow some sort of plan themselves. That plan usually consisted in setting feasible goals without specifying targets and (hard) deadlines.

For example: goals were like "do networking, visit as many meetups as you can," instead of "visiting X number of meetups and make X number of connections in the next X months." Or "practice algorithms in HackerRank" instead of "complete X number of algorithms and reach an X rank in X days."

A possible reason why the majority planned that way was the uncertainty rooted in self-perceived abilities to cope with tasks with a lot of unknowns.

For many authors, the simplest way to successfully tackle the uncertainty came down to commitment: dedicating hours per day to anything related to the task until reaching a satisfactory outcome. The time of dedication varied, as shown in analyses based on the New Coder Survey.

Times each topic was mentioned in all articles and posts (65); one per reading

Not many emphasized the need for taking breaks: I got the impression that many chose a rather intensive approach. Still there were those who admitted that they included no-coding periods while still learning.

4. Things that (really!) helped them

Several authors stressed the importance of some kind of hands-on experience. I counted 35 unique authors when including all the arguments.

Half of those authors found the curriculum enough as it encouraged a non-prescriptive solution for the projects. It appeared that the more advanced they were in the curriculum, the more likely they believed that the curriculum was enough.

27 unique authors commented about at least one of these topics

Those looking for extra-curricular experience turned into building their own projects, online contributions, and freelancing. People less-advanced in the curriculum seemed to prefer the first option, while those who were more advanced students preferred the second.

“you want to be a software engineer? write code for something…you want to be a growth hacker? growth hack something… a product manager? ship something… just go do it” — rahuldotiyer, Medium

Networking was also a very relevant aspect. Several praised the positive role of having access to an online community. Additionally, those who discovered and practiced face-to-face networking gained substantial rewards in the forms of useful information, mentoring, and even accessing potential jobs or collaborative projects.

Many listed alternative online learning resources. Some of them encouraged others to maintain a strong online presence, or help fellow coders on social platforms to consolidate their knowledge.

5. Becoming trouble shooters

“web development shops aren’t really in the business of coding, they are in the business of solving problems” — kenrogers, Medium

For those with an engineering focus, the quote above might be an obvious one. If your main focus is design, it would be the same: your role would be to come up with aesthetic-functional solutions.

Several authors confirmed that they were preparing themselves to be problem-solvers. Many of them were asked to solve a problem during the interview process. A few also stressed that they had to learn new things in order to solve those problems.

6. Hunting for and getting the job

Applications: targeted or not?

Half of the authors who reported how they got the interviews that led to a job succeeded through less conventional channels that bypassed the HR departments and agencies. They used networking, Craigslist or direct email, for example, as opposed to conventional methods like job boards and recruiters.

21 authors mentioned which channel the got the job from

However, all but five of the reporting authors were apparently in favor of applying to positions through conventional channels.

The portfolio is key

In order to get those interviews, many of the authors confirmed that the portfolio was a very relevant factor.

“you don’t have jack if you don’t have a portfolio” — quesurifn, freeCodeCamp Forum

There were different opinions about what the portfolio should contain. For example:

• One author claimed it should consists of, at a minimum, 3 projects. They should at least show progress, and it’s better if one is non-tutorial based.
• Another author suggested to include only the best you have made.
• There was also one who recommended that new coders redo their portfolio after gaining new skills.

Not only the content of the portfolio but how it was presented appeared to be important. Those emphasizing the engineering of web development were first in taking CodePen projects into a more website-like format.

Such a level of portfolio personalization suggests that the portfolio was used to support the “personal narrative” the applicant wanted to present to potential employers.

What else?

Motivation, commitment, and ability to learn (along with an interest in learning) were personal attributes mentioned many times during the interviews.

[my employer said] they could teach me programming whereas [they] can’t teach the hard work, sacrifice and motivation that I had shown by chasing my dream — rickwest8, Medium

Other aspects that might have played a role during the negotiation were other personal skills, previous experience, and context. It seems that the simpler the portfolio, the more important those aspects.

I didn’t closely go over what they wrote about technical exams, cover letters, and résumés, but I got the impression that several authors assigned more importance to the first two than the last one.

Finally, a job!

The majority of the authors started to look for jobs before completing the Front-end certification. However, this data could suggest that not advancing through the certification could have made it harder to get a job.

That comes from comparing the author's certificates against the proportion of total students getting the different freeCodeCamp certificates.

At the time of graduation, the proportion of students claiming the basic certification was very high compared to those with more advanced certs. But that proportion changed in favor of the authors with more advanced levels when finding jobs. Given the available data, it would be incorrect to reach a conclusion, though.

Apparently the large majority got positions as junior web developers, assuming positions described simply as “developer” were junior positions. I can’t discuss the employer’s actual requirements and what was negotiated, leaving the possibility that similar positions entailed different descriptions.

Data from 60 authors

7. It might take you a year of hard work, but it is doable

Several of the authors admitted that self-studying and then hunting for jobs required a lot of self-motivation. It wasn’t easy, but as many of them pointed out, it was feasible.

“you know your goal , you can make it work” — derozan10, freeCodeCamp Forum

Based on reported dates, the median time between starting to learn to code and finding a job was 320 days. Those who took less were about 120 days with a minimum of 90 days while those who took longer were about 550 days or more.

Conclusion

We should be aware that the characteristics of the data and the implemented methodology might affect the generalization of these results.

Despite that, there are still some take-aways that we can learn from this analysis. However, instead of offering you an opinionated "pill for success," I propose that you use these results as a starting point to find your way.

I invite you then to continue reading similar material as well as asking for advice on social media. Select those solutions that better suit your skills, interests, and situation. You can start by exploring the full list of articles and posts used for this project (thanks, William Pritchard!).

_We prepared a small site to show the posts and articles used in this project; you can follow the link provided ([https://evaristoc.github.io/foundjob/65-new-coders-that-found-jobs/](https://evaristoc.github.io/foundjob/65-new-coders-that-found-jobs/" rel="noopener" target="blank" title="))

To complete this project, we partially searched the forum's posts programmatically, using an authorized scraper made by Neenad Ingole. Toni Shortsleeve helped me in identifying relevant readings on Medium.

The analysis of the content consisted in classical identification of main ideas supported by visual tools (like this one) sketching summarization techniques. You can also find examples of the tools in my GitHub repository.

Happy coding!

When I am not in the DataScience room, I am in the Forum, checking my Twitter, or studying new subjects.