SQL | Data Analytics

🔥 A reminder that ANY_VALUE is a pretty interesting aggregation function in #SQL It gives you a chosen row from a group. Chosen doesn't mean random, but non-deterministic. Together with HAVING MAX | MIN you can actually control what rows get picked. While ANY_VALUE works both with GROUP BY and as a window function OVER (PARTITION BY...), the window variety does not yet support HAVING MIN MAX. Otherwise, when do I use it? A couple of cases, and it's not only for the thrill of getting an item by chance from the group: 🔻 line events also contain header info, so say we need to extract order header data from orderline data 🔻aggregation after pseudo-pivoting with CASE WHEN value = x, same as we used to do with MIN or MAX before 🔻 other aggregations of string values based on a rule

🗺 Geography Functions in SQL: Quick Guide with Examples SQL makes it easy to handle geographic data. Here are key functions with examples: 1. `ST_Distance(geo1, geo2)` Calculates the distance between two points in meters.

   SELECT ST_Distance(
     geography::STGeomFromText('POINT(-73.935242 40.730610)', 4326), 
     geography::STGeomFromText('POINT(-118.243683 34.052235)', 4326)
   ) AS distance_in_meters; -- Distance from NYC to LA
   

2. `ST_Intersects(geo1, geo2)` Checks if two geographic objects intersect.

   SELECT ST_Intersects(
     geography::STGeomFromText('POLYGON((-73.97 40.75, -73.87 40.75, -73.87 40.85, -73.97 40.85, -73.97 40.75))', 4326), 
     geography::STGeomFromText('POINT(-73.935242 40.730610)', 4326)
   ) AS intersects; -- Does NYC point intersect with the polygon?
   

3. `ST_Area(geo)` Calculates the area of a polygon.

   SELECT ST_Area(
     geography::STGeomFromText('POLYGON((-73.97 40.75, -73.87 40.75, -73.87 40.85, -73.97 40.85, -73.97 40.75))', 4326)
   ) AS area_in_square_meters; -- Area of a NYC region
   

4. `ST_Within(geo1, geo2)` Checks if one object is inside another.

   SELECT ST_Within(
     geography::STGeomFromText('POINT(-73.935242 40.730610)', 4326), 
     geography::STGeomFromText('POLYGON((-73.97 40.75, -73.87 40.75, -73.87 40.85, -73.97 40.85, -73.97 40.75))', 4326)
   ) AS is_within; -- Is NYC point inside the polygon?
   

5. `ST_Buffer(geo, radius)` Creates a buffer zone around a point or shape.

   SELECT ST_Buffer(
     geography::STGeomFromText('POINT(-73.935242 40.730610)', 4326), 
     1000
   ) AS buffer_area; -- 1km buffer around NYC
   

Pro Tips: - Use spatial indexes for faster queries. - Simplify large polygons for better performance. These functions are perfect for mapping, logistics, and spatial analysis in SQL!🌐

It's true 🤪

📍Understanding Indexes in SQL: A Quick Guide Indexes in SQL are a powerful feature that can significantly improve the performance of your database queries. Think of an index as a roadmap that helps SQL quickly locate the data you need, without having to scan every row in a table. Here’s how they work, why they matter, and some examples. ✨What is an Index? An index is a special data structure (usually a B-tree) that the database uses to speed up the retrieval of rows. When you create an index on a column (or multiple columns), the database builds a structure that allows it to find the data much faster than searching the entire table. ✨ Why Use Indexes? Indexes drastically improve query performance, especially in large tables. Without an index, a query like:

SELECT * FROM Orders WHERE OrderID = 102;

would require SQL to examine every row in the Orders table. With an index on the OrderID column, SQL can jump directly to the relevant row, making the query faster. 🔥 Example: Creating an Index To create an index on the OrderID column of an Orders table, you can use this SQL statement:

CREATE INDEX idx_orderid ON Orders(OrderID);

This creates a non-clustered index on the OrderID column. Now, queries filtering by OrderID will be much faster. 👇Types of Indexes with Examples: 1. Primary Index: When you define a primary key, SQL automatically creates a unique index. For example:

   CREATE TABLE Customers (
       CustomerID INT PRIMARY KEY,
       Name VARCHAR(100)
   );
   

This creates a unique index on CustomerID because it's the primary key. 2. Unique Index: A unique index ensures that no two rows have the same value in the indexed column(s). For example:

   CREATE UNIQUE INDEX idx_email ON Customers(Email);
   

This ensures that no two customers can have the same email address. 3. Composite Index: Sometimes queries filter on more than one column. A composite index can improve performance in such cases. For example:

   CREATE INDEX idx_name_city ON Customers(Name, City);
   

This index will speed up queries like:

   SELECT * FROM Customers WHERE Name = 'John' AND City = 'New York';
   

4. Clustered Index: A clustered index determines the physical order of data in a table. By default, a table can have only one clustered index, often created when you define a primary key. Here’s an example:

   CREATE CLUSTERED INDEX idx_customerid ON Customers(CustomerID);
   

This organizes the table's data based on CustomerID. 5. Non-Clustered Index: A non-clustered index contains pointers to the data rather than reordering the table. You can create multiple non-clustered indexes. For example:

   CREATE INDEX idx_lastname ON Customers(LastName);
   

This would optimize queries that search by LastName:

   SELECT * FROM Customers WHERE LastName = 'Smith';
   

🚫 When NOT to Use Indexes: While indexes boost performance, they aren’t always a silver bullet. For example, creating too many indexes can slow down write operations like INSERT, UPDATE, and DELETE because the database has to update each index. Indexes also consume additional disk space. Here’s an example where an index might hurt performance: If you're frequently updating or inserting new orders in a table, having too many indexes on that table might cause slowdowns:

INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (104, 3, '2024-10-16');

✍ Key Takeaway: Indexes are essential for optimizing database queries, but they should be used strategically. Analyze your query patterns and database size to decide when and where to apply them.

⚡️Python Crash Course: Master Coding Fundamentals in Under 90 Minutes! Unlock the power of #Python programming with this crash course designed to teach essential coding skills in just under 90 minutes. Perfect for beginners and those looking to refresh their knowledge, this video dives straight into the core concepts of Python, offering hands-on examples and practical tips. By the end of the #course, coding fundamentals will be second nature, paving the way for more advanced programming adventures. Don't miss this opportunity to master Python quickly and efficiently. https://www.youtube.com/watch?v=VOdPQmm298o&list=PLTsu3dft3CWiow7L7WrCd27ohlra_5PGH&t=1s

#SQL Zero to Hero

Database Design FREE Course - Learn how to design and plan a database for beginners. 🚀 This database design course will help you understand database concepts and give you a deeper grasp of database design. Database design is the organisation of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model. 🔻 https://www.youtube.com/watch?v=ztHopE5Wnpc&t=4486s #sql #database

📊 Understanding CTE (Common Table Expressions) in SQL If you're working with SQL, you may have come across complex queries that are hard to read or maintain. One tool to simplify such queries is the Common Table Expression (CTE). 📍 What is a CTE? A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and can help break down complicated queries into manageable parts. You can think of it as a temporary view that lasts only for the duration of the query. 📍 Syntax of a CTE The basic syntax for a CTE looks like this:

WITH CTE_name AS (
    -- your query here
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM CTE_name;

The WITH keyword introduces the CTE, followed by the name you want to give the CTE (`CTE_name`). Inside the parentheses, you write the query that defines the CTE. After the CTE is defined, you can use it in your main query as if it were a table. 📍 Example of a CTE Let’s consider an example where we want to retrieve employees from a database who have salaries higher than the department average:

WITH DepartmentSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_name, e.salary, d.avg_salary
FROM employees e
JOIN DepartmentSalaries d
    ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

In this example, we use a CTE called DepartmentSalaries to calculate the average salary for each department. Then, in the main query, we use this CTE to filter out employees whose salary is higher than their department's average. 📍 Why Use a CTE? - Improves readability: CTEs allow you to break down complex queries into simpler, logical steps. - Reusability: You can reference a CTE multiple times within the same query. - Recursion: One powerful use of CTEs is for recursive queries, such as working with hierarchical data (e.g., organization charts, file systems). 📍 Recursive CTE Recursive CTEs are useful for hierarchical data where a query references itself. Here's an example that retrieves all employees and their managers in a hierarchy:

WITH RecursiveCTE AS (
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL -- Start with the top manager
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    INNER JOIN RecursiveCTE r
    ON e.manager_id = r.employee_id
)
SELECT * FROM RecursiveCTE;

This recursive CTE retrieves all employees, starting from the top manager, and follows the reporting chain to include all subordinates. 📍 Conclusion CTEs are an invaluable tool for SQL developers. They make queries easier to read, help break down complex logic, and offer powerful recursive capabilities for dealing with hierarchical data. If you're not using CTEs yet, it’s time to incorporate them into your SQL toolkit!

🖥 #SQL Chart

How made $300k as a Freelance Data #Analyst Using Linkedin (5 Easy Steps) https://www.youtube.com/watch?v=NCxpmzx7EXE #dataanalyst #sql

💻 SQL Tip: Using `CONCAT()` Function 🔗 CONCAT() is a super handy function in SQL when you need to combine (concatenate) two or more strings into one. 📍Syntax:

SELECT CONCAT(string1, string2, string3, ...)

It takes multiple string arguments and merges them into a single output. You can use this to join columns, static text, or any combination of values. 📍Example: Suppose you have a table employees with two columns: first_name and last_name. You want to display their full name:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

This will combine the first_name and last_name columns with a space in between. 📍 Pro Tips: - If any argument is NULL, CONCAT() will skip it instead of returning NULL. Use CONCAT_WS() (concatenate with separator) to ensure separators are added properly. - If you're working in Oracle or databases that don't support CONCAT(), use the || operator instead. #sql #concat #dataanalyst

#BigQuery Beginners Tip 🚀 If you're working in BigQuery and need to change a word or variable across your script, don't update each instance manually! Use Change All Occurrences 🔄 Here’s how to do it: 1. Select the text you want to change. 2. Right-click (or use the keyboard shortcut) and select Change All Occurrences. 3. Type the new text, and BigQuery will update all instances at once! This simple trick can be a time-saver, especially when dealing with large #SQL queries. #dataanalyst #analytics

More than 2000+ questions for preparing a Data Engineer interview. https://github.com/OBenner/data-engineering-interview-questions #Dataengineer #analytics #analyst

Very cool sketches about #dataviz Read it here: https://uxknowledgebase.com/tables-other-charts-data-visualization-part-3-5bfab15ce525 #dataanalyst #analyst #productanalyst

IN vs EXISTS in SQL: Quick Overview IN and EXISTS are used to compare values between tables in SQL, but they work differently and suit different situations. 📍 IN Operator The IN operator checks if a value exists in a list or subquery result. It’s useful for smaller datasets. Example:

SELECT * 
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'NY');

This returns employees in New York departments. IN processes the full subquery result, which can slow down large datasets. 📍 EXISTS Operator The EXISTS operator checks if a subquery returns any rows, making it faster for larger datasets. Example:

SELECT * 
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.location = 'NY');

It stops the subquery after the first match, speeding up execution. 📍 Key Points - Performance: IN is slower for large datasets, while EXISTS is faster. - Null Values: EXISTS handles NULL better. 📊 Conclusion Use IN for smaller queries, and EXISTS for larger datasets or when performance is a priority. #dataanalyst #analytics #analyst

🔥 Test Your Python Skills! 🔥 💻 Here are some must-know Python interview questions with solutions! Let’s dive in and sharpen your coding skills: 1️⃣ Check if a string is a palindrome:

def is_palindrome(s):
    return s == s[::-1]

print(is_palindrome("madam"))  # True
print(is_palindrome("hello"))  # False

2️⃣ Factorial using recursion:

def factorial(n):
    if n == 0 or n == 1:
        return 1
    return n * factorial(n - 1)

print(factorial(5))  # 120

3️⃣ Merge two dictionaries:

dict1 = {'a': 1, 'b': 2}
dict2 = {'c': 3, 'd': 4}

# Python 3.5+
merged_dict = {**dict1, **dict2}

# Python 3.9+
merged_dict = dict1 | dict2

print(merged_dict)

4️⃣ Find intersection of two lists:

list1 = [1, 2, 3, 4]
list2 = [3, 4, 5, 6]

intersection = list(set(list1) & set(list2))
print(intersection)  # [3, 4]

5️⃣ Generate even numbers from 1 to 100:

even_numbers = [i for i in range(1, 101) if i % 2 == 0]
print(even_numbers)

6️⃣ Find the longest word in a sentence:

def longest_word(sentence):
    words = sentence.split()
    return max(words, key=len)

print(longest_word("Python is a powerful language"))  # "powerful"

7️⃣ Count frequency of elements in a list:

from collections import Counter

my_list = [1, 2, 2, 3, 3, 3, 4]
frequency = Counter(my_list)
print(frequency)  # Counter({3: 3, 2: 2, 1: 1, 4: 1})

8️⃣ Remove duplicates while keeping order:

def remove_duplicates(lst):
    return list(dict.fromkeys(lst))

my_list = [1, 2, 2, 3, 4, 4, 5]
print(remove_duplicates(my_list))  # [1, 2, 3, 4, 5]

9️⃣ Reverse a linked list:

class Node:
    def __init__(self, data):
        self.data = data
        self.next = None

def reverse_linked_list(head):
    prev = None
    current = head
    while current:
        next_node = current.next
        current.next = prev
        prev = current
        current = next_node
    return prev

# Create and reverse linked list: 1 -> 2 -> 3
head = Node(1)
head.next = Node(2)
head.next.next = Node(3)
reversed_head = reverse_linked_list(head)
while reversed_head:
    print(reversed_head.data, end=" -> ")
    reversed_head = reversed_head.next

🔟 Binary Search Algorithm:

def binary_search(arr, target):
    low, high = 0, len(arr) - 1
    while low <= high:
        mid = (low + high) // 2
        if arr[mid] == target:
            return mid
        elif arr[mid] < target:
            low = mid + 1
        else:
            high = mid - 1
    return -1

print(binary_search([1, 2, 3, 4, 5, 6, 7], 4))  # 3

--- 🔗 Practice these Python snippets and ace your next interview! 💪

Basics of Lakehouse Engineering The good course about #Lakehouse Engineering - #Apache Iceberg, Nessie, Dremio. https://www.youtube.com/watch?v=wepFB_WP_9g&list=PLsLAVBjQJO0qVfGet6FEQw-nZ6ygLtYuH&index=1 #sql #dataanalyst