SQL | Data Analytics

Average time to advance to the next career level in #analytics

💡Why complicate dates when YYYY-MM-DD exists? ISO 8601 is the best date format for your #data, Here's why? - Unambiguous: No confusion between MM/DD and DD/MM. - Sortable: Dates are in chronological order by default. - Programming-friendly: Easily parsed by most systems and databases. - Consistent length: Always 10 characters. - Internationally recognized: A global standard used across systems. - Dash-separated: Works better with file systems and URLs than slashes 👇Do you agree? Which date format do you use? #analytics #dataanalyst

It's true =)

💡 Understanding NULLIF in SQL In SQL, the NULLIF function is a handy tool that simplifies comparisons and helps prevent errors when working with null values. But what does it actually do? `NULLIF` Syntax:

NULLIF(expression1, expression2)

The function compares two expressions: - If expression1 is equal to expression2, it returns NULL. - If they are not equal, expression1 is returned. 📍Why use NULLIF? One common scenario is avoiding division by zero errors. For example, instead of a typical divide-by-zero error:

SELECT 100 / NULLIF(some_column, 0) FROM table;

Here, if some_column equals 0, NULLIF will return NULL, preventing the error and returning a null value instead of crashing the query. 🚀 This simple function keeps your SQL code clean and prevents those pesky runtime errors. Next time you’re handling potential nulls or divide-by-zero scenarios, give NULLIF a try! #SQL #DataTips #NULLIF #SQLTricks #Database

What type of joins did you use?

🚀 Popular #SQL Challenges You Should Know! 📍1. How to Find the Second Highest Value in a Column Need to find the second highest salary?

SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

📍2. How to Find the N-th Highest Salary in a Table Want to find the N-th highest salary? Just order the salaries in descending order and use LIMIT to pick the exact one you need. For example, to find the 3rd highest salary:

SELECT salary FROM employees ORDER BY salary DESC LIMIT 2,1;

Ultimate Analytics Solution Mindmap Guide: Unlock Your Data Career! https://www.youtube.com/watch?v=JnbFfr0kQCo #dataanalyst #analytics #sql

🚀 PostgreSQL: The SPLIT_PART() Function — Effortless String Splitting! Need to quickly extract a part of a string separated by a specific delimiter? The SPLIT_PART() function in PostgreSQL has got you covered! 🎯 🔹 How does it work? Syntax:

SPLIT_PART(string, delimiter, field)

- `string` — the string you want to split. - `delimiter` — the character that separates the parts. - `field` — the index of the part you want to extract (starts at 1). 📌 Example: Extract parts from a comma-separated list:

SELECT SPLIT_PART('apple,banana,cherry', ',', 2); -- returns 'banana'

📌 Real-life Use Cases: 1️⃣ Email Parsing: Extract username or domain from an email address.

SELECT SPLIT_PART('user@domain.com', '@', 1); -- 'user'

2️⃣ URL Parsing: Get the domain from a URL.

SELECT SPLIT_PART('https://example.com/path', '/', 3); -- 'example.com'

Simple, powerful, and saves time! 🔥 #PostgreSQL #DatabaseTips #SQL

If you’ve ever worked with SQL, even just a little, you’ll know that NULL is a truly unique value. Unlike anything else, it doesn’t represent an empty string '' or the number 0, but rather the absence of a value. Here are some fun quirks about NULL: 🔹 You can't check if NULL is in a list: NULL IN (NULL) will return NULL, not TRUE or FALSE. 🔹 Neither NULL = NULL nor NULL <> NULL will return TRUE or FALSE—both are considered invalid. 🔹 COUNT(column) only counts non-NULL values, while COUNT(*) or COUNT(1) counts all rows, including those with NULLs. 🔹 Aggregate functions like SUM, MIN, MAX, and AVG ignore NULL values entirely. NULL isn’t the smallest value—it’s simply ignored. 🔹 When sorting in ascending order, ORDER BY will put NULLs first by default. 🔹 Since NULL is not equal to any other value (not even to another NULL), joins won't match rows with NULL values. 🎯How to handle NULLs in SQL: 🔹 Use x IS NULL or x IS NOT NULL to check for NULL values. 🔹 COALESCE: Returns the first non-NULL value in a list. 🔹 IFNULL/ISNULL: Provides a backup value if NULL is encountered. 🔹 NULLIF: Turns a specific value into NULL when needed. Working with NULL in #SQL is all about understanding that it’s not a value—it’s the lack of one! #dataanalyst #analyst #data

TOP-23 SQL Interview Questions & Answers Link 📌COMMON SQL INTERVIEW QUESTIONS TO PREPARE FOR: Q1. Tell me about yourself and why you want this position? Q2. What is SQL? Q3. Why do you want to work for our company in this SQL position? Q4. What is MySQL? Q5. What’s the main difference between SQL and MySQL? Q6. In SQL, what are ‘JOINS’? Q7. What is an INDEX, and why is it useful to have? Q8. What personality will you bring to the team? Q9. If a ‘constraint’ is added in SQL, what does this mean? Q10. What are the more common types of SQL constraint and what do they mean? Q11. So far, you have referred to TABLES and FIELDS in your answers. What are they? Q12. What’s your biggest weakness? Q13. Tell me what the different subsets of SQL are? Q14. It’s 5pm on a Friday and you receive a request from a stakeholder who says it’s urgent. You assess the task and it will take approximately one hour to complete. What would you do? Q15. How would you format SQL server dates? Q16. What is primary and foreign key? Q17. Why do you want to leave your current job? Q18. What is database denormalization? Q19. What is database normalization? Q20. What are your salary expectations in this SQL position? Q21. In SQL, what is a subquery? Q22. What happens to the data rows in a table when the table contains a clustered index? Q23. That’s the end of your SQL interview. Do you have any questions for the panel?

🎓 Difference Between IFNULL and COALESCE in SQL If you’ve worked with missing values in a database, you’ve probably come across #IFNULL and #COALESCE. At first glance, they seem to do the same thing, but let’s break down their differences. 👇 💡 IFNULL takes two arguments. If the first argument is #NULL, it returns the second. If the first argument is not NULL, it returns the first value. 📌 Example:

IFNULL(salary, 0) — returns 0 if salary is NULL, otherwise returns salary.

💡 COALESCE is more powerful. It takes multiple arguments and returns the first non-`NULL` value in the list. 📌 Example:

COALESCE(salary, bonus, 0) — returns the first non-`NULL` value starting with salary. If all are NULL, it returns 0.

🔍 Key Differences: - IFNULL only works with two arguments. - COALESCE can handle multiple arguments and returns the first non-`NULL` one. #sql #database #dataanalyst

How calculate the MEDIAN in the BigQuery There is no Median function in the BigQuery. We can to calculate median using the PERCENTILE_CONT(x, 0.5) or PERCENTILE_DISC(x, 0.5) functions. Link

🔥 ETL vs ELT: What's the Difference? When it comes to data processing, two key approaches stand out: ETL and ELT. Both involve transforming data, but the processes differ significantly! 🔹 ETL (Extract, Transform, Load) - Extract data from various sources (databases, APIs, etc.) - Transform data before loading it into the storage (cleaning, aggregating, formatting) - Load the transformed data into the data warehouse (DWH) ✏️ Key point: Data is transformed before being loaded into the storage. 🔹 ELT (Extract, Load, Transform) - Extract data from sources - Load raw data into the data warehouse - Transform the data after it's loaded, using the power of the data warehouse’s computational resources ✏️ Key point: Data is loaded into the storage first, and transformation happens afterward. 🎯 When to use which? - ETL is ideal for structured data and traditional systems where pre-processing is crucial. - ELT is better suited for handling large volumes of data in modern cloud-based architectures. Which one works best for your project? 🤔

📍Get prepared for your #SQL interview with this mock interview video. Practice your technical and HR interview skills to land your dream job!

Data analytics:

⚠ This roadmap aims to give a complete picture of the modern #data #engineering landscape and serve as a study guide for aspiring data engineers. This roadmap include free resources, books and courses for Data Engineering! 📍 Link - https://awesomedataengineering.com/

📊 LEAD and LAG Functions in SQL: A Comprehensive Guide with Examples 💡 The LEAD and LAG #functions in SQL are powerful tools for data analysis, allowing you to access data from subsequent or previous rows without the need for complex #joins. These functions are particularly useful for trend analysis, time series analysis, and a variety of other tasks. In this article, we’ll explore how the LEAD and LAG functions work using #SQL code examples and explain how they can simplify your data queries. ⚠What are LEAD and LAG Functions? #LEAD retrieves data from the next row in the result set, while #LAG fetches data from the previous row. Both functions use the OVER clause to define the window or set of rows over which they operate. 📍Syntax:

LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column) LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)

📍Using LEAD Function The LEAD function is used to access data from subsequent rows. Let’s calculate the next day’s sales amount for each row.

SELECT id, sale_date, amount, LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_day_amount FROM sales;

📍Calculating Daily Sales Difference Using both LEAD and LAG functions, you can calculate the difference in sales amounts between consecutive days.

SELECT id, sale_date, amount, LEAD(amount, 1) OVER (ORDER BY sale_date) - amount AS next_day_difference, amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS previous_day_difference FROM sales;

🎓 Popular SQL challenges you should know! 🔻How to Find Employees Earning Above the Average Salary

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

🔻How to Find Salaries with Their Rank

SELECT salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

🔻How to Find the Highest Salary in Each Department

SELECT department_id, MAX(salary) AS highest_salary FROM employees GROUP BY department_id;