Aditya Sharma’s Post

DAY-256 OF SQL =========== RANKING FUNCTION/WINDOW FUNCTION: In SQL, ranking functions are window functions that assign a rank to each row based on a specified order. Common ones are ROW_NUMBER() (unique sequential numbers), RANK() (same rank for ties, skips next ranks), and DENSE_RANK() (same rank for ties, no skips). Using the OVER() clause, you can rank rows within partitions or the whole dataset, useful for top performers, pagination, or finding duplicates.

Today's class on my SQL I learned how to create a table by using the CREATE TABLE statement. with some basic syntax, CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, column3 datatype constraints ); I was told to take important Notes of the following; 1. Always select a database first  2. Common Data Types 3. Avoid common mistakes among the table created today where: * Customers table * Order_details table * Orders table * Products table * Suppliers table Below is the screenshot of my work today.

Day 26/90 — SQL Series | Week 4 "My WHERE clause is not filtering dates correctly." "My SUM is returning NULL instead of a number." 9 times out of 10 — it's a data type mismatch. CAST and CONVERT fix it. CAST('250' AS INT) → turns text into number so you can do math CAST('2024-01-15' AS DATE) → turns text into date so filters work CAST(order_id AS VARCHAR) → turns number into text for concatenation Rule: use CAST (works everywhere). Use CONVERT only when you need date formatting in SQL Server. #SQL #CastConvert #DataAnalytics #LearnSQL #DataAnalyst

Mastering SQL Window Functions: Cumulative Sum Made Simple Problem: Given a table of purchases, calculate the cumulative quantity purchased for each product type over time, ordered by date. Logic (Step-by-Step): → Use a window function (SUM OVER) to avoid grouping rows. → Apply PARTITION BY product_type to calculate separately for each product. → Use ORDER BY order_date to ensure chronological accumulation. → The sum keeps adding previous values → giving a running total (cumulative sum). #SQL #DataAnalytics #DataScience #LearningInPublic #WindowFunctions #100DaysOfCode

Question: How can I capitalize the first letter of all values in a column and also combine two columns (name and surname) into one column in SQL? For example: mohan + singh → Mohan Singh amit + sharma → Amit Sharma priya + gupta → Priya Gupta What is the best way to achieve this using SQL functions? Kindly share your answer in the comments.

Scared of SQL? I recommend you to check just these 25 Commands: 1. SELECT 2. LIMIT 3. AS 4. SELECT DISTINCT 5. COUNT 6. MIN 7. MAX 8. SUM 9. AVERAGE 10. WHERE 11. HAVING 12. AND 13. OR 14. BETWEEN 15. IN 16. LIKE 17. GROUP BY 18. ORDER BY 19. UPDATE 20. ALTER TABLE 22. INSERT INTO 23. INNER JOIN 24. LEFT JOIN 25. RIGHT JOIN #sql #tips #juniordataanalyst #internanalyst #databaseanalyst

I rewrote a SQL query 3 times before I finally understood what it was actually doing. It was supposed to calculate average goals per league (Aug, 2013/14 season) but I kept getting stuck in nested subqueries and unnecessary id IN (...) filters. Some key lessons I learned: → 𝗕𝗿𝗲𝗮𝗸 𝗽𝗿𝗼𝗯𝗹𝗲𝗺𝘀 𝗶𝗻𝘁𝗼 𝗹𝗮𝘆𝗲𝗿𝘀 Filter → Transform → Join → Aggregate → 𝗖𝗧𝗘𝘀 𝗮𝗿𝗲 𝗳𝗼𝗿 𝗰𝗹𝗮𝗿𝗶𝘁𝘆, 𝗻𝗼𝘁 𝗰𝗼𝗺𝗽𝗹𝗲𝘅𝗶𝘁𝘆 Instead of messy nested queries, structure the logic so you can understand it later. → 𝗡𝗼𝘁 𝗲𝘃𝗲𝗿𝘆 𝘀𝘂𝗯𝗾𝘂𝗲𝗿𝘆 𝗶𝘀 𝗻𝗲𝗰𝗲𝘀𝘀𝗮𝗿𝘆 Why filter the same table with a subquery when a simple WHERE season = ... AND EXTRACT(MONTH...) = ... does the job? → 𝗔𝗹𝘄𝗮𝘆𝘀 𝗸𝗲𝗲𝗽 𝘁𝗵𝗲 𝗷𝗼𝗶𝗻 𝗸𝗲𝘆 If your CTE drops the linking column, the whole query breaks later. → 𝗔𝗩𝗚 𝗶𝘀 𝗷𝘂𝘀𝘁 𝗦𝗨𝗠/𝗖𝗢𝗨𝗡𝗧 Once you see that, debugging becomes much easier. Don’t just write queries. Design the data flow. #DataAnalytics #SQL 

Day 19/90 — SQL Series | Week 3: Functions "Find all customers whose name starts with R." "Find all emails from Gmail." "Find all products with 'shirt' somewhere in the name." All 3 solved with LIKE + wildcards. % → any number of characters (zero or more) _ → exactly one character WHERE name LIKE 'R%' → starts with R (Rahul, Rohan, Riya) WHERE email LIKE '%@gmail.com' → ends with @gmail.com WHERE product LIKE '%shirt%' → 'shirt' anywhere in the name WHERE code LIKE 'A_01' → A then any 1 char then 01 (e.g. AB01, AC01) LIKE is case-insensitive in SQL Server by default. Combine with NOT LIKE to exclude patterns. #SQL #LIKEOperator #DataAnalytics #LearnSQL #DataAnalyst

𝐒𝐐𝐋 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐂𝐡𝐚𝐥𝐥𝐞𝐧𝐠𝐞 | 𝐋𝐞𝐯𝐞𝐥: 𝐀𝐝𝐯𝐚𝐧𝐜𝐞𝐝 𝐂𝐡𝐚𝐥𝐥𝐞𝐧𝐠𝐞: Convert each row into multiple rows based on value Do it using pure SQL Avoid using loops (think set-based 💡) 𝐖𝐡𝐚𝐭 𝐭𝐡𝐢𝐬 𝐭𝐞𝐬𝐭𝐬: Recursive thinking Set-based SQL approach Understanding of row expansion Advanced SQL concepts (CTE / generate_series / window tricks) Drop your solution in the comments! P.S - Get create and insert statement in the first comment.

An interesting problem posted by Tyagi, which can be solved by using Recursive logic. Do check the comment to see my simple solution.