Gain additional insights from your PostgreSQL data

What you should know

Using the exercise files

Include overall aggregates with ROLLUP

Obtain general-purpose aggregate statistics

Using GROUP BY to aggregate data rows

Evaluate columns with Boolean aggregates

Return all possible combinations of groups with CUBE

Find the standard deviation and variance of a dataset

Segmenting groups with aggregate filters

Solution Group statistics

Challenge Group statistics

Streamline partition queries with a WINDOW clause

Ordering data within a partition

Calculate a moving average with a sliding window

Partition rows within a window

Challenge Leverage window functions

Return values at specific locations within a window

Solution Leverage window functions

Calculate the median value of a dataset

Find the most frequent value within a dataset with MODE

Calculate the first and third quartiles of a dataset

Determine the range of values within a dataset

Challenge Retrieve statistics of a dataset with groups

Solution Retrieve statistics of a dataset with groups

Rank rows with a window function

Find a hypothetical rank

View top performers with percentile ranks

Challenge Evaluate rankings within a dataset

Evaluate probability with cumulative distribution

Solution Evaluate rankings within a dataset

Define values with CASE statements

Merge columns with COALESCE

Convert values to null with NULLIF

Cast values to a different data type

Output row numbers with query results

Move rows within a result with LEAD and LAG

Use an IN function with a subquery

Challenge Calculations across rows

Solution Calculations across rows

Define WHERE criteria with a series

Next steps