Yaroslav Pelykh’s Post

🌟 𝐃𝐚𝐲 𝟗 of 𝐒𝐐𝐋 𝐉𝐨𝐮𝐫𝐧𝐞𝐲: 𝐄𝐱𝐩𝐥𝐨𝐫𝐢𝐧𝐠 𝐃𝐚𝐭𝐞 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬! 🌟 Today, I focused on the essential date functions in SQL, which are vital for manipulating and analyzing date and time data effectively. 📅✨ 🔍 𝐖𝐡𝐚𝐭 𝐈 𝐂𝐨𝐯𝐞𝐫𝐞𝐝: In my latest presentation, I delved into various date functions, such as GETDATE(), DATEADD(), DATEDIFF(), and more. Each function has unique applications that enhance data analysis capabilities.   💡  𝐖𝐡𝐲 𝐈𝐭 𝐌𝐚𝐭𝐭𝐞𝐫𝐬: Mastering these date functions enables us to handle time-based data accurately, ensuring reliable analysis and reporting. These tools are crucial for tasks like calculating durations, summarizing monthly trends, and tracking user activity. 🚀 I’m excited to continue my SQL journey and explore even more functionalities! If you have insights or tips about using date functions effectively, feel free to share! Also, I came across a fantastic YouTube video by Ankit Bansal that explains all these date functions in detail which has been incredibly helpful!. Do check it out here: https://lnkd.in/gGJ8qjTA Check out my PPT for detailed explanations of each function! #𝐒𝐐𝐋𝐉𝐨𝐮𝐫𝐧𝐞𝐲 #𝐃𝐚𝐭𝐚𝐀𝐧𝐚𝐥𝐲𝐬𝐢𝐬 #𝐒𝐐𝐋 #𝐃𝐚𝐭𝐞𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬 #𝐋𝐞𝐚𝐫𝐧𝐢𝐧𝐠𝐉𝐨𝐮𝐫𝐧𝐞𝐲 #𝐂𝐨𝐝𝐞𝐛𝐚𝐬𝐢𝐜𝐬 #𝐍𝐚𝐦𝐚𝐬𝐭𝐞𝐒𝐐𝐋 #𝐃𝐚𝐭𝐚𝐒𝐞𝐧𝐬𝐞

What is the difference between COUNT(*) and COUNT(1) in SQL? Let’s break it down 👇 → 𝗪𝗵𝗮𝘁 𝘁𝗵𝗲𝘆 𝗱𝗼 - COUNT(*): Counts all the rows in a table, regardless of NULL values. - COUNT(1): Also counts all rows. The 1 is just a placeholder constant. → 𝗪𝗵𝗮𝘁 𝗿𝘂𝗻𝘀 𝗳𝗮𝘀𝘁𝗲𝗿 - COUNT(1) might be slightly faster, but the difference is almost unnoticeable. → 𝗪𝗵𝗮𝘁 𝗿𝗲𝗮𝗱𝘀 𝗯𝗲𝘁𝘁𝗲𝗿 - COUNT(*) is clear and straightforward. - COUNT(1) might be less immediately obvious. 𝗜 𝗮𝗺 𝗧𝗘𝗔𝗠 𝗖𝗢𝗨𝗡𝗧(*)! Because it's more clear and straightforward! What is your team? Comment below! 👇 ------------------------------------------------------- Click 🔔 on my profile to get notified on all my new posts.

Dear Excel Santa 🎅, Here's what I would love to see: - in-cell usable lambda functions (see image) - support for typed arrays of functions i.e. so we can type {SUM, MIN, MAX} instead of HSTACK(SUM, MIN, MAX) - support for function equality operations i.e. so that LAMBDA(x, SUM(x)) = SUM returns TRUE - a SIGNATURE() function that returns a rich object describing a function signature, so we can ensure that a function expects the same number of arguments I'm going to provide it, that also supports equality operations. SILLYEXAMPLE = LAMBDA(x, y, fn, IF( SIGNATURE(fn) = SIGNATURE(LAMBDA(x, y, "anything"), fn(x, y), "Signature of provided function is incorrect" ) ); - support for partial application. Suppose I have a function like this: TRIVIALEXAMPLE = LAMBDA(multiplier, array, array * multiplier); Then partial application would allow this: =BYROW(AnArray, TRIVIALEXAMPLE(3)) So that we provide the first n arguments, and missing arguments are passed by the calling function. In this case, missing argument is "array", and it's passed by BYROW as the array of values for a row. Owen --- ❓ What's in your letter to Excel Santa for 2024? #data #analytics #excel #lambda

Ever wondered when to use RANK(), ROW_NUMBER(), or DENSE_RANK() in your SQL queries? 🤔 These window functions may look similar, but they shine in different scenarios. Let’s simplify: 💡 RANK() Assigns ranks with gaps for ties. Perfect for cases where ties matter. 👉 Example: Ranking students by scores. If two students tie for first place, they both get a rank of 1, and the next rank is 3. 💡 ROW_NUMBER() Assigns a unique sequence to every row, no ties allowed. 👉 Example: Listing rows in the order of entry. Even if rows are identical, each gets a unique number. Great for de-duping datasets! 💡 DENSE_RANK() Assigns ranks without skipping numbers for ties. 👉 Example: Ranking products by sales. If two products tie for first place, the next rank is 2 (no gaps!). Practical Tip: Choose based on your ranking logic. Need to account for ties but keep sequential ranks? Go for DENSE_RANK(). Want to break ties? Stick with ROW_NUMBER(). What’s your favorite window function for ranking? Let’s discuss below! 👇 #SQLTips #DataAnalytics #SQLQueries #LearningSQL #DataEngineering

Hello busy people, Let's talk about a hidden gem in SQL: the QUALIFY keyword! 👀 What is QUALIFY? QUALIFY filters results of window functions directly, making your queries cleaner and more readable. Example: Imagine you need to find the most recent purchase for each customer. Here’s how QUALIFY can help: SELECT customer_id, purchase_date, purchase_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) as purchase_rank FROM purchases QUALIFY purchase_rank = 1; Why Use QUALIFY? - Simplifies queries by keeping window function logic and filtering together - Reduces the need for complex subqueries See you in the next one! #SQL #DataAnalysis #BigData #DataScience #SQLTips #DataAnalytics #QUALIFY

Write an SQL query to find the ctr of each Ad Round ctr to 2 decimal points. Order the result table by ctr in descending order and by ad_id in ascending order in case of a tie.. - Create table CREATE TABLE ad_actions ( ad_id INT, user_id INT, action VARCHAR(50) ); -- Insert rows INSERT INTO ad_actions (ad_id, user_id, action) VALUES (1, 1, 'Clicked'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (2, 2, 'Clicked'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (3, 3, 'Viewed'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (5, 5, 'Ignored'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (1, 7, 'Ignored'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (2, 7, 'Viewed'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (3, 5, 'Clicked'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (1, 4, 'Viewed'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (2, 11, 'Viewed'); INSERT INTO ad_actions (ad_id, user_id, action) VALUES (1, 2, 'Clicked'); with cte1 as ( select ad_id,sum (case when action in ('Clicked') then 1 else 0 end) as clicked from ad_actions group by ad_id ) ,cte2 as( select ad_id , sum(case when action in('Clicked','Viewed') then 1 else 0 end ) as total from ad_actions group by ad_id ) select a.ad_id, coalesce(round((clicked +0.0)/nullif((total +0.0),0)*100,2),0) as ctr from cte1 a join cte2 b on a.ad_id = b.ad_id order by a.ad_id asc,ctr desc ;

Day 20/30 of My SQL Challenge: Intermediate SQL with Window Functions & Ranking 📊🏆 Today’s challenge was all about diving deeper into window functions and ranking—key techniques for performing complex calculations across data sets without altering the results of a query. 💻 Today's Tasks: 1. Ranking Products by Total Sales – I used the RANK() window function to rank products based on total sales. This is a great way to quickly see which products are performing best. 2. Calculating a Running Total of Orders – Next, I applied the SUM() window function to calculate the running total of orders across a period, providing insights into sales trends over time. 💬 Window functions are incredibly powerful for advanced analytics—whether you’re ranking data or calculating running totals, they let you perform complex calculations while maintaining visibility into the entire dataset. #SQL #DataAnalytics #WindowFunctions #IntermediateSQL #Ranking #RunningTotal #SQLChallenge #LearningJourney #TechJourney #DataScience #AdvancedQueries

🚀 Mastering SQL: The Power of Window Functions and CTEs! Recently, I’ve been diving deep into SQL and discovered how window functions and Common Table Expressions (CTEs) can make querying data so much more efficient and insightful. Here’s a quick example of their combined power: Scenario: Imagine you’re analyzing customer bookings and want to identify the top 3 cities per country based on booking volume. Sounds tricky? Let SQL handle it! WITH BookingRank AS ( SELECT country, city, COUNT(*) AS booking_count, RANK() OVER (PARTITION BY country ORDER BY COUNT(*) DESC) AS rank FROM bookings GROUP BY country, city ) SELECT * FROM BookingRank WHERE rank <= 3; Breakdown: 🔹 CTE (BookingRank): Simplifies the query by organizing intermediate results. 🔹 Window Function (RANK): Dynamically ranks cities within each country based on booking counts. 🔹 Output: Only the top 3 cities per country. This approach keeps your query clean and helps you focus on the insights without over-complicating the logic. If you’re exploring SQL or working on analytics, understanding these tools can save you hours and unlock actionable insights. 💡 Pro Tip: Practice is key. Start applying these in your daily analysis tasks to get comfortable. What’s your go-to SQL trick for solving complex queries? Let’s share and learn together! #DataAnalytics #SQL #Learning #DataInsights

What is the difference between INNER JOIN and OUTER JOIN? ------------------------------------------------------------- INNER JOIN: Returns: Only rows with matching values in both tables. Use When: You need data that exists in both tables. Example: SELECT * FROM A INNER JOIN B ON A.id = B.id; OUTER JOIN: ------------ Types: ------------- LEFT JOIN: Returns all rows from the left table, and matched rows from the right table. Unmatched rows from the right table result in NULL values. RIGHT JOIN: Returns all rows from the right table, and matched rows from the left table. Unmatched rows from the left table result in NULL values. FULL JOIN: Returns all rows when there's a match in either table. Unmatched rows from either side will have NULL values. Use When: You need all data from one or both tables, regardless of matches. Example: SELECT * FROM A LEFT JOIN B ON A.id = B.id; #SQL #SQLInterviewQuestions

Today, I will be sharing some powerful SQL tools—Window Functions and Common Table Expressions (CTEs)—and they’re complete game-changers! Here’s why I’m excited: 📌 Window Functions: These allow you to perform calculations across sets of rows, making it easier to do things like ranking, running totals, and averages. They’re perfect for deeper insights into your data. 📌 CTEs (Common Table Expressions): It simplify complex queries by letting you build them step-by-step, making the data easier to understand and work with. These tools help you analyze data more deeply, answer tough questions, and get insights you might’ve missed before. Have you explored these techniques yet? Let’s talk about it below!