7 Advanced SQL Techniques for Data Scientists in 2024

In this blog post, we will explore seven advanced SQL techniques that every data scientists should master.

1. Common Table Expression (CTE)

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

CTEs simplify complex queries, improve readability, and can be used for recursive queries.

It is especially helpful in a SQL interview to make your code easy to understand for the interviewer.

Example 1: Write a query to compute ROI for each advertiser.

Report ROI for each advertiser.

WITH advertiser_cost AS (
   SELECT advertiser_id, SUM(cost) AS total_cost
   FROM advertiser
   GROUP BY advertiser_id
),
revenue AS (
    SELECT SUM(spend) revenue, A.advertiser_id
    FROM ad_info I
    INNER JOIN advertiser A
    ON A.ad_id = I.ad_id
    GROUP BY  A.advertiser_id
)

SELECT C.advertiser_id, COALESCE(R.revenue, 0) / C.total_cost AS roi
FROM advertiser_cost C
LEFT JOIN revenue R
ON R.advertiser_id = C.advertiser_id

Example 2: draw histogram by visit session duration

Generate the distribution of the number of users by different session durations, so we can

WITH session_user_duration AS (
    SELECT
        W.session_id,
        W.user_id,
        MAX(D.duration) AS duration
    FROM session_web W
    inner JOIN session_web_duration D
    ON D.session_id = W.session_id
    GROUP BY W.session_id, W.user_id
),
session_user_label AS (
    SELECT session_id,
           CASE WHEN duration < 50 THEN '< 50'
                WHEN duration < 100 THEN '50, 100'
                WHEN duration < 150 THEN '100, 150'
                WHEN duration < 200 THEN '150, 200'
                WHEN duration < 250 THEN '200, 250'
                WHEN duration >= 250 THEN '>= 250'
               ELSE NULL END AS duration_category,
           user_id
    FROM session_user_duration
)
SELECT duration_category, COUNT(DISTINCT user_id)
FROM session_user_label
GROUP BY duration_category
ORDER BY duration_category;

2. Window Functions

Window functions perform calculations across a set of rows related to the current row. They allow you to rank, aggregate, and compute running totals without the need for subqueries or self-joins.

Example 1: Top artist for each country, ranked by their number of streamings.

WITH artist_plays AS (
    SELECT
        S.artist_id,
        P.country,
        SUM(num_plays) num_plays
    FROM song_plays P
    INNER JOIN song S
    ON S.song_id = P.song_id
    WHERE P.date = '2021-01-01'
    GROUP BY 1,2
),

artist_ranking AS (
    SELECT
    artist_id,
    country,
    ROW_NUMBER() OVER(PARTITION BY country ORDER BY num_plays DESC) ranking
FROM artist_plays
)

SELECT country, artist_id
FROM artist_ranking
WHERE ranking = 1;

Example 2: Calculate the cumulative sum of sales by date for daily sales trends to optimize marketing strategies.

SELECT date, sales_amount,
       SUM(sales_amount) OVER (ORDER BY date) AS cumulative_sales
FROM daily_sales;

3. UPSERT

UPSERT is a combination of UPDATE and INSERT. It allows you to insert a new row or update an existing one if there’s a conflict with a unique constraint or primary key.

Example 1: Update page views or insert a new row

Use case: track page views on a website while avoiding duplicate entries.

INSERT INTO page_views (page, views)
VALUES ('homepage', 1)
ON CONFLICT (page)
DO UPDATE SET views = page_views.views + 1;

Example 2: Update product inventory or insert a new product

Use case: Maintain accurate inventory records when receiving new stock.

INSERT INTO inventory (product_id, quantity)
VALUES (101, 10)
ON CONFLICT (product_id)
DO UPDATE SET quantity = inventory.quantity + 10;

4. Recursive Queries

Recursive queries retrieve hierarchical or tree-structured data by repeatedly executing a subquery that refers to itself.

Example 1: Retrieve an employee’s entire management chain

Use Case: Generate an organization chart or analyze management structures.

WITH RECURSIVE management_chain (employee_id, manager_id) AS (
  SELECT employee_id, manager_id
  FROM employees
  WHERE employee_id = 101
  UNION ALL
  SELECT e.employee_id, e.manager_id
  FROM employees e
  JOIN management_chain mc ON e.employee_id = mc.manager_id
)
SELECT * FROM management_chain;

Example 2: Calculate the sum of all child accounts’ balances

Use Case: Analyze the financial health of an organization by aggregating the balances of all child accounts.

WITH RECURSIVE account_tree (account_id, parent_account_id, balance) AS (
  SELECT account_id, parent_account_id, balance
  FROM accounts
  WHERE parent_account_id IS NULL
  UNION ALL
  SELECT a.account_id, a.parent_account_id, a.balance
  FROM accounts a
  JOIN account_tree at ON a.parent_account_id = at.account_id
)
SELECT SUM(balance) FROM account_tree;

5. DynamicSQL

CREATE PROCEDURE filter_employees (column_name VARCHAR, value VARCHAR)
LANGUAGE plpgsql
AS $$
DECLARE
  query TEXT;
BEGIN
  query := 'SELECT * FROM employees WHERE ' || column_name || ' = $1';
  EXECUTE query USING value;
END;
$$;

CREATE FUNCTION sort_data(column_name VARCHAR)
RETURNS TABLE (id INT, name VARCHAR, salary DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT id, name, salary FROM employees ORDER BY ' || column_name;
END;
$$;

6. Materialized View

A materialized view is a database object that contains the results of a query.

The main differences between a materialized view and a regular view lie in how they store and manage data.

Materialized views store a physical copy of the data and can improve query performance, but they can become stale if not refreshed regularly.

In contrast, regular views only store the query definition and always provide up-to-date data, but they may have slower query performance for complex operations.

Example 1: Create a materialized view of the total sales by category

Use Case: Speed up reporting for a dashboard that displays sales metrics.

CREATE MATERIALIZED VIEW total_sales_by_category AS
SELECT category, SUM(sales) AS total_sales
FROM sales
GROUP BY category;

Example 2: Create a materialized view of the average salary by department Use Case: Optimize queries for HR applications that analyze salary data.

CREATE MATERIALIZED VIEW avg_salary_by_department AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

7. UNNEST

The UNNEST function is used to transform a single row with an array or a set of elements into multiple rows, each containing one element from the array or set. It simplifies the process of working with array data types and allows you to perform operations on individual elements.

Example 1: Expand an array of tags associated with a blog post Use Case: Analyze the popularity of tags used in blog posts to optimize content strategy.

CREATE TABLE blog_posts (
  id INT PRIMARY KEY,
  title VARCHAR,
  tags VARCHAR[]
);

INSERT INTO blog_posts (id, title, tags)
VALUES (1, 'Advanced SQL Techniques', ARRAY['sql', 'data analysis', 'database']);

SELECT bp.id, bp.title, tag
FROM blog_posts bp, UNNEST(bp.tags) AS tag;

Example 2: Find users who have a common interest Use Case: Build a recommendation engine for connecting users based on shared interests.

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR,
  interests VARCHAR[]
);
INSERT INTO users (id, name, interests)
VALUES (1, 'Alice', ARRAY['reading', 'movies', 'travel']),
       (2, 'Bob', ARRAY['movies', 'sports', 'cooking']),
       (3, 'Carol', ARRAY['travel', 'hiking', 'photography']);
SELECT u1.name AS user1, u2.name AS user2, interest
FROM users u1, UNNEST(u1.interests) AS interest
JOIN users u2 ON u1.id < u2.id
WHERE interest = ANY(u2.interests);

By using the UNNEST function, you can efficiently work with array data types and perform operations that involve transforming or analyzing individual elements within the array. This technique can be especially helpful in various applications, such as recommendation engines, content analysis, or social network analysis.

Conclusion

Mastering these advanced SQL techniques and strategies will not only help you work more efficiently as a data analyst but also enable you to extract valuable insights from complex datasets.

By understanding common table expressions, window functions, upserts, recursive querying, dynamic SQL, materialized views and unnest, you will be well-equipped to tackle any data analysis challenge that comes your way.

Elevate your data skills with SQLPad — master SQL & Python/Pandas via tailored coding challenges and courses, all in your browser.