This week's discussion...
A SQL crash course: the easiest way to gain an edge as a generalist in tech
Learning at The Commons
2022 cohorts are closed but our waitlist is open! Join to get front-line access to 2023 Sprints.
If someone forwarded this to you, subscribe here.
Your Weekly Level Up
🔥 Hot topic: The Tech Discovery Summit
✏️ Career Resource: Interview Advice from The Community
✅ Community Reccos: Portable monitors
💭 Thought Starter: Twitter vs....?
💡 Brain Teaser: 🔢
🚀 Community Share: Meetups Around The Globe
THE COMMONS SPOTLIGHT
A SQL crash course: the easiest way to gain an edge as a generalist in tech
Written by Sean Newman, mentor at The Commons.
To get ahead, generalists need to learn to code. We’ve all heard this mantra in one form or another. The thing is, as a generalist, it’s less about technical expertise, and more about application. This means SQL is by far the language that will yield the best results for those in BizOps, Strategic Finance, Product, and Growth Marketing. Python is probably not going to help you in your day job as much.
Learning SQL and understanding its application is the quickest way to differentiate yourself from your peers. So what do I mean by application? Application means pulling together the right data (and summarizing it!) in such a way that you can make a strategic decision. Change the trajectory of your career by learning about different paths in tech directly from mentors who work at Google, Wealthsimple, PartnerStack, Astranis, Dolly, Juniper, and Uber.
See what happened? This dramatically simplified example was possible because an all-star analyst did the following:
- Defined the problem: Orders take too much time. He needed to find just how many minutes per order per courier.
- Defined potential variables: Date, Time of Day, Rating, and Different Legs of the Trip are all important areas to explore
- Wrote the code: We’ll dive into this shortly
- Analyzed the results: The leg of the trip waiting at the restaurant is far too long!
Now, the manager and his analyst can brainstorm together why couriers wait so long at restaurants and what solutions they have to offer. This is all because the analyst not only knew how to write SQL (step 3), but also how to apply it (steps 1, 2, and 4). We’re going to look at everything you need to know for steps 3. But to take things to the next level, I recommend also finding opportunities at your job or in The Commons to work on real-world examples!
WHAT IS SQL?
Let’s first establish a few definitions…
- Query = SQL code
- Raw Data = An output table containing information specified by a query
- Table = Database objects containing rows and columns of information
- Schema: A collection of tables
- Database: A collection of schemas
It’s important to understand how tables, schemas, and databases fit into an ecosystem. This will help you combine different datasets into the most meaningful raw data. Within schemas, each table is typically defined as “dim” or “fct”.
- Dim stands for dimension. This is a static mapping table such as dim_quantities. It tells information about data. For example, dim_quantites may say the size of each package. Strawberries are 1.5 lbs, Oranges are 0.75 lbs, etc.
- Fct stands for fact. These tables update in real time with activity such as purchases and inventory movement. For example, fct_groceries may say the time when each fruit was purchased by a customer
THE BASICS:
Every SQL statement should include the following attributes.
- SELECT… determines which columns to include. This example has * which means every column in the table will be pulled in
- FROM… determines which table to pull from. This example is pulling from a table called fct_groceries
- WHERE… determines how data should be filtered. This query specifies only the year 2022 and onward. It also excludes any row that is in a category outside of Fruits.
The output of this query is as follows. Since we selected * in the query above, it is showing us all of the data (both columns + rows) from the table.
Now, let’s build upon this query more…
We did a few new things here:
- DATE_TRUNC… instead of individual days, we’ve bucketed the data into months (we always use the 1st of the month to do this). For example, 5/8/22 is now 5/1/22, or ‘2022-05-01’
- COUNT… Each row counts as one. This is counting the rows within the unique combination of month_sold, category, and item. In layman's terms, this will tell us the total number of items sold each month.
- SUM… this is summing the revenue for each sale within month_sold, category, and item. Similar to the above, this will tell us the total revenue by item, per month.
- ORDER BY… this is always optional. ORDER BY sorts your data. In this example, the data is being sorted by month_sold, then by category, then by item in ascending order. By default, ORDER BY sorts the data in ascending order. To sort the data in descending order, use DESC - eg. ORDER BY [field names] DESC.
- GROUP BY… when aggregations such as count or sum are included, “group by” always must be added at the end of the query. It’s very similar to the logic of a pivot table in Excel. The example below shows this. Notice how the two dark-yellow rows contain the same month, category, and item. They are combined and quantity_sold = 1 + 1, revenue = 3 + 3. When doing a GROUP BY, you can either number the columns chronologically as 1,2,3, or you can write out the column names as month, category, item.
Check out the new data set
If you’ve made it to this point, you now know enough SQL to be dangerous. Now, we’re going to go through other functions that are used on a daily basis by generalists making an impact at their companies!
Want access to the full article? Get free access here.
Want hands on experience building SQL skills? Get on the waitlist for the January Strategy & Operations Sprint.
Join the Waitlist
What’s happening at The Commons
Upcoming events
IRL Meetups!
With the help of our Geo-Captains we are taking URL to IRL to a whole new level this month. With London, San Francisco, Montreal, Vancouver and Calgary in the books, you can look forward to upcoming meetups in Toronto, New York and Chicago!
Multiple Dates | IRL!
Community + limited spots for non-community members
Community members register here
Non-community members register here
No-Code/Low-Code Showcase
Being able to improve and automate processes is a true super power for any operator! Join mentor Sarah Zarling (Senior Product Operations Manager, Color) for this session focused on expanding your No-Code/Low-Code toolkit, and how to apply these solutions to your day-to-day.
November 23, 8PM ET
Community only
Register here
Weathering Tough Times & Job Search Chat
Tackling a job search or dealing with change at work isn't easy. Tack on the recent headlines and it feels even heavier. That's why we're bringing together three expert community voices. Join mentors Shane P. (Head of Ops @ KOHO), Rohan S. (Strategic Ops Manager @ Super) and Saumil S. (Growth & Chief of Staff at SafeGraph) for a chat on weathering tough times at work and in the job search.
December 1, 7PM ET | Virtual
Community only
Register here
The Teams in Tech Summit
Join us for a super practical way to gain exposure to the tech industry and build skills to help you nail an interview and deliver impact quickly on the job. More info below - workshops include a Teams in Tech Panel, Data Viz Workshop, and Tackling a Case Interview.
December 3+4, ~11AM-3PM ET
All are welcome!
Non-community members register here
Community members register here
Past events
ICYMI we recently hosted these events. Members, you can find relevant recordings on our platform.
- IRL Hangs: Community members from San Francisco, London, Montreal, Calgary and Vancouver met up over beers and sushi.
- Mentor Office Hours, November | On Monday, two of our incredible mentors, Alex (GM at Instawork) and Chris (Revenue Advisor and Fractional CRO), hosted Office Hours covering everything from breaking into tech, to perfecting your GTM strategy.
- Acing the Recruiter Screen | Two community members, Marlies (Tech Recruiter @ Pinterest) & Shaurya (Talent Management @ Wealthsimple), with extensive experience in recruiting, shared helpful tips on how to stand out during a crucial phase of the interview process: the recruiter screen.
- Automating Workflows with Alexis from Spotify | Alexis, a BizOps Tech & Automation Manager at Spotify, kicked off a three-part series on automating workflows 🤖 Part 1 covered advanced Google Sheets formulas to take your Sheets game to the next level. Stay tuned for subsequent sessions on automated trackers, emails and building your own Slackbot 👀
Application Alert: Join the Waitlist
Looking to join The Commons? Your way in is through an application. We're currently closed to new members, but we'll be re-opening seats soon! Apply to join the waitlist this week. We'll be reviewing applications in the order of the waitlist.
Join the Waitlist 2023
We'll be kicking off the following Sprints in January 2023:
- Strategy & Operations
- Strategic Finance
- Product
- Revenue Growth
💥 JOIN THE WAITLIST 💥
Learn more // Join the Waitlist
SOLD OUT SPRINTS
All 2022 Sprints are now closed!
Join the Waitlist
HOT TOPIC - The Tech Discovery Summit
Are you on the hunt for a new role? Change the trajectory of your career by learning about different paths in tech directly from mentors who work at Google, Wealthsimple, PartnerStack, Astranis, Dolly, Juniper, and Uber.
💥 Discounted pricing available until EOD Friday. Secure your spot for just $30 (a 40% discount off the regular price!). 💥
Over the course of a weekend, you'll dive into the following sessions:
💼 Business Teams in Tech: a 360 view of all of the typical business, non-technical teams at a Tech company including: Product, Strategy & Operations, Business Development and Partnerships, Chief of Staff, Strategic Finance and more. Mentors in each role will walk you through what their team does, a typical day-to-day and project they work on.
🤿 Office Hours - Dive Deeper: You'll select two roles you're interested in and join those mentors in office hours for candid Q&A sessions filled with insight and advice.
📈 Data Viz & Dashboarding: An introductory workshop on how to build strong data visualizations, a key skill for take home cases and on the job! We’ll dive into how data viz fits into dashboards - an operator's snapshot on the health of their projects and initiatives.
🧠 Tackling a Case Interview: Join two of our mentors for an in depth look at how they tackle an ambiguous project, like one you'd see on the job or in a take-home case. Walk away with best practices to apply in your job search or a new role.
The Details
⏰ December 3-4
📍All live and online
👋 For ambitious professionals who are exploring a new role in the tech industry. We built this to make your journey easier and more successful. The Summit is open to everyone - you don't need to be a member of The Commons!
Register (Limited Time: 40% Savings)
Member? Enroll for free here
Your weekly level up
Interview Advice Straight From The Community
When community members face unique challenges during the hiring process, they turn to The Commons for advice....
Here's a round up of advice from members of The Commons:
- Try standard email formats: "That is rough! Recruiter laid off may not be a good sign for new open roles. Do you remember the Hiring Manager's name? Use the same email format and email them. Or reach out on Linkedin? Reaching them directly is your best bet..."
- Don't delay! "Something very similar happened to me for a job I was super excited about and I waited too long to follow up - I ended up doing what Sandeep mentioned above (structured an email using same email format with their name) to reach out to the hiring manager after a lot of time had gone by and found out that the recruiter went out on emergency leave so my candidacy slipped through the cracks despite the hiring manager actually intending to move me forward. If you don’t hear back via email I’d follow up via LinkedIn too. Happy to share how I structured the email if helpful (I tried to be as non awkward as possible!) - feel free to DM 😊"
- Ask the Recruiter: "How’d you find out they were laid off? If you are directly in touch, you can ask them for the name of the hiring manager and/or email address."
- Find another Recruiter: "+1 to all of the above. You can also go on linkedIn and try to find the last standing recruiter at the company and see if they can help."
Add to the thread here.
Portable Monitors 🖥
With remote work on the rise, more people are looking for tools to suit their lifestyle - like portable monitors.
Here's what the community recommended ⬇️
- ThinkVision M14 Portable Monitor - Ayo
- TRIO & TRIP Max Laptop Monitor - Fairuz "Really handy for travelling and is 50% off atm for Black Friday"
- ASUS Zenscreen - Michael M. "I use ths one with. my MacBook, works great.
- espresso - Michael M. "I've also been eyeing this company"
- Viewsonic VA1655 - Balil "I use a Viewsonic VA1655, but have also considered the ASUS one above. How much you spend depends on your use case, but since mine is mainly remote work while traveling, I went with a 1080p screen, and have no complains so far."
Have more suggestions? Share them with Raza in this thread.
Twitter vs....?
With all of the buzz around Twitter lately, community members have been chatting about where people will flock to next. What platforms have you checked out? Add your thoughts to the thread ⬇️
What number should replace the question mark?
Check out the answer here.
IRL Meetups Around The Globe 🌎
ICYMI, the #gratitude channel has been bursting with ~good vibes~ 🤗
Check out the threads here.
.svg){kind=small}
