DataLemur ?? (Ace the SQL & Data Interview)

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

1. Read Ace The Data Science Interview 2. Practice on DataLemur ?? (Ace the SQL & Data Interview) 3. Land your dream job in Data

99% of competitive companies won't ask you SQL interview questions like “What does GROUP BY do?". Instead, you'll have to write a SQL query which uses?GROUP BY?to solve a real-world problem, like in this Tesla interview question: https://lnkd.in/gdC3PTkX To solve the question, realize that parts that are not yet finished can be found by filtering for rows with no data present in the finish_date column. This can be done using the SQL snippet: WHERE finish_date IS NULL Because some parts might be represented multiple times in the query data because they have several assembly steps that are not yet complete, we can GROUP BY to obtain only the unique parts. Thus, the final answer to this Tesla SQL Interview question is: SELECT part FROM parts_assembly WHERE finish_date IS NULL GROUP BY part; Hopefully, you've understood how just memorizing what WHERE or GROUP BY isn't going to cut it, and that to solve beginner SQL interview questions you still have to creatively apply the basic commands. Practice 201+ real SQL interview questions: https://lnkd.in/gS7nB5Zm

Write a function smallest_multiple to find the smallest number that is perfectly divisible (i.e. no remainder) by all numbers from 1 to the target number. For example, if the target value was 5, we'd return 60, because 60 is evenly divisible by 1, 2, 3, 4, and 5. There is no number smaller than 60 which satisfies this condition. Run your Python code against test cases in the browser: https://lnkd.in/gCCaAcHf Hint: Start with the brute-force solution first. We could just loop through 1 to some arbitrarily large number, and see if it meets the criteria. Hint #2: You probably realize this is wasteful to check EVERY single number. Like no way is an odd number going to work for any target value of 2 or higher... because my definition an odd number won't be evenly divisible by 2. Is there a smarter way to prune the search space?

6,000-Word SQL Interview Guide for Data Analysts & Data Scientists. It Covers: ??Common SQL Commands Used ??SQL Questions from FB, Amazon, etc. ??Join SQL Interview Questions ??Must-Know Window Functions ??Common DB Design Interview Questions ??6-Step Process to Solve Hard Questions ??3 Best Books for SQL Interviews ??Sites to Practice SQL Questions ??How-To Solve Open-Ended Questions ??4 Take-Home SQL Challenges Link: https://lnkd.in/gWSn4pHJ

???????? ???????????? ?????? ?????????????????? ????????????????: Given a reviews table, write a query to retrieve the average star rating for each product, grouped by month. The output should display the month as a numerical value, product ID, and average star rating rounded to two decimal places. Sort the output first by month and then by product ID. ?????????????? ?????????? ??????????????: ?review_id (integer) ?user_id (integer) ?submit_date (datetime) ?product_id (integer) ?stars (integer (1-5)) ???????? ??: ?????????????? The question is asking for the month in the output, but we don't have a month column in the reviews table. So, use a code snippet like this: SELECT EXTRACT(MONTH FROM submit_date) AS mth FROM reviews; ???????? ??: ???????????????? To round the average star rating to 2 decimal places, try this: ROUND(AVG(stars), 2) AS avg_stars To practice this question, and test your solution on the input data, check out the Amazon #SQL interview question on DataLemur: https://lnkd.in/gNqwfMPr

?????? ???????????????? ?????? ???????? ?????????????????? / ???????? ??????????????: 31 interactive SQL lessons lessons for free, featuring every single SQL concept needed for real-world data analytics & data science. Includes 20+ practice exercises including a few Microsoft, Amazon, and Tesla SQL interview questions which you solve directly in the browser - no installation needed. ?? https://lnkd.in/gWRfXkqh

SQL interviews are tricky, but if you approach each question with a structured approach, you’ll ace the SQL interview. Here’s a 6-step framework to solve any SQL interview question: 1. Understand the question 2. Identify Relevant information 3. Break down the problem 4. Consider Edge Cases 5. Write queries to answer sub-problems 6. Test your final query ?????? ?????????????????? ???????? ??: ???????????????????? ?????? ???????????????? Often you might get a long SQL word problem, where you’ll have lots of extra details and it might not be clear what the interviewer is specifically asking you to question. So understanding and clarifying what specifically needs to be done is the best first step to take. ?????? ?????????????????? ???????? ??: ???????????????? ???????????????? ?????????????????????? You might have extraneous columns, or even extra tables that aren’t needed for your SQL query. Interviewers do this on purpose, because in real-world SQL you'll often have thousands of tables, with hundreds of columns, and it's a skill to determine what information you actually need to query. As such, Identify what’s actually needed to directly answer the SQL interview question at-hand. ?????? ?????????????????? ???????? ??: ?????????? ???????? ?????? ?????????????? Often, SQL interviews have a multi-part solution, consisting of multiple joins, unions, subqueries, and CTEs. Map out what are the smaller building blocks that are needed for the final solution. You want to verbalize this step, because it shows the interviewer that's watching you code that you are able to break-down complex problems into simpler sub-problems. ?????? ?????????????????? ???????? ??: ???????????????? ???????? ?????????? You can’t forget edge cases, like if some value is null, or there is a tie in your results set. Make sure to think about this BEFORE you start writing your SQL query. Frequently, SQL interviews will purposely have tricky test cases which catch whether you’ve handled all edge cases. ?????? ?????????????????? ???????? ??: ?????????? ?????????????? ?????? ??????-???????????????? Write queries to answer sub-problems: don’t go after the question all at once. Write small subqueries that answer sub-problems. Test your solutions incrementally, and slowly combine your sub-problem results. I If you try to answer the problem all in one go, your SQL query likely won’t run and you’ll overwhelm yourself trying to figure which of the 27 lines you wrote contains the error. ?????? ?????????????????? ???????? ??: ???????? ???????? ?????????? ?????????? Run your SQL query, and validate that your output matches the expected output. From coaching hundreds of people, you won’t believe how many people?think?they have the final solution, but don’t realize there’s a slight difference between the expected results and what they produced. The end! With these 6-steps, you're ready to tackle the 200+ SQL interview questions on DataLemur!

Can you solve this real Microsoft SQL interview question: “Which Azure customer buys at least 1 Azure product from each product category?” You're given a?customer_contracts?table, which details which companies buy which products, and a table of Azure?products, which has details about what product category each Azure service belongs too. Hints: ? Join the two tables with either a LEFT or INNER JOIN ? Count all of the distinct product categories that a customer has purchased a product from using COUNT(DISTINCT products.product_category) AS unique_count You can explore the input data, get more hints, and test your #SQL query to this problem on DataLemur. Link in the first comment ????

5 REAL TikTok Data Science Interview Questions - how many can you answer? ??. ?????????? ?? ?????? ?????????? ???? ???????? ?????? ???????????????????? ???????? ???? ???????????? ??????????. Assume you're given tables with information about TikTok user sign-ups and when they confirmed/verified their account via email and/or text. Write a SQL query to display the user IDs of those who did not confirm their sign-up on the first day, but confirmed on the second day. Full problem + dataset here: https://lnkd.in/eAxTiCtG ??. ?????????????? ?????? ????????-???????????????? ????????????????. Here, the interviewer is asking you to explain the classic?bias-variance tradeoff that you face when building ML models. You’ll be asked to give a concrete example, and also connect this tradeoff to the concept of overfitting. ??. ???????? ???? ?????????? ?? ???????????????????? ??/?? ???????? ?????? ???????????????? ???? ?????? ????????. This question is trying to dive into your past Data?Science experience, and gauge your Product Data?Science skills. Follow on questions you'll get asked: * How did you know to run an A/B test for this product/feature? * What was the business impact from the A/B test? * Did you run into any issues with user-imbalance? ??.??????????? ?? ?????????? ???? ?????????????? ?????? ?????? ???? ???????????? ?????????? ?????? ?????????????????? ???? ?????? ???????????? ??????. New TikTok users sign up with their emails. They confirmed their signup by replying to the text confirmation to activate their accounts. Users may receive multiple text messages for account confirmation until they have confirmed their new account. Write a query to find the activation rate for a given list of users. https://lnkd.in/e5JtvaKi ??.?????????'?? ?????? ???????????????????? ?????????????? ?????????????????????? ?????? ????-????????????????? Besides answering the question, be ready to answer this follow-up question: "How does changing the scale of variables affect correlation and covariance?". If you said it affects covariance, but has no effect on correlation, well done! How many of these problems could you solve? Comment below! p.s. if you want to quiz yourself on more real Data Science interview questions, read Ace the Data Science Interview: https://amzn.to/3kF79Fx p.p.s. more SQL/Coding questions available online which you can code directly in the browser on DataLemur: https://lnkd.in/esz2SY6Y