SQL | Data Analytics

Data science volunteer wanted! 🎉 📍What we are expect from a candidate: Love data and internet search Skills to process data by hand or (semi)automate. Be ready to spend time with us from the 12th till 25th of December (6-7 hours in total). The task can be interesting for Data-science specialists, OSINT investigators and everyone who wants to interact with AI and different ETL tools. Who we are: 📍Editorial of pop-science zine: https://www.instagram.com/pamylka.zin/ 📍 Ready for volunteering? Write us here: pamylka.zin@gmail.com

🚀 Want to become an Analytics Engineer in 2025? Let’s brake it down together. Some people still believe that working with dbt alone makes them analytics engineers. However, analytics engineering isn't about a single tool — it's about a specific approach and skill set. Analytics Engineering is a multidisciplinary field that combines data engineering, data analysis, and software engineering. The main goal of an analytics engineer is to provide analysts and business users with clean, easy-to-understand datasets for answering analytical questions. Following software engineering best practices, these datasets must be well-tested and documented, enabling collaboration and the creation of new datasets built upon existing ones. To master analytics engineering, you need to develop skills in three key areas: basic skills, tooling, and domain expertise. For basic skills, I recommend learning: 🔹 SQL 🔹 Command line 🔹 Git 🔹 a bit of Python Required tooling: 🔹 dbt (or alternatives) 🔹 Airflow (or alternatives) 🔹 SQLFluff 🔹 pre-commit 🔹 Github Actions Domain expertise: 🔹 Data modeling 🔹 Data orchestration 🔹 Data quality 🔹 Data warehouse architecture

Data Engineering Zoomcamp - 2025 Cohort Start: 13 January 2025 Registration link: https://airtable.com/shr6oVXeQvSI5HuWD Materials specific to the cohort: cohorts/2025/ Self-paced mode All the materials of the course are freely available, so that you can take the course at your own pace

⚡️7 Projects to Master #Data #Engineering https://www.kdnuggets.com/7-projects-master-data-engineering

Getting Started with DuckDB: A Quick Guide 🦆💻 Are you ready to dive into the world of DuckDB, the blazing-fast analytical database engine? 🚀 Perfect for handling analytical workloads directly on your laptop or server without the need for a heavyweight setup. Here’s how you can get started: --- 1. What is DuckDB? DuckDB is an open-source SQL database engine optimized for analytical queries. It’s often called the “SQLite for analytics” because: - It’s lightweight and doesn’t require a server. - It runs entirely in-process, meaning you can integrate it into your application seamlessly. --- 2. Why Use DuckDB? - High Performance: Designed for analytical queries, even on large datasets. - Easy to Use: Query your data with SQL—no complex setup. - Portable: Works across platforms, with zero configuration. - Integration-Friendly: Works with CSV, Parquet, Arrow, Pandas, and more. --- 3. Installation DuckDB is super easy to install. Just run: - Python: pip install duckdb - R: install.packages("duckdb") - Command Line: Download the binary from the DuckDB website. --- 4. Getting Started with Queries Here’s a quick example in Python:

import duckdb

# Create an in-memory database and query a CSV
con = duckdb.connect()
result = con.execute("SELECT * FROM 'data.csv' WHERE value > 100").df()

print(result)

DuckDB also supports SQL joins, window functions, and much more! --- 5. Use Cases - Analyze datasets directly in tools like Python or R. - Query large Parquet or CSV files without loading them into memory. - Build lightweight ETL pipelines. --- Start exploring the power of DuckDB today and transform how you work with data! 🦆✨ 👉 Visit DuckDB.org to learn more. #DuckDB #SQL #DataAnalytics

Nice intro about #databases, indexes and data modelling https://github.com/oleg-agapov/data-engineering-book/blob/master/book/2-beginner-path/2-1-databases/databases.md

Advent of code 2024 🎄 Advent of Code 2024 starts tomorrow! If you are not familiar with this wonderful resource, be sure to pay attention. What is it? Every day in December, a new programming puzzle opens up to solve - sort of like a LeetCode-like holiday calendar for developers. 💡 Pro tip: Many developers use Advent of Code as a great opportunity to learn a new programming language. It's much more fun than just reading official tutorials! 🔥 By the way, the Dagster team launched a similar initiative - " 30 Days of Orchestration" . This is a great opportunity to get to know Dagster in practice. Join these challenges - level up your skills in December! 🚀

DBT Tutorial 12 videos YouTube Playlist https://youtube.com/playlist?list=PLc2EZr8W2QIBegSYp4dEIMrfLj_cCJgYA&si=RXZY3jcHCOQ9A4Sz #dbt

💡 What is Self JOIN in #SQL? Self JOIN is a technique in SQL where a table is joined with itself. It’s used to find relationships within the same table, such as hierarchical data or comparisons between rows. --- 🔑 When to Use Self JOIN? - To explore hierarchies (e.g., employees and managers). - To compare rows (e.g., finding duplicates). - For parent-child relationships (e.g., categories and subcategories). --- 🛠 Example 1: Employee and Manager Suppose we have an employees table: | employee_id | name | manager_id | |-------------|----------|------------| | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie | 1 | | 4 | David | 2 | Query:

SELECT  
    e1.name AS Employee,  
    e2.name AS Manager  
FROM  
    employees e1  
LEFT JOIN  
    employees e2  
ON  
    e1.manager_id = e2.employee_id;  

Result: | Employee | Manager | |----------|----------| | Alice | NULL | | Bob | Alice | | Charlie | Alice | | David | Bob | --- 🛠 Example 2: Finding Duplicates For a products table: | product_id | name | price | |------------|----------|-------| | 1 | Laptop | 1000 | | 2 | Laptop | 1000 | | 3 | Monitor | 200 | | 4 | Laptop | 1200 | Query:

SELECT  
    p1.product_id,  
    p2.product_id,  
    p1.name,  
    p1.price  
FROM  
    products p1  
JOIN  
    products p2  
ON  
    p1.name = p2.name AND  
    p1.price = p2.price AND  
    p1.product_id < p2.product_id;  

Result: | Product1 | Product2 | Name | Price | |----------|----------|---------|-------| | 1 | 2 | Laptop | 1000 | --- ⚡ Pro Tip: Use aliases (`e1`, `e2`) to avoid confusion, and always optimize your query for large datasets with proper indexing. #dataanalyst

### 💡 What is Self JOIN in SQL? Self JOIN is a technique in SQL where a table is joined with itself. It’s used to find relationships within the same table, such as hierarchical data or comparisons between rows. --- ### 🔑 When to Use Self JOIN? - To explore hierarchies (e.g., employees and managers). - To compare rows (e.g., finding duplicates). - For parent-child relationships (e.g., categories and subcategories). --- ### 🛠 Example 1: Employee and Manager Suppose we have an employees table: | employee_id | name | manager_id | |-------------|----------|------------| | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie | 1 | | 4 | David | 2 | Query:

SELECT  
    e1.name AS Employee,  
    e2.name AS Manager  
FROM  
    employees e1  
LEFT JOIN  
    employees e2  
ON  
    e1.manager_id = e2.employee_id;  

Result: | Employee | Manager | |----------|----------| | Alice | NULL | | Bob | Alice | | Charlie | Alice | | David | Bob | --- ### 🛠 Example 2: Finding Duplicates For a products table: | product_id | name | price | |------------|----------|-------| | 1 | Laptop | 1000 | | 2 | Laptop | 1000 | | 3 | Monitor | 200 | | 4 | Laptop | 1200 | Query:

SELECT  
    p1.product_id,  
    p2.product_id,  
    p1.name,  
    p1.price  
FROM  
    products p1  
JOIN  
    products p2  
ON  
    p1.name = p2.name AND  
    p1.price = p2.price AND  
    p1.product_id < p2.product_id;  

Result: | Product1 | Product2 | Name | Price | |----------|----------|---------|-------| | 1 | 2 | Laptop | 1000 | --- ### ⚡ Pro Tip: Use aliases (`e1`, `e2`) to avoid confusion, and always optimize your query for large datasets with proper indexing. --- 🖥 Want to level up your SQL game? Save this post and try out these queries! 🚀

🎯 Understanding Self JOIN in SQL with Examples A Self JOIN in SQL is a join operation where a table is joined with itself. It is often used when you need to compare rows within the same table. A self join creates an alias for the table to distinguish between the original table and its "duplicate." --- ### Syntax of Self JOIN

SELECT a.column1, b.column2
FROM table_name AS a
JOIN table_name AS b
ON a.common_column = b.common_column;

Here: - table_name is the original table. - a and b are aliases for the table. - common_column is the column used to join the table to itself. --- ### Use Cases for Self JOIN 1. Finding Relationships Within Data: For example, finding employees and their managers in an organizational hierarchy. 2. Comparing Rows: For instance, finding duplicate entries or anomalies in data. 3. Data Lineage or Tree Structures: Useful for navigating parent-child relationships. --- ### Example 1: Employee-Manager Relationship Consider a table employees with the following structure: | employee_id | name | manager_id | |-------------|----------|------------| | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie | 1 | | 4 | David | 2 | Here, each employee has a manager_id that references the employee_id of another row. #### Query:

SELECT 
    e1.name AS Employee,
    e2.name AS Manager
FROM 
    employees AS e1
LEFT JOIN 
    employees AS e2
ON 
    e1.manager_id = e2.employee_id;

#### Result: | Employee | Manager | |----------|----------| | Alice | NULL | | Bob | Alice | | Charlie | Alice | | David | Bob | --- ### Example 2: Finding Duplicate Entries Consider a products table: | product_id | name | price | |------------|----------|-------| | 1 | Laptop | 1000 | | 2 | Laptop | 1000 | | 3 | Monitor | 200 | | 4 | Laptop | 1200 | #### Query to Find Duplicate Products:

SELECT 
    p1.product_id AS Product1,
    p2.product_id AS Product2,
    p1.name,
    p1.price
FROM 
    products AS p1
JOIN 
    products AS p2
ON 
    p1.name = p2.name AND 
    p1.price = p2.price AND 
    p1.product_id < p2.product_id;

#### Result: | Product1 | Product2 | Name | Price | |----------|----------|---------|-------| | 1 | 2 | Laptop | 1000 | --- ### Example 3: Detecting Hierarchical Relationships Consider a categories table: | category_id | category_name | parent_category_id | |-------------|---------------|--------------------| | 1 | Electronics | NULL | | 2 | Laptops | 1 | | 3 | Accessories | 1 | | 4 | Keyboards | 3 | #### Query:

SELECT 
    c1.category_name AS Category,
    c2.category_name AS Parent_Category
FROM 
    categories AS c1
LEFT JOIN 
    categories AS c2
ON 
    c1.parent_category_id = c2.category_id;

#### Result: | Category | Parent_Category | |-------------|-----------------| | Electronics | NULL | | Laptops | Electronics | | Accessories | Electronics | | Keyboards | Accessories | --- ### Key Notes 1. Always use aliases when performing a self join to avoid ambiguity. 2. Self joins can be computationally expensive for large datasets, so use them judiciously with appropriate indexes. 3. Depending on the use case, you may choose an INNER JOIN, LEFT JOIN, or other types of joins. --- ### Conclusion The Self JOIN is a powerful tool in SQL for working with hierarchical or comparative data. By creating table aliases and using a clear structure, you can unlock complex relationships and insights from your data.

💡 Understanding Self JOIN in #SQL with Examples A Self JOIN in SQL is a join operation where a table is joined with itself. It is often used when you need to compare rows within the same table. A self join creates an alias for the table to distinguish between the original table and its "duplicate." --- 📌**Syntax of Self JOIN**

SELECT a.column1, b.column2
FROM table_name AS a
JOIN table_name AS b
ON a.common_column = b.common_column;

Here: - table_name is the original table. - a and b are aliases for the table. - common_column is the column used to join the table to itself. --- 🔻 Use Cases for Self JOIN 1. Finding Relationships Within Data: For example, finding employees and their managers in an organizational hierarchy. 2. Comparing Rows: For instance, finding duplicate entries or anomalies in data. 3. Data Lineage or Tree Structures: Useful for navigating parent-child relationships. --- 🔥 Example 1: Employee-Manager Relationship Consider a table employees with the following structure: | employee_id | name | manager_id | |-------------|----------|------------| | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie | 1 | | 4 | David | 2 | Here, each employee has a manager_id that references the employee_id of another row. #### Query:

SELECT 
    e1.name AS Employee,
    e2.name AS Manager
FROM 
    employees AS e1
LEFT JOIN 
    employees AS e2
ON 
    e1.manager_id = e2.employee_id;

#### Result: | Employee | Manager | |----------|----------| | Alice | NULL | | Bob | Alice | | Charlie | Alice | | David | Bob | --- 🔥 Example 2: Finding Duplicate Entries Consider a products table: | product_id | name | price | |------------|----------|-------| | 1 | Laptop | 1000 | | 2 | Laptop | 1000 | | 3 | Monitor | 200 | | 4 | Laptop | 1200 | #### Query to Find Duplicate Products:

SELECT 
    p1.product_id AS Product1,
    p2.product_id AS Product2,
    p1.name,
    p1.price
FROM 
    products AS p1
JOIN 
    products AS p2
ON 
    p1.name = p2.name AND 
    p1.price = p2.price AND 
    p1.product_id < p2.product_id;

#### Result: | Product1 | Product2 | Name | Price | |----------|----------|---------|-------| | 1 | 2 | Laptop | 1000 | --- 🔥 Example 3: Detecting Hierarchical Relationships Consider a categories table: | category_id | category_name | parent_category_id | |-------------|---------------|--------------------| | 1 | Electronics | NULL | | 2 | Laptops | 1 | | 3 | Accessories | 1 | | 4 | Keyboards | 3 | #### Query:

SELECT 
    c1.category_name AS Category,
    c2.category_name AS Parent_Category
FROM 
    categories AS c1
LEFT JOIN 
    categories AS c2
ON 
    c1.parent_category_id = c2.category_id;

#### Result: | Category | Parent_Category | |-------------|-----------------| | Electronics | NULL | | Laptops | Electronics | | Accessories | Electronics | | Keyboards | Accessories | --- Key Notes 1. Always use aliases when performing a self join to avoid ambiguity. 2. Self joins can be computationally expensive for large datasets, so use them judiciously with appropriate indexes. 3. Depending on the use case, you may choose an INNER JOIN, LEFT JOIN, or other types of joins. --- 📊**Conclusion** The #Self #JOIN is a powerful tool in SQL for working with hierarchical or comparative #data. By creating table aliases and using a clear structure, you can unlock complex relationships and insights from your data. #dataanalyst

✅ Why #SQL is Unkillable SQL's Penetration is Absurd There's probably no computer language in the world with as diverse of a userbase as SQL. Now, that's not to say that it's necessarily important that data analysts are able to work your transactional database, but there's a lot of value in being able to piggyback off of extant documentation of how to use SQL, and your users's pre-existing understanding of SQL. When it comes to teaching people how to use a tool, being able to leverage things that already exist is a big win. If you can get value out of fifty years of accumulated documentation, that's great. If people just already know how to query your database, that's even better. SQL is "Good Enough" I think this is sort of the lazy answer. But it's true, at least to some degree. For general-purpose programming languages, there is some space for debate about what things are important, and what parts of computation should be emphasized. But if you look at any honest attempt at replacing SQL, you get something that's still vaguely SQL-shaped, in terms of its implementation of the relational model. We got the fundamentals right on the first try, basically. And yeah, there's some bad stuff in there, bizarre syntax, weird semantics, poor capabilities for abstraction. But empirically it seems like people are getting on just fine despite all those things. SQL is Not a Standard Yes, okay, there is a document which is "the SQL standard." That thing is about as toothless as they come, though. Any dream that once existed of having SQL be portable across databases is completely dead. The reason everyone uses SQL is because everyone knows SQL. But this is not such a bad thing. Once you throw out the dream of one, unified SQL, the language itself becomes a platform that is malleable. Vendors who have specific needs, or features, can graft them onto SQL pretty easily, without having to go through some standards body, or requiring users to activate some pragma. it's just "_____-flavoured SQL." I'm not going to transpile my SQL I've seen a couple of SQL-killers operate by compiling their query language into SQL, so you can use it directly against Postgres, or whatever. Look, I'll be straight with you: I'm not going to do that. It took the JavaScript community years to get that experience satisfactory, and they had the force of Google and Mozilla behind them, along with a ton of community will to make it work. I haven't actually tried one of these languages that compiles to SQL, but it's a hard sell to introduce another layer into the stack for me, chief. The Query Language is a Small Piece of the Puzzle A database is an extremely expensive and complex piece of software to build. There's a ton of components to be built, depending on the architecture, that are not related to the query language at all. This means that most-to-all database projects have to answer the question "what query language are we going to use." Nobody ever got fired for choosing SQL. In a piece of software with as many moving pieces as a database, an important consideration is to de-risk as many individual pieces as possible. If you have a choice between one of the most popular and successful languages of all time, and something new and arguably "better," the sensible choice is SQL, regardless of any aesthetic qualms one might have with its design. I think this is related to, but distinct from: Novelty Budgets You only get a couple pivot points to innovate on when building a new database. For a scary, important piece of your tech stack like a database, you're not willing to take weird bets on too many dimensions of it. I'd be willing to bet that SQL, being good enough for most things, is not bad enough to justify spending some of that budget on replacing it. from - https://buttondown.com/jaffray/archive/why-sql-is-unkillable/

🎯 Mastering Pivot and Unpivot Operations in #SQL 🎯 Pivot and Unpivot are powerful SQL operations that help reshape your data for analysis and reporting. Here's everything you need to know: --- ### 🔄 Pivot: Rows to Columns What it does: Converts data from a long format (rows) into a wide format (columns). ✅ Use case: Summarize data, like showing sales for different products by month. 🔧 Example: Imagine you have a table SalesData like this: | Month | Product | Sales | |--------|----------|-------| | Jan | A | 100 | | Jan | B | 200 | | Feb | A | 150 | | Feb | B | 250 | You can pivot it to look like this: | Month | A | B | |--------|-----|-----| | Jan | 100 | 200 | | Feb | 150 | 250 | SQL Code:

SELECT Month, 
       [A] AS ProductA, 
       [B] AS ProductB
FROM (
    SELECT Month, Product, Sales
    FROM SalesData
) src
PIVOT (
    SUM(Sales) FOR Product IN ([A], [B])
) AS PivotTable;

--- ### 🔄 Unpivot: Columns to Rows What it does: Converts data from a wide format (columns) into a long format (rows). ✅ Use case: Normalize data for analysis or storage. 🔧 Example: Starting with this table: | Month | A | B | |--------|-----|-----| | Jan | 100 | 200 | | Feb | 150 | 250 | You can unpivot it to look like this: | Month | Product | Sales | |--------|----------|-------| | Jan | A | 100 | | Jan | B | 200 | | Feb | A | 150 | | Feb | B | 250 | SQL Code:

SELECT Month, Product, Sales
FROM (
    SELECT Month, [A], [B]
    FROM SalesData
) src
UNPIVOT (
    Sales FOR Product IN ([A], [B])
) AS UnpivotTable;

--- 💡 Key Points: - Pivot is ideal for creating summarized reports. - Unpivot helps normalize your data for deeper analysis. - Both operations make your dataset more flexible for analytics and visualization. 🔗 Pro Tip: Master these operations to prepare your data for tools like Power BI, Tableau, or Excel.

Understanding NTILE in SQL: Dividing Data into Equal Groups Have you ever wondered how to divide your data into equal groups for analysis? Meet NTILE, a powerful SQL function that makes this task incredibly easy. Whether you’re analyzing user engagement, sales distribution, or employee performance, NTILE is your go-to tool. ### What is NTILE? NTILE is a window function in SQL that divides your dataset into a specified number of groups, or "tiles," based on the order you define. It’s perfect for creating quartiles, deciles, percentiles, or any other equal subdivisions. ### Syntax:

NTILE(number_of_groups) OVER (PARTITION BY column_name ORDER BY column_name)

- `number_of_groups`: How many groups you want to divide your data into. - `PARTITION BY`: (Optional) Breaks your dataset into smaller partitions before applying NTILE. - `ORDER BY`: Determines the order in which rows are assigned to groups. ### How Does It Work? Imagine you have a table of sales data, and you want to rank salespeople into 4 performance tiers based on their total sales. Here’s how you can do it:

SELECT 
    salesperson_id,
    total_sales,
    NTILE(4) OVER (ORDER BY total_sales DESC) AS performance_tier
FROM sales_data;

- The highest sales go into tier 1, the next into tier 2, and so on. - If your data doesn’t divide evenly, NTILE distributes the remainder among the first few groups. ### Practical Use Cases: 1. Quartiles for Data Analysis: Divide data into 4 equal parts to identify top and bottom performers. 2. Customer Segmentation: Create groups like "top 10% buyers" or "bottom 10% buyers." 3. Experiment Binning: Divide users into groups for A/B testing. ### Limitations: - NTILE ensures groups are as equal as possible, but if your dataset has uneven rows, some groups may have one extra row. - It doesn’t work well for datasets with strict requirements for evenly distributed groups. ### Quick Tip: Combine NTILE with other window functions (like ROW_NUMBER, RANK, or PERCENT_RANK) for deeper insights and to handle edge cases. ### Final Thoughts: NTILE is a simple yet versatile tool that empowers you to group your data for meaningful analysis. If you haven’t tried it yet, give it a shot in your next #SQL project! What’s your favorite use case for #NTILE? Let me know in the comments. 🙌

✅ SQL JOINS are not the intersection of circles. https://www.youtube.com/watch?v=e4PUGpZlcIw Many articles on the internet continue to explain #SQL #JOINS with intersecting circles (Venn diagrams). In this video, we will show why this approach is incorrect, and how to actually illustrate joins.

✅ 6-week Free Data Engineering Boot Camp Launch Video This data engineering boot camp will be amazing! We'll be publishing a new video almost every day from November 15th, 2024 to December 31st, 2024! https://www.youtube.com/watch?v=myhe0LXpCeo #sql #dataanalyst