SQL for Data Analytics

Explore SQL fundamentals and learn to access and retrieve data in databases using SQL, illustrated with the Billboard Top 100 year end table and basic table concepts.

Use limit clause to view first few rows of a table, avoiding loading entire dataset. Place limit at end of query and adjust from 10 to 20 rows.

Filter data with the where clause using comparison operators, applying conditions like year greater or equal than 2011 and top five songs per year.

Apply the like operator to search patterns with % for multiple characters, _ for a single character, and use I like for case-insensitive results, including starts with or contains Taylor.

Sort data using order by clause to arrange by artist name or year_rank, ascending or descending, and by multiple columns. Use where and limit to filter, and substitute column numbers.

Explore count and sum as aggregate functions, counting rows with count(*), count(col), and handling nulls; apply sum to numerical columns like volume, avoiding non-numerical data such as date.

Master the group by clause to segment data by year and month, leveraging where and order by, and compute metrics like average stock price with line chart visualizations.

Filter aggregated data with the having clause after a group by, unlike where which filters row-level data; see how to keep only months where Apple stock price exceeds 400 dollars.

Explore why relational databases split data into separate tables, linking accounts and orders with account_id. Learn how joins improve organization, performance, and data integrity by avoiding redundancy and update-delete risks.

Learn how inner join connects two tables to return only matching records via the on clause and join keys. See practical examples using the user and likes tables and aliasing.

Apply left and right joins to include all records from the left or right table, with nulls for unmatched rows, illustrated by users, likes, accounts, and orders.

Learn to combine data with joins and filter results, using left joins to identify accounts with no orders by filtering where right table values are null.

Learn to use joins and aggregation in SQL for data analytics to build sales dashboards, analyze total sales by month, top accounts, and regional performance.

Learn how full outer joins return all matches and unmatched rows from both tables, and how this differs from inner joins and unions.

Learn how to combine two data sets with union and union all, following rules for number of columns, similar data types, and column order, and understand how duplicates are handled.

Master self-join and subjoined techniques in SQL to compare rows within the same table, using aliases for hierarchical relationships and time-series patterns, such as orders within 30 days.

Explore how subqueries power multi-step data analysis, from aggregating daily spend by account to filtering by a single value, with joins, unions, and performance improvements.

Explore window functions, or analytic functions, that compute values over rows without joins, enabling cumulative totals and moving averages for time series. Useful for attribution, funnel analysis, retention, and segmentation.

Learn to compute a cumulative sum with window functions in sql for data analytics, using over, partition, order by, and window frame clause to produce running totals by date.

learn how row_number, rank, and dense_rank compute ordered rankings within a window partition and identify the top 10 accounts by region based on sales quantity.

Learn to apply window functions with aggregates like sum, average, min, max, and count across partitions, using partition by and order by date to compute running totals.

Explore how to use the window frame clause to compute running totals, moving averages, and min/max values across a partition.

Explore using lag and lead to compare sales quantities against previous or next rows, compute day over day growth, and analyze two day prior trends in SQL for data analytics.