Skip to content

Week 10: Subqueries and Window Functions

Due: Wednesday, Oct 28th

Tuesday's class covers subqueries and Thursday's covers window functions. Both appear on HW5 and on Exam 2.

Prompt

Paste the standard prompt, then paste this block.

Topic: Subqueries and window functions.

My learning objectives:
- Write a subquery in the FROM clause and in the WHERE clause,
  and explain the difference between the two.
- Explain how a window function differs from an aggregate
  function, given the same data.
- Rank and compare rows using OVER and PARTITION BY.

Quiz me on these objectives. Give me one short problem at a
time and wait for my answer, and don't explain anything unless
I get it wrong or ask you to.

Sources

The PostgreSQL page is short and dense. Read the first example slowly, and pay attention to the sentence explaining that a window function does not collapse rows the way GROUP BY does. That single difference is the whole idea.