Skip to content

Window Functions on TPC-H

TPC logo

In this lab you will use window functions to compute shares, ranks, and changes from one row to the next, on the tpch database. You have 30 minutes. Problems 1–4 are the goal; Problem 5 is for anyone who finishes early. After the lab, use the rest of class for HW5 or for your team's GP3 review.

Setup

  1. Start your SSH tunnel, open pgAdmin, and open the Query Tool on the tpch database.
  2. Download windows.sql and open it in the Query Tool (the folder icon, or Ctrl+O).
  3. Type your name at the top.

Everything you need is in the file: each problem is a comment that says what to write and how many rows to expect, and the top of the file lists the tables and columns you will use. Write each query under its problem, highlight it, and press F5 to run it. Some problems also ask a question; answering it in a comment is optional.

The TPC-H Schema and the slides are there if you need them.

Submission

Submit your file windows.sql via Gradescope by the end of the day. The autograder only checks that the file has the kinds of queries the problems ask for; it does not run your queries. The queries themselves are checked by hand.