PostgreSQL: Advanced Queries

Focused View

Adam Wilbert

2:50:40

0 View

[1] Introduction

[1] Gain additional insights from your PostgreSQL data.mp4

00:57

[2] What you should know.mp4

00:46

[3] Using the exercise files.mp4

02:35

[2] 1. Obtain Summary Statistics by Grouping Rows

[1] Using GROUP BY to aggregate data rows.mp4

07:09

[2] Obtain general-purpose aggregate statistics.mp4

05:16

[3] Evaluate columns with Boolean aggregates.mp4

04:37

[4] Find the standard deviation and variance of a dataset.mp4

05:18

[5] Include overall aggregates with ROLLUP.mp4

04:14

[6] Return all possible combinations of groups with CUBE.mp4

03:38

[7] Segmenting groups with aggregate filters.mp4

04:57

[8] Challenge Group statistics.mp4

01:07

[9] Solution Group statistics.mp4

07:45

[3] 2. Use Window Functions to Perform Calculations across Row Sets

[1] Create a window function with an OVER clause.mp4

04:11

[2] Partition rows within a window.mp4

04:07

[3] Streamline partition queries with a WINDOW clause.mp4

02:42

[4] Ordering data within a partition.mp4

05:08

[5] Calculate a moving average with a sliding window.mp4

04:52

[6] Return values at specific locations within a window.mp4

07:53

[7] Challenge Leverage window functions.mp4

00:54

[8] Solution Leverage window functions.mp4

05:08

[4] 3. Statistics Based on Sorted Data within Groups

[1] Calculate the median value of a dataset.mp4

06:25

[2] Calculate the first and third quartiles of a dataset.mp4

05:47

[3] Find the most frequent value within a dataset with MODE.mp4

02:57

[4] Determine the range of values within a dataset.mp4

02:13

[5] Challenge Retrieve statistics of a dataset with groups.mp4

00:47

[6] Solution Retrieve statistics of a dataset with groups.mp4

05:39

[5] 4. Ranking Data with Windows and Hypothetical Sets

[1] Rank rows with a window function.mp4

05:59

[2] Find a hypothetical rank.mp4

04:07

[3] View top performers with percentile ranks.mp4

04:59

[4] Evaluate probability with cumulative distribution.mp4

02:54

[5] Challenge Evaluate rankings within a dataset.mp4

00:36

[6] Solution Evaluate rankings within a dataset.mp4

05:46

[6] 5. Define Output Values with Conditional Expressions

[1] Define values with CASE statements.mp4

06:42

[2] Merge columns with COALESCE.mp4

03:49

[3] Convert values to null with NULLIF.mp4

03:10

[7] 6. Additional Querying Techniques for Common Problems

[1] Output row numbers with query results.mp4

02:29

[2] Cast values to a different data type.mp4

02:15

[3] Move rows within a result with LEAD and LAG.mp4

05:47

[4] Use an IN function with a subquery.mp4

04:07

[5] Define WHERE criteria with a series.mp4

04:30

[6] Challenge Calculations across rows.mp4

00:31

[7] Solution Calculations across rows.mp4

04:59

[8] Conclusion

[1] Next steps.mp4

00:58

Description

In this course, database expert Adam Wilbert introduces you to a number of advanced query techniques that you can use to better understand your data. Statistical analyses involve understanding the shape and size of the full data set, and deeper insights can be gained by grouping data together to obtain some very useful metrics. Adam shows you how to create basic groups and apply aggregate calculations, then moves into window functions that create subgroups for more granular analysis. He goes over statistics that are based on sorted data within groups, such as the median value, the first and third quartiles of a dataset, the most frequent value, and more. Adam also covers ranking, hypothetical sets, percentile functions, and conditional expressions for further manipulating query result sets. He concludes with some additional querying techniques that you may find useful in solving common problems.

More details

User Reviews

Rating

average 0

Total votes0

Focused display

PostgreSQL

Adam Wilbert

Instructor's Courses

Linkedin Learning

View courses Linkedin Learning

LinkedIn Learning is an American online learning provider. It provides video courses taught by industry experts in software, creative, and business skills. It is a subsidiary of LinkedIn. All the courses on LinkedIn fall into four categories: Business, Creative, Technology and Certifications. It was founded in 1995 by Lynda Weinman as Lynda.com before being acquired by LinkedIn in 2015. Microsoft acquired LinkedIn in December 2016.