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 42,137 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
-
Have an upcoming SQL interview, and need to cram? Here's what to study, depending on if you've only got a few hours or a few days to prep: ๐๐ก๐๐ญ ๐๐จ ๐๐ซ๐๐ฆ ๐๐ ๐๐จ๐ฎ๐ซ ๐๐๐ ๐๐ง๐ญ๐๐ซ๐ฏ๐ข๐๐ฐ ๐๐ฌ๐ฌ๐๐ฌ๐ฌ๐ฆ๐๐ง๐ญ ๐๐ฌ ๐๐ง ๐ ๐ ๐๐ฐ ๐๐จ๐ฎ๐ซ๐ฌ If youโve only got a few hours to study for a SQL interview, and donโt know SQL already, start learning SQL basics with SQLBolt and cram as many lessons while praying to the god of your choice. If you do know some SQL, skip the tutorials. Donโt focus on learning any more new SQL commands because youโll only confuse yourself! Instead, the highest ROI on your time is reviewing the list of basic SQL commands used in interviews like: โขSELECT โขFROM โขWHERE โขGROUP BY โขHAVING โขORDER BY โขLIMIT You can also review common aggregate functions used in SQL interviews like: โขAVG() โขCOUNT() โขMAX() โขMIN() โขSUM() ๐๐ก๐๐ญ ๐๐จ ๐๐๐ฏ๐ข๐๐ฐ ๐๐ ๐๐จ๐ฎ๐ซ ๐๐๐ ๐๐ง๐ญ๐๐ซ๐ฏ๐ข๐๐ฐ ๐๐๐ฌ๐ญ ๐๐ฌ ๐๐ง ๐ ๐ ๐๐ฐ ๐๐๐ฒ๐ฌ If you've got a few days to prepare for a SQL test, you should cram by: โขspending day 1 reading the ultimate SQL interview guide: https://lnkd.in/gWSn4pHJ โขspending day 2 reviewing commands and applying them on real SQL interview questions: https://lnkd.in/gRtyY3Ht โขspending day 3 identifying your weaknesses, and only practicing questions on those topics. I've seen many people be weak on SQL joins, SQL window functions, and blank on how to do date-time operations (like extract the year from a date), but your own personal weaknesses seriously can only be know by actually doing some practice questions so def don't skip that part. For more details, check out this blog on cramming for #SQL interviews: https://lnkd.in/dhCNKdtG
-
-
Google's search results for "SQL Interview Questions" are TERRIBLE, and mislead thousands of Data Analysts & Data Scientists into studying the WRONG THINGS. Here's what Google is getting wrong, what companies ~actually~ ask in SQL interviews, and how to best prepare for these SQL assessments. โ ๐๐ก๐๐ญ ๐๐จ๐จ๐ ๐ฅ๐'๐ฌ ๐๐๐ญ๐ญ๐ข๐ง๐ ๐๐ซ๐จ๐ง๐ : Every blog on the first page of Google claims that popular interview questions are: โข What is a Database? โข What does SQL stand for? โข What is a query? Absolute ๐ฉ... this isn't what any proper data job interview is going to ask! ๐๐๐ซ๐'๐ฌ ๐ฐ๐ก๐๐ญ ๐๐๐ ๐๐ง๐ญ๐๐ซ๐ฏ๐ข๐๐ฐ๐ฌ ๐๐๐ญ๐ฎ๐๐ฅ๐ฅ๐ฒ ๐๐ฌ๐ค (๐๐จ๐จ๐ค๐ฆ๐๐ซ๐ค ๐ญ๐ก๐ข๐ฌ): They'll ask you to whiteboard, orally describe, or in a shared code editor write a SQL query to solve a particular question like: โข"I have a table of e-commerce sales. What's the average sales for each month?" โข"I have a table of employee salary data. How do I find the highest paid person in each department?" โขโI have a table of products and orders. Whatโs the 7-day rolling average for the number of orders placed for product XYZ?โ These questions test your ability to translate a business question into SQL, usually under time pressure. The best way to ace these types of questions is to practice hundreds of REAL SQL interview questions from tech companies like Google, Amazon, Meta, etc.: ๐ https://lnkd.in/gS7nB5Zm To solve these questions, youโll end up using these 20 commands a ton: 1 - WHERE 2 - AND 3 - OR 4 - NOT 5 - LIKE 6 - GROUP BY 7 - MIN 8 - MAX 9 - COUNT 10 - SUM 11 - AVG 12 - INNER JOIN 13 - LEFT JOIN 14 - RIGHT JOIN 15 - FULL OUTER JOIN 16 - UNION 17 - RANK 18 - LEAD 19 - LAG 20 - ROW NUMBER If you donโt know what these commands do, take that as a sign to work on your SQL foundations. Hereโs a 30-day โLearn SQL for Freeโ roadmap: ๐ https://lnkd.in/ggf-7rvf ๐๐จ๐ง๐๐๐ฉ๐ญ๐ฎ๐๐ฅ ๐๐ฎ๐๐ฌ๐ญ๐ข๐จ๐ง๐ฌ You might also be asked conceptual questions, about joins, database design, troubleshooting slow queries, and more. Examples include: * Whatโs the difference between WHERE vs. HAVING? * How does an inner, left, and outer join differ from one another? * Iโm designing a ride-sharing app like Uber. What are some tables Iโd create, and how do they related to each other?โ Studying for these questions are a bit harder, but I wrote this 6,000 word SQL interview guide which covers what books & resources to read to learn this foundational knowledge, along with the most popular conceptual questions you'll face: ๐ https://lnkd.in/gWSn4pHJ Any SQL interview questions you've got for me? If you've done a SQL interview before, anything I missed?
-
-
You wouldn't expense a $500 dinner to your company, right? Yet many managers have no problem getting 12 people into a 30-min meeting to discuss some useless thing, costing the company THOUSANDS in wasted productivity. That's why Shopify built a tool that displays the cost of each meeting right in the calendar invite - pure genius ๐๐๐
-
-
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
-