These questions test your knowledge of the Over Command.
Read on to Learn about Over
Over is useful in limiting or tweaking the data returned from aggregate functions
Think of over as like a running total. For instance in our runners table, we have a query that selects name and average weight of all the runners seen so far when ordered by name. Here average_weight is recomputed on each 'step' of the SQL output
select name, weight, avg(weight) over (order by name) from runners order by name limit 3
Partition by allows us to further subdivide the preceeding Over Command
Here avg_weight is recomputed on every 'step' of the SQL like above BUT it is reset when the partition by field changes.
In this case we partition by country (think of it as grouping by country). There is a different aggregate function for each country
select name, weight, country, avg(weight) over (partition by country order by name) from runners order by name
Preceeding and Following
Preceeding and Following allow us to perform aggregate functions on the rows just before and after the current row.
Here we will list the weight and the minimum weight of each runner and their neighbours just before and after them.
min(andy) = min(50, 100) min(bob) = min(50, 100, 50) min(cedric) = min(100, 50, 70) min(dave) = min(50, 70, 70) min(eric) = min(70, 70)
select name, weight, min(weight) over (order by name ROWS between 1 preceeding and 1 following) from runners order by name