DataLemur 🐒 (Ace the SQL & Data Interview)’s Post

SQL Interviews LOVE to test you on Window Functions. Here’s the 7 most popular window functions & some real SQL interview questions to practice these commands 👇 𝟕 𝐌𝐨𝐬𝐭 𝐓𝐞𝐬𝐭𝐞𝐝 𝐖𝐢𝐧𝐝𝐨𝐰 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬 * RANK() - gives a rank to each row in a partition based on a specified column or value * DENSE_RANK() - gives a rank to each row, but DOESN'T skip rank values * ROW_NUMBER() - gives a unique integer to each row in a partition based on the order of the rows * LEAD() - retrieves a value from a subsequent row in a partition based on a specified column or expression * LAG() - retrieves a value from a previous row in a partition based on a specified column or expression * NTH_VALUE() - retrieves the nth value in a partition Now, let’s put these commands into practice: 𝐔𝐛𝐞𝐫 𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧 Uses Row_Number() to find the 3rd ride booked: https://lnkd.in/gf4UDx4d 𝐆𝐨𝐨𝐠𝐥𝐞 𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧 Uses Row_Number() to find odd & even measurements from a sensor: https://lnkd.in/gBUCxxih 𝐒𝐩𝐨𝐭𝐢𝐟𝐲 𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧 Uses DENSE_RANK() to find the top 5 artists on Spotify: https://lnkd.in/gDJ_paEY 𝐖𝐚𝐲𝐟𝐚𝐢𝐫 𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐐𝐮𝐞𝐬𝐭𝐢𝐨𝐧 Uses LAG() to find the Year-over-Year Growth: https://lnkd.in/g2WAe2BK

Anyone else find debugging SQL a pain?

Let’s solve this Google SQL Interview Question together: Assume you're given a table with measurement values obtained from a Google sensor over multiple days with measurements taken multiple times within each day. Write a query to calculate the sum of odd-numbered and even-numbered measurements separately for a particular day and display the results in two different columns. 𝐈𝐧𝐩𝐮𝐭 𝐦𝐞𝐚𝐬𝐮𝐫𝐞𝐦𝐞𝐧𝐭𝐬 𝐓𝐚𝐛𝐥𝐞: measurement_id | integer measurement_value | decimal measurement_time | datetime Before we start solving this problem, why don’t you give it a try too? You don’t need to login, or install anything, just directly run the query in the browser against the measurements table: https://lnkd.in/gBUCxxih 𝐒𝐭𝐞𝐩 𝟏: 𝐎𝐫𝐝𝐞𝐫𝐢𝐧𝐠 𝐚𝐧𝐝 𝐏𝐚𝐫𝐭𝐢𝐭𝐢𝐨𝐧𝐢𝐧𝐠 We first order the measurements based on their measurement time and partition them by day using the ROW_NUMBER window function. This helps us establish the order of measurements within each day: SELECT CAST(measurement_time AS DATE) AS measurement_day, measurement_value, ROW_NUMBER() OVER ( PARTITION BY CAST(measurement_time AS DATE) ORDER BY measurement_time) AS measurement_num FROM measurements; It is important to use measurement_time in the ORDER BY clause of the window function to ensure that the measurements are ordered within each day based on the actual measurement's time. This will ensure that the row numbering (measurement_num) is accurate and reflects the chronological order of the measurements within each day. 𝐒𝐭𝐞𝐩 𝟐 & 𝟑: 𝐅𝐢𝐥𝐭𝐞𝐫𝐢𝐧𝐠 𝐚𝐧𝐝 𝐒𝐮𝐦𝐦𝐢𝐧𝐠 To filter for odd and even numbers, we can use the Modulus operator (%)! Use measurement_num % 2 != 0 to check if the result is 1, indicating odd numbers or measurement_num % 2 = 0 with a result of 1 for even numbers. We can apply the modulus concept to the aggregate function SUM() along with the FILTER clause, summing over the corresponding measurement_value, which gives us the final solution 𝐓𝐡𝐞 𝐅𝐢𝐧𝐚𝐥 𝐒𝐨𝐥𝐮𝐭𝐢𝐨𝐧: WITH ranked_measurements AS ( SELECT CAST(measurement_time AS DATE) AS measurement_day, measurement_value, ROW_NUMBER() OVER ( PARTITION BY CAST(measurement_time AS DATE) ORDER BY measurement_time) AS measurement_num FROM measurements ) SELECT measurement_day, SUM(measurement_value) FILTER (WHERE measurement_num % 2 != 0) AS odd_sum, SUM(measurement_value) FILTER (WHERE measurement_num % 2 = 0) AS even_sum FROM ranked_measurements GROUP BY measurement_day; If you want to solve more FAANG SQL interview questions, try these problems: https://lnkd.in/gRtyY3Ht

The Data Science/ML gospel is now in PYTHON 🐍 and they made the book FREE: https://lnkd.in/gYzzturP

Learning SQL? Here's 3 mistakes beginners learning SQL need to avoid: 🛑 Mistake 1: Trying to Learn Everything SQL As a beginner, you can skip DDL (CREATE, ALTER, DROP, etc.), DCL (GRANT, REVOKE, etc.) and just focus on SELECT. Basic outline of SQL topics every Data Analyst & Data Scientist needs to know here: https://lnkd.in/e5j7fZpH 🛑 Mistake 2: Building on Weak Foundations Don't move onto HAVING if you struggle with GROUP BY. Don't learn UNION if you still don't understand JOINs. People are too quick to move onto new commands, because mastering the basics isn't sexy 🙅♂️ 🛑 Mistake 3: Not Practicing Enough There's a HUGE difference between reading how a SQL command works vs. using it to solve a business problem. For practice, try answering 200+ real-world scenario SQL questions directly in the browser: What mistakes did you make when starting to learn SQL? https://lnkd.in/egwVjb5K

PSA for aspiring Data Scientists, Data Analysts, and Data Engineers: You can love SQL. Or you can hate SQL. But you can’t get away with avoiding SQL!

Want to learn SQL? This free online tutorial covers the most important SQL commands & functions you need to know: https://lnkd.in/e3cw69HZ

4 SQL Games that will take your SQL skills to the next level 👇👇👇 𝟏. 𝐒𝐐𝐋 𝐌𝐮𝐫𝐝𝐞𝐫 𝐌𝐲𝐬𝐭𝐞𝐫𝐲 A murder has happened in SQL city! Can you use your SQL knowledge to find the killer? 𝟐. 𝐒𝐐𝐋 𝐏𝐨𝐥𝐢𝐜𝐞 𝐃𝐞𝐩𝐚𝐫𝐭𝐦𝐞𝐧𝐭 Join SQLPD, and solve crimes while learning SQL in the process! 𝟑. 𝐒𝐜𝐡𝐞𝐦𝐚𝐯𝐞𝐫𝐬𝐞 This space-based strategy game implemented entirely within a PostgreSQL database. Compete against other players using raw SQL commands to command your fleet! 𝟒. 𝐒𝐐𝐋 𝐈𝐬𝐥𝐚𝐧𝐝 In this adventure game, you're stranded on SQL island after a tragic plane crash, and have to use your SQL skills to find a way to escape the island. Links to each game in this blog post: https://lnkd.in/gUnfa4Kx 𝐁𝐨𝐧𝐮𝐬 𝐑𝐞𝐬𝐨𝐮𝐫𝐜𝐞: 𝐃𝐚𝐭𝐚𝐋𝐞𝐦𝐮𝐫 This interactive SQL interview platform is a simple & fun way to practice solving realistic #SQL problems you'll encounter if you're hunting for a new Data Science/Data Analyst gig!