Acing Twitch's SQL screen

***About the Author: *Thomas is currently a Data Science Manager at Twitch. His team supports analytics for product teams working on viewer-facing and personalization products, such as the signup flow, the channel page, search, and recommendations.

Interviewing for a Data Scientist role at Twitch? Awesome! We work with a lot of data – as in petabytes of data. While exciting, efficiently navigating a vast database like ours (which is continually growing!) means that you’ll need an excellent understanding of Structured Query Language (SQL) in order to quickly access the information you need. Because fluency in SQL is key to success in Data Science roles, we have candidates complete a technical assessment that gauges their SQL skills. Technical Assessments are typically given at the midway point of the interview process, after the recruiter and hiring manager phone screens, and before the final onsite interviews.

We want you to bring your best self to the SQL assessment, so we’ve put together this guide to help you know what to expect and how to study, so you can ace it!

Learning and Practicing

First things first, if you’d like to learn SQL or just brush up on your skills, Mode has a great hands-on SQL tutorial. There are plenty of other intro classes at w3schools, SQLZOO, and Codecademy.

For practice, Mode has an exercise, with solutions, related to a search product, and a free, public dataset (Mode Public Warehouse) that can be queried for free. Leetcode and HackerRank both have a few questions that will test your knowledge of SQL – medium/hard on Leetcode is a great bar to aim for.

Background on our SQL Tools

Twitch handles a huge amount of data that rarely fits in memory, so we typically work in a database called Redshift, whose dialect derives from Postgres. Essentially, an Amazon Redshift data warehouse is a collection of computing resources called nodes, which are organized into a group called a cluster. Each cluster runs an Amazon Redshift engine and contains one or more databases. We run SQL queries on giant Redshift clusters to aggregate data before we can visualize it. However, for your test you can use any SQL dialect you’re comfortable with.

The SQL Screen

Now for the moment you’ve been waiting for: the SQL screen!

The SQL screen takes place remotely in a virtual coding environment. The interviewer provides instructions and walks candidates through the questions one by one. For training purposes, there will sometimes be two interviewers – one conducts the interview as usual, and the other, usually a trainee, listens and remains in the background.

What do we Test?

The assessment questions will test you on core SQL concepts like inner/left join, union, distinct, group by, string manipulation, timestamp transformation, window functions, and case statements. While your interviewer won’t expect you to add comments to each line of code, you should use line breaks and indent for visibility. When applicable, organize your code into separate subqueries (also known as CTEs).

How Should You Answer?

When answering a question, correctness beats efficiency and elegance. Don’t feel pressured to answer right away; take the time to think about a question before answering it. If it helps, think out loud so the interviewer can follow along and step in to help. If that is too distracting, it’s OK to talk through the solution after having taken a stab at it. Again, the most important thing is that we understand your technical decisions, so make sure to communicate them clearly rather than quickly generating code without explanation.  

As we addressed above, Twitch Data Scientists primarily work with a database called Redshift, whose dialect derives from Postgres. You can use any SQL dialect you’re comfortable with during the screening. If the interviewer is not familiar with parts of the dialect you’ve used, they will ask for clarifications. 

Navigating Ambiguity

If something is unclear, make sure to ask your interviewer. For example, if you have specific questions about functions, such as “what is the name and signature of the function truncating a timestamp to a day?,” ask and your interviewer can answer them. Likewise if you’re unfamiliar with a certain syntax or function, being transparent and asking the interviewer beats searching for it suspiciously in the background.

We hope this helps you prepare for the technical phone screen, and we’re all looking forward to chatting with you!

Author’s Note: Thanks to Edith Ho, Ryan Pires, and Angelo Paxinos for reviewing and editing this article.

Want to Join Our Quest to empower live communities on the internet? Find out more about what it is like to work at Twitch on our Career Site,  LinkedIn, and Instagram, or check out our Job Openings and apply.