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
DataLemur ๐ (Ace the SQL & Data Interview)
Information Services
San Francisco, California 41,908 followers
Practice real FAANG SQL & Data Interview questions! Made by Nick Singh, Best-Selling Author of Ace the Data Interview!
About us
Hi, I'm Nick Singh, the creator of DataLemur! I used to write SQL queries and run A/B tests all day at Facebook, and before that, wrote Python on Google Nest's Data Infrastructure Team. I know first-hand how difficult Data Science, Data Analyst, & Data Engineering interviews can be. While my best-selling book, Ace the Data Science Interview, has successfully helped 16,000+ readers prepare for the Statistics, ML, and Business-Sense portions of data interviews, readers kept asking for a more interactive way to practice the SQL questions from the book. That's why I made DataLemur, a SQL & Data Analytics interview platform for the data community! Happy practicing: https://datalemur.com/
- Website
-
http://datalemur.com/
External link for DataLemur ๐ (Ace the SQL & Data Interview)
- Industry
- Information Services
- Company size
- 2-10 employees
- Headquarters
- San Francisco, California
- Type
- Privately Held
Locations
-
Primary
San Francisco, California 94110, US
Employees at DataLemur ๐ (Ace the SQL & Data Interview)
-
Nick Singh ๐๐
Nick Singh ๐๐ is an Influencer Founder: DataLemur.com (SQL Interview Prep) โข Author: Ace the Data Science Interview โข Ex-Facebook โข 160K+ follow me on LinkedIn for Data/SQL/Careerโฆ
-
Puja Agnihotri
Business Analyst | Ex-Protiviti Tech Consultant | Virginia Tech Alumni
-
Debasmita Dey
SQL Consultant
-
Siddhant Sahu
--
Updates
-
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
-
-
DataLemur ๐ (Ace the SQL & Data Interview) reposted this
Day 22 out of 50 Starting the journey of sharing #interview questions . Company: Amazon Level: Hard Source: DataLemur ๐ (Ace the SQL & Data Interview) Language/Tool: SQL . Problem Statement: Amazon Web Services (AWS) is powered by fleets of servers. Senior management has requested data-driven solutions to optimize server usage. Write a query that calculates the total time that the fleet of servers was running. The output should be in units of full days. . https://lnkd.in/gKHwwhbT . Here's my approach, what would be yours? . #python #interview #meta #fang #datascience #sql #easy #dailychallenge #50daysofsolving #facebook #MYSQL #HACKERRANK
-
-
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!
-
-
Data Science interviews cover WAY more than just SQL. You can expect technical questions on: โขProbability & Statistics โขMachine Learning โขData Structures & Algorithms โขData Manipulation w/Pandas โขProduct-Sense โขA/B Testing To help you prep, here's a FREE 9-day Data Interview Crash Course: https://lnkd.in/gYzWZGXQ
-