PostgreSQL: Advanced Queries

Introduction

• Gain additional insights from your PostgreSQL data
• What you should know
• Using the exercise files
• Learning with CoderPad

1. Obtain Summary Statistics by Grouping Rows

• Using GROUP BY to aggregate data rows
• Obtain general-purpose aggregate statistics
• Evaluate columns with Boolean aggregates
• Find the standard deviation and variance of a dataset
• Include overall aggregates with ROLLUP
• Return all possible combinations of groups with CUBE
• Segmenting groups with aggregate filters
• Solution: Group statistics with filters

2. Use Window Functions to Perform Calculations across Row Sets

• Create a window function with an OVER clause
• Partition rows within a window
• Streamline partition queries with a WINDOW clause
• Ordering data within a partition
• Calculate a moving average with a sliding window
• Return values at specific locations within a window
• Solution: Leverage window functions

3. Statistics Based on Sorted Data within Groups

• Calculate the median value of a dataset
• Calculate the first and third quartiles of a dataset
• Find the most frequent value within a dataset with MODE
• Determine the range of values within a dataset
• Solution: Retrieve statistics of a dataset with groups

4. Ranking Data with Windows and Hypothetical Sets

• Rank rows with a window function
• Find a hypothetical rank
• View top performers with percentile ranks
• Evaluate probability with cumulative distribution
• Solution: Evaluate rankings within a dataset

5. Define Output Values with Conditional Expressions

• Define values with CASE statements
• Merge columns with COALESCE
• Convert values to null with NULLIF

6. Additional Querying Techniques for Common Problems

• Output row numbers with query results
• Cast values to a different data type
• Move rows within a result with LEAD and LAG
• Use an IN function with a subquery
• Define WHERE criteria with a series
• Solution: Calculations across rows
• Pattern matching with regular expressions

Conclusion

• Next steps