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

Can you solve this Microsoft Python Coding Interview Question? You know about factorials right... like how 5! is 5 * 4 * 3 * 2 * 1 = 120. ?????????? ?? ???????????????? ???????? ?????????????? ?????? ???????????? ???? ???????????????? ???????????? ???? ??! For example, for 5! we’d return 1 because 120 has exactly 1 trailing zero. For 10! = 3,628,800 we'd return 2 trailing zeroes. Here's a link to code-up & test your Python solution: https://lnkd.in/ex8nFkmk p.s. the brute-force solution involves computing the factorial, and then counting the number of zeroes. Can you think of a way to solve this WITHOUT computing the actual factorial?

Find practicing SQL boring? Maybe try these 4 fun SQL learning games: https://lnkd.in/gUnfa4Kx They are definitely on the easier side, so if your looking for a challenge, after mastering the basics with these games, give the hard SQL interview questions on DataLemur(dot)com a try!

Can you solve this Microsoft SQL Interview question about Microsoft Teams? Write a query to identify the top 2 Power Users who sent the highest number of messages on Microsoft Teams in August 2022. Display the IDs of these 2 users along with the total number of messages they sent. Output the results in descending order based on the count of the messages. Test & run your SQL query for free here, and also get the full solution to this problem: https://lnkd.in/gkM3AmQX Comment your version of the solution below – curious to see your take:

Your first attempt learning ML will be bad. Your first attempt writing SQL will be bad. Your first attempt using data-viz tools will be bad. Your first attempt in anything data related will be bad. But you can’t make your 10th, 50th, or 100th without making your first. So don’t worry about it – just do it!

Because data jobs are highly competitive, companies often use tough SQL interview questions to filter candidates. To succeed, Nick Singh says it’s essential to practice SQL skills beforehand, in case your interview features something like a timed assessment. For more of this course on LinkedIn Learning, check out the link below: https://lnkd.in/e72zWptq #InterviewPreparation

Want to Ace the Amazon SQL Interview? Here's 4 REAL Amazon SQL Interview Questions: ??. ?????????? ?? ?????? ?????????? ???? ?????? ?????? ?????????????? ???????????? ?????????????? ?????? ?????????? ?????????????? ?????????? ??????????. Given the reviews table, write a query to get the average stars for each product every month. The output should include the month in numerical value, product id, and average star rating rounded to two decimal places. Sort the output based on month followed by the product id. Write SQL code interactively to solve this question here: https://lnkd.in/gNqwfMPr ??. ???????????? ?????????????????? ?????? ????????. ?????? ???? ?????? ???????????????? ?? ???????? ?????? ??????????? ?SELECT fields instead of using SELECT * ?Avoid SELECT DISTINCT ?Create joins with INNER JOIN (not WHERE) ?Avoid JOINs in general (maybe try de-normalization) ?Add indexes to your database ?Examine the SQL query execution plan Note: the interviewer will likely push you for more detail, or ask you about a real example about a time you had to make one of these optimizations yourself. While this question might be out-of-scope for Data Analysts and Data Scientists, Amazon expects people interviewing for Data Engineering and Business Intelligence roles to know how databases work internally, and best practices for database design. If you aren't familiar with these concepts, check out my article on how to prep for database design interviews: https://lnkd.in/gbNNr_vR ??. ?????????? ?? ?????? ?????????? ???? ???????? ?????? ??????????????-???????????????? ??????????. Assume you are given the table containing information on Amazon customers and their spending on products in various categories. Identify the top two highest-grossing products within each category in 2022. Output the category, product, and total spend. Write SQL code interactively to solve this question here: https://lnkd.in/gu2DES78 ??. ????????'?? ?????? ???????????????????? ?????????????? ????????() ?????? ??????????_????????()? Essentially RANK is to SELECT what dense_rank() is to SELECT DISTINCT. RANK() gives you the ranking within your ordered partition. Ties have the same rank, with the next ranking(s) skipped. So, if you have 4 items at rank 2, the next rank listed would be ranked 6. DENSE_RANK() also ranks within your ordered partition, BUT the ranks are consecutive. This means no ranks are skipped if there are ranks with multiple items, and the rank order depends on your ORDER BY clause.

Can you solve this REAL Twitter / X SQL Interview Question? Assume you're given a table Twitter tweet data. Just 4 simple columns in a table called tweets: tweet_id (integer) user_id (integer) msg (string) tweet_date (timestamp) ?????????? ?? ?????????? ???? ???????????? ?? ?????????????????? ???? ???????????? ???????????? ?????? ???????? ???? ????????. Output the tweet count per user as the bucket and the number of Twitter users who fall into that bucket. Here's a link to write a SQL query & run it directly in the browser – please try this out yourself first before seeing our solution: https://lnkd.in/gAZF38if Here's how I'd solve it: First, we need to find the number of tweets posted by each user in 2022 by grouping the tweet records by user ID and counting the tweets. SELECT user_id, COUNT(tweet_id) AS tweet_count_per_user FROM tweets WHERE tweet_date BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY user_id; Next, we use the query above as a subquery, then we use the tweet_count_per_user field as the tweet bucket and retrieve the number of users. SELECT tweet_count_per_user AS tweet_bucket, COUNT(user_id) AS users_num FROM ( SELECT user_id, COUNT(tweet_id) AS tweet_count_per_user FROM tweets WHERE tweet_date BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY user_id) AS total_tweets GROUP BY tweet_count_per_user; What do you think of this solution? How did you solve it? Use a CTE?

SQL + Python + Stats = ??????

Stripe’s valued at $50+ billion – can you solve a real SQL Interview Question they asked in a job interview? ??????????????: For context, payment transactions are repeated by accident; it could be due to user error, API failure or a retry error that causes a credit card to be charged twice. Using the transactions table, write a SQL query to identify any payments made at the same merchant with the same credit card for the same amount within 10 minutes of each other. Count such repeated payments. Link to the full problem + dataset + interactive environment to run your SQL query: https://lnkd.in/gwfRakVt ????????: You’ll want to use a LAG window function, to see if two similar looking transactions happened within 10 minutes of each other. Need a tutorial/refresher on LAG? Check this out: https://lnkd.in/gTbXF2PH Comment your solution below if you were able to solve it!