SQL for Data Analysis: Querying, Aggregating, and Window Functions

You will learn how to extract and transform data from databases to answer business questions. The course starts with basic data retrieval and moves through connecting tables and summarizing information. You will finish by using window functions to perform complex calculations like running totals and rankings. By the end, you will be able to write efficient queries that turn raw data into clear insights for reporting and trend analysis.

Getting specific data from a table

The learner will be able to write queries that filter and sort data from a single table.

Key takeaways

  • The WHERE clause filters rows by checking if they meet specific conditions like equality or range.
  • Text values in a query must be enclosed in single quotes while numeric values do not need them.
  • The ORDER BY clause organizes your results in ascending or descending order based on one or more columns.
  • The LIMIT clause restricts the total number of rows returned by a query to make large datasets manageable.

Sources

Combining data from different tables

The learner will be able to use joins to connect related information across multiple tables.

Key takeaways

  • Inner joins return only the rows where a match exists in both tables.
  • Left joins keep all records from the first table even if no match is found in the second.
  • Joins use primary and foreign keys to connect related information across different tables.
  • Missing join conditions or multiple matches can cause row explosion where the result set grows too large.

Sources

Summarizing data into groups

The learner will be able to calculate totals and averages for specific categories of data.

Key takeaways

  • Aggregate functions like SUM and AVG turn many rows of data into a single summary number.
  • The GROUP BY clause organizes rows into categories so you can perform calculations on each group.
  • The WHERE clause filters individual rows before they are grouped, while the HAVING clause filters the final summarized results.
  • Every column in your SELECT statement must be either an aggregate calculation or included in the GROUP BY list.

Sources

Organizing complex queries with CTEs

The learner will be able to break down long queries into readable steps using Common Table Expressions.

Key takeaways

  • A Common Table Expression is a named temporary result set that makes complex queries easier to read.
  • You can chain multiple CTEs together by separating them with commas to build a step-by-step data pipeline.
  • Recursive CTEs allow you to query hierarchical data like family trees or company organization charts.
  • Most modern databases run CTEs and subqueries with similar speed because the query optimizer handles both efficiently.

Sources

Calculating values across rows with window functions

The learner will be able to perform calculations like running totals and rankings without losing individual row details.

Key takeaways

  • Window functions perform calculations across related rows while keeping every individual row in the final output.
  • The PARTITION BY clause divides data into groups so the calculation can start over for each subset.
  • Adding an ORDER BY clause inside the window creates a sequence for running totals and ranking operations.
  • Ranking functions like ROW_NUMBER and RANK help identify top items within a group without hiding other data.

Sources

Making queries run faster

The learner will be able to identify and fix slow queries to handle large amounts of data efficiently.

Key takeaways

  • Use execution plans to see if the database is reading every row or using an index.
  • Avoid using functions on columns in your filters so the database can use indexes.
  • Filter data as early as possible to reduce the amount of work the database does during joins.
  • Keep database statistics updated so the optimizer can choose the best path for your query.

Sources