The Prime 3 SQL Expertise Wanted to Get to the Subsequent Spherical | by Andre Violante | Aug, 2023
When you’re aspiring and presently interviewing for roles corresponding to information scientists, information analysts, and information engineers then you might be prone to encounter a number of technical interviews that require dwell coding, often involving SQL. Whereas later interviews may require totally different programming languages like Python, which is widespread within the information area, let’s deal with the everyday SQL questions that I’ve encountered throughout these interviews. For the aim of this dialogue, I’ll assume that you simply’re already accustomed to basic SQL ideas corresponding to SELECT, FROM, WHERE, in addition to combination features like SUM and COUNT. Let’s get into the specifics!
1. Mastering Joins and Desk Sorts
Certainly, the most typical SQL query is round desk joins. It may appear too apparent, however each interview I’ve participated in has centered round this matter. You must really feel comfortable with interior joins and left joins. Moreover, proficiency in dealing with self-joins and unions is efficacious. Equally vital is the flexibility to execute these joins throughout totally different desk sorts, significantly truth and dimension tables. Listed here are my free definitions for these two phrases:
Truth Desk: A desk containing quite a few rows however comparatively few attributes or columns. Think about an instance the place a web-based retailer maintains an “orders” desk with columns like: date, customer_id, order_id, product_id, items, quantity. This desk has few attributes however comprises an enormous quantity of information.
Dimension Desk: A dimensional desk with fewer rows but many attributes. As an illustration, the identical on-line retailer’s “buyer” desk may maintain one row per buyer, that includes attributes corresponding to customer_id, first_name, last_name, ship_street_addr, ship_zip_code and extra.
Understanding these two major desk sorts is vital. It’s essential to understand why and the way to merge truth and dimension tables to make sure correct outcomes. Let’s take into account a real-world instance: the interview query presents two tables (“orders” and “buyer”) and asks:
What number of prospects have bought no less than 3 items of their lifetime and have a transport zip code of 90210?
