SQL | Data Analytics

📖 Learn SQL

Here's one of the most useful SQL keywords you've never heard of: GROUP BY CUBE It's useful when for performing aggregate analysis when you want to group by more than one column. Think of GROUP BY CUBE as the ultimate grouping keyword. It essentially contains GROUP BY ROLLUP and GROUP BY GROUPING SETS inside of it. How does it work? Take our example dataset: the Citibike trips dataset. This data contains Citibike trips taken by riders in NYC. Let's say you're grouping by three different columns: year, month, and station name. You're doing a COUNT() of trips taken. What are all the possible groupings we could come up with? 1. Total trips overall 2. Trips from each station 3. Trips in each month 4. Trips in each year 5. Trips in each month + each station 6. Trips in each year + each station 7. Trips in each year + each month 8. Trips in each year + each month + each station GROUP BY CUBE will perform all of these grouping combinations for you automatically. Without GROUP BY CUBE, you'd need to write multiple queries and UNION the results together! The image below shows what this looks like in actual code. Usage is super simple. All you need to do is type GROUP BY CUBE along with the names of the columns you'd like to group by. #sql #groupby

Use data creatively, but responsibly 😉

🪄 Covering the Entire Data Set with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 🪄 When you need every row in your partition to “know” the complete picture, use this powerful window frame:

AVG(amount) OVER (PARTITION BY category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS partition_avg

What’s Happening? This clause tells SQL to consider all rows in the partition—ignoring the current row’s position entirely. It’s perfect for calculations that need a partition-wide perspective, such as computing overall averages, totals, or even min/max values for each group. Why Use It? 👉 Comprehensive Analysis: Every row in the partition has access to the entire group's data, which is ideal when you need to compare each record against a complete metric. 👉 Consistency: It ensures that each row in the partition uses the same aggregate value, even if you’re applying an ORDER BY clause elsewhere. For example, without an ORDER BY clause, functions like COUNT(event_name) OVER (PARTITION BY session_id) will return the full count for that session. However, if you add an ORDER BY clause, SQL might default to a running count (using a frame like RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures you always get the full count regardless of ordering. When to Use It: 👉 Group-Wide Metrics: When you want to display a group’s total or average alongside each individual record. 👉 Data Normalisation: When you need to calculate percentages or deviations relative to the entire group. I'll say again that you can use a number instead of UNBOUNDED, you may have a use case when you want to return results for the 5 page views prior and 5 page views after current row for instance. Have you used partition-wide window frames in your projects? Share your success stories or questions below!

What fundamental axioms and unchangeable principles exist in data engineering and data modeling? Consider Euclidean geometry as an example. It's an axiomatic system, built on universal "true statements" that define the entire field. For instance, "a line can be drawn between any two points" or "all right angles are equal." From these basic axioms, all other geometric principles can be derived. So, what are the axioms of data engineering and data modeling? I asked ChatGPT about that and it gave this list: ▪️ Data exists in multiple forms and formats ▪️ Data can and should be transformed to serve the needs ▪️ Data should be trustworthy ▪️ Data systems should be efficient and scalable Classic ChatGPT, pretty standard, pretty boring 🥱. Yes, these are universal and fundamental rules, but what can we learn from them? Here is what I'd call axioms for myself: 🔹 Every table should have a primary key which is unique and not empty (dbt tests for life 🙂) 🔹 Every column should have strong types and constraints (storing data as STRING or JSON is ouch) 🔹 Data pipelines should be idempotent (I don't want to deal with duplicates and inconsistencies) 🔹 Every data transformation has to be defined in code (otherwise what are we doing here) Now it's your turn: what principles would you defend at all costs? 🤔

📖 Most important SQL commands

Top 10 Advanced SQL Queries for Data Mastery 1. Recursive CTE (Common Table Expressions) Use a recursive CTE to traverse hierarchical data, such as employees and their managers.

WITH RECURSIVE EmployeeHierarchy AS (
  SELECT employee_id, employee_name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.employee_name, e.manager_id
  FROM employees e
  JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * 
FROM EmployeeHierarchy;

2. Pivoting Data Turn row data into columns (e.g., show product categories as separate columns).

SELECT *
FROM (
    SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, product_category, sales_amount
    FROM sales
) AS pivot_data
PIVOT (
    SUM(sales_amount)
    FOR product_category IN ('Electronics', 'Clothing', 'Books')
) AS pivoted_sales;

3. Window Functions Calculate a running total of sales based on order date.

SELECT 
  order_date, 
  sales_amount, 
  SUM(sales_amount) OVER (ORDER BY order_date) AS running_total
FROM sales;

4. Ranking with Window Functions Rank employees’ salaries within each department.

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

5. Finding Gaps in Sequences Identify missing values in a sequential dataset (e.g., order numbers).

WITH Sequences AS (
  SELECT MIN(order_number) AS start_seq, MAX(order_number) AS end_seq
  FROM orders
)
SELECT start_seq + 1 AS missing_sequence
FROM Sequences
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.order_number = Sequences.start_seq + 1
);

6. Unpivoting Data Convert columns into rows to simplify analysis of multiple attributes.

SELECT 
  product_id, 
  attribute_name, 
  attribute_value
FROM products
UNPIVOT (
  attribute_value FOR attribute_name IN (color, size, weight)
) AS unpivoted_data;

7. Finding Consecutive Events Check for consecutive days/orders for the same product using LAG().

WITH ConsecutiveOrders AS (
  SELECT 
    product_id, 
    order_date,
    LAG(order_date) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date
  FROM orders
)
SELECT product_id, order_date, prev_order_date
FROM ConsecutiveOrders
WHERE order_date - prev_order_date = 1;

8. Aggregation with the FILTER Clause Calculate selective averages (e.g., only for the Sales department).

SELECT 
  department,
  AVG(salary) FILTER (WHERE department = 'Sales') AS avg_salary_sales
FROM employees
GROUP BY department;

9. JSON Data Extraction Extract values from JSON columns directly in SQL.

SELECT 
  order_id, 
  customer_id,
  order_details ->> 'product' AS product_name,
  CAST(order_details ->> 'quantity' AS INTEGER) AS quantity
FROM orders;

10. Using Temporary Tables Create a temporary table for intermediate results, then join it with other tables.

-- Create a temporary table
CREATE TEMPORARY TABLE temp_product_sales AS
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

-- Use the temp table
SELECT p.product_name, t.total_sales
FROM products p
JOIN temp_product_sales t ON p.product_id = t.product_id;

Why These Matter Advanced SQL queries let you handle complex data manipulation and analysis tasks with ease. From traversing hierarchical relationships to reshaping data (pivot/unpivot) and working with JSON, these techniques expand your ability to derive insights from relational databases. Keep practicing these queries to solidify your SQL expertise and make more data-driven decisions! #sql #dataanalyst

Brilliant educational meme that explains SQL joins #sql #joins #dataanalyst #productanalyst

🪄Why Is My LAST_VALUE() ASC Not Matching My FIRST_VALUE() DESC?🪄 I remember the moment I first encountered SQL window functions and was stumped by this behavior. You might write: 👉LAST_VALUE(value) OVER (ORDER BY date ASC) and expect it to match: 👉FIRST_VALUE(value) OVER (ORDER BY date DESC) but it doesn’t always! The Secret? .... Default Window Frames. SQL functions work over a “window” of rows. By default: LAST_VALUE() only looks at rows from the start of the partition up to the current row. This means that at any given point, it's only considering what it has seen so far, not what comes next. FIRST_VALUE() with a descending order grabs the first row in that reversed list—which, in an ascending sequence, corresponds to the very last row. ✨ How to Fix It If you need LAST_VALUE() to “peek” ahead and return the last value in the entire partition, adjust its frame: LAST_VALUE(value) OVER ( ORDER BY date ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) This tells SQL to look from the current row all the way to the end, ensuring you get the actual last value. ✨Takeaway: Understanding window frames is key to getting the results you expect. When your output isn’t matching your intuition, it might just be a window frame issue! I’ll talk you through the different window frame options over my next posts. Have you encountered this issue before? What was your approach to troubleshooting it? Let’s discuss in the comments! #sql #bigquery #dataanalyst

♦ In case you are ready to learn new skills and tools and more or less defined your study roadmap, the next step would be to decide where to practice. For sure, you have a free option to leverage your laptop and install different kinds of free software such as databases, data tools, coding frameworks and so on. However, it would be far from ideal. Far from real-world use cases. I hope it is crystal clear that you won’t get a job with localhost projects and even if you get one you will struggle in the organization because they run things differently. So, the best path forward for you work with the most popular vendors. Luckily for us all of them are providing a Trial (Free) period that will work perfectly for your goals. I've collected the most popular resources for public cloud, data platforms, BI and ETL vendors that are giving you free trials and hands-on tutorials. ✅ https://blog.surfalytics.com/p/free-trials-and-student-programs

SQL Noir - a game for learning SQL "The developers have released SQL Noir, a game for learning SQL. According to the plot, the user must take on the role of a detective and solve several crimes by analyzing evidence in the database. The crime challenges in SQL Noir are divided into three levels: beginner, intermediate and advanced. There are currently four cases available to solve: - theft of a briefcase containing important documents; - theft of an expensive vinyl record; - mysterious murder in Miami; - the murder of an aristocrat during a social party." #sql

🚩If you want to Excel at using the most used database language in the world, learn these powerful SQL features: • Wildcards (%, _) – Flexible pattern matching • Window Functions – ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG() • Common Table Expressions (CTEs) – WITH for better readability • Recursive Queries – Handle hierarchical data • STRING Functions – LEFT(), RIGHT(), LEN(), TRIM(), UPPER(), LOWER() • Date Functions – DATEDIFF(), DATEADD(), FORMAT() • Pivot & Unpivot – Transform row data into columns • Aggregate Functions – SUM(), AVG(), COUNT(), MIN(), MAX() • Joins & Self Joins – Master INNER, LEFT, RIGHT, FULL, SELF JOIN • Indexing – Speed up queries with CREATE INDEX Like it if you need a complete tutorial on all these topics! 👍❤️ #sql

📖 #SQL JOINS TYPES

🔥 In dbt, you can dynamically select source schemas when working with multiple environments. As mentioned before, dbt natively supports multi-environment setups—you can develop models in a development database and then run the same code in a production database. The challenge is that dbt sources are static by default, requiring exact values in the "source.yml" file. However, you can make them dynamic using Jinja. I recently got the a case where we needed to create two identical models: one using production Postgres data and another using staging data. Manually changing the value isn’t the option. So I used Jinja to make it dynamic. This works by using a dbt variable instead of a static value. It's good practice to provide a default value (such as the production schema name) in case the variable isn't specified. With this setup, regular "dbt run" commands will work as before. If you need to materialize the same model using a different source schema, you can simply pass the schema name to the dbt variable at runtime. #dbt #sql

♦ SQL is all you need! Supercharge #BigQuery with BigFunctions The SQL Data Stack is the future. It’s not just a buzzword but a transformative approach to how we handle our data operations, making SQL all we need! https://medium.com/google-cloud/sql-is-all-you-need-77554fea90c0 #sql #dataanalyst

🚀The Three Levels of SQL Comprehension: What they are and why you need to know about them https://docs.getdbt.com/blog/the-levels-of-sql-comprehension #sql #dataanalyst #dbt

♦Open Source Data Tools https://datais.me/updates/ For quite a long time I wanted to find a convenient page with which I could quickly get information about the latest versions of open source data products, and, if necessary, look at their history. Unfortunately, I couldn't find something suitable for me, and, as it happens, I launched my own. If suddenly someone else finds it useful, that would be great🥺 #sql #dataanalyst #data