Mock Interview | SQL + BigQuery | Episode 11 | Surfalytics
This video features a mock SQL interview hosted by Nikita, with a special focus on BigQuery syntax. The session is designed for an experienced candidate and includes three relatively complex tasks that test logical thinking and knowledge of specific BigQuery functions. The interview covers a range of practical SQL challenges, including: Ranking Data: The first task involves ranking customer orders, where the candidate uses the DENSE_RANK() window function. This is followed by a discussion of QUALIFY, a clause supported by BigQuery and Snowflake that offers an alternative to CTEs or subqueries for this type of problem. Aggregating with Arrays and Joins: The second task requires creating an ordered array of event descriptions from the most recent session using ARRAY_AGG(). It also involves calculating an engagement score by joining multiple tables and using a CASE WHEN statement within a SUM function to conditionally count events. Handling Nested JSON Data: The final task focuses on flattening a nested reviews array using UNNEST(). The candidate then extracts data from the resulting JSON objects using JSON_EXTRACT_SCALAR(). The interviewer notes that BigQuery now recommends newer functions like JSON_VALUE() or JSON_QUERY() over this legacy function. Following the coding session, the conversation shifts to BigQuery query optimization techniques: The importance of early filtering to reduce the amount of data processed in joins. Utilizing partitioning for low-cardinality columns and clustering for high-cardinality columns to increase query speed and reduce cost. Leveraging BigQuery's columnar storage by specifying only the necessary columns in a SELECT statement to avoid costly full table scans. The video concludes with constructive feedback for the candidate, highlighting the importance of using CTEs for readability over subqueries and explaining one's thought process before starting to code. Timecode: Thank you for watching our video "Mock Interview | SQL + BigQuery | Episode 11 | Surfalytics" on Surfalytics_TV. Click the link to watch more mock interviews: https://www.youtube.com/watch?v=qUwbpgbEymE&list=PLNCDg7zJiXhPD0ethFHC-1V1pkvWhc633&pp=iAQB Subscribe for channel for more videos: https://www.youtube.com/@SurfalyticsTV?sub_confirmation=1 #sql #sqlinterview #bigquery #dataanalytics #technicalinterview #codinginterview #livecoding #interviewprep #dataengineering #queryoptimization #gcp #datanalyst ================= What is Surfalytics? Inspired by West Coast surfing spots 🏖️ and Pacific Ocean vibes 🌊. Created to help you start a new career in the data analytics space, and develop data engineering and analytics skills through coaching. It will teach you not just dry skills, but will keep your focus on delivering significant value to businesses in the analytics realm as well as help to get fair compensation 💰 for the work you’re passionate about ❤️🔥. The goal of Surfalytics is to assist you in achieving one of the following: 🏄♂️ Land your first job in the data industry with literally zero experience. I have accomplished this many times across the globe. 🏄 Advance from a middle-level role to a senior position (as an Analyst or Engineer). 🏄♀️ Transition from a non-technical Analyst role to a technical Engineer role. Moreover, we will focus on creating a highly competitive CV and securing top job offers. We will not consider any lowball offers, focusing only on top-tier companies and well-paid opportunities. Finally, Surfalytics is a results-driven community with a very narrow focus, resulting in a high return on investment (ROI). Here, ‘investment’ does not mean money but your time. I am literally fighting for your attention to encourage you to study and work hard, instead of watching Netflix or playing video games. This is the best YouTube channel for Data Analytics and Engineering. You will patch up a lack of knowledge and get new experience or you build a Data Analyst roadmap or Data Engineer roadmap for yourself. Want to be part of our growing community? Site: https://surfalytics.com/ Blog: https://blog.surfalytics.com/ #surfalytics #nikitavolynets #sql #mockinterview #technicalskills #cte #windowfunctions #database