Skip to content

GP4: Queries and Views

Due: Monday, Nov 9th

In GP1 you wrote the questions your feature should answer. In GP4 you will answer them, on the data every team loaded in GP3. This is the same work you did in HW5, on your own tables, with two differences:

  1. Your queries may read another team's tables, as the System Description has promised since GP1.
  2. Every query has to run from Python, because GP5 serves them as endpoints.

Objectives

  • Turn the team's questions into precise analytical queries, choosing outer joins, subqueries, aggregation, or window functions as each question requires.
  • Read another team's tables to answer a question that your own tables cannot, without writing to them.
  • Define complex queries as views, and decide where an index or a materialized view would be justified.
  • Write queries as SQLModel functions, and give each view a function that returns its rows as SQLModel objects.
  • Show that each query returns a meaningful answer on the loaded data, in SQLite and in PostgreSQL.

What to commit

In your team's directory, src/planner/<area>/:

  • queries.sql – your views, at least one complex view per person
  • queries.py – at least one SQLModel query per person, plus one function for each view
  • load.py, models.py, and data/ – the fixes to your GP3 data, described below
  • schema.dbml and schema.png – updated wherever the tables changed
  • README.md – the "Proposed queries" section, revised as described below

With four people, that is at least four views and four SQLModel queries. Each person writes one of each and commits them; put your name in the comment above each one.

To start, one person on the team downloads the templates queries.sql and queries.py into your team's directory, replaces every <area> with your area, and commits and pushes them; everyone else pulls. Each template has four stubs, one per person, and passes the checks as it is.

Before you start

Fix the data first

The GP3 feedback lists the problems in your data that make your queries trivial or empty: tables with no rows, columns that are always the same, and rows that contradict each other. Fix those first, since a query on broken data is graded on what it returns. The fixes are not graded separately, but a view that returns nothing, or the same answer for every input, loses most of its credit.

One gap is in the core data rather than yours: the 2024 catalog has no general education area pages. Treat the 2025 area pages as 2024's, as GP3 says to do for courses, and say so in your README.

What day it is

From now on, "today" is Sunday, Nov 1, 2026, and the current term is Fall 2026 (1268). Nothing generated happens after that date, and every question about "now" (open cycles, outstanding recommendations, the current advisor) means that date.

In Python, import the two values rather than typing them, once they are on main (see the next section):

from planner.core import CURRENT_TERM, TODAY

SQL cannot import anything, so in a view, join to term on term_code = 1268, or write the date as '2026-11-01'. Do not use CURRENT_DATE or now(), which change every day and differ between SQLite and PostgreSQL.

Reading across teams

Your queries and views may read any team's tables. Your load.py may now read tables too, but only from a team that loads before you. scripts/load.py runs the teams in this order, which is the order of AREAS in scripts/build.py:

  1. catalog
  2. requirements
  3. plans
  4. advising
  5. preferences
  6. scheduling

When your load.py reads another team's table, check that the table has rows, and raise an exception if it is empty. An empty table usually means the teams loaded in the wrong order, and without the exception your load would quietly build nothing and still pass the check.

Three rules have not changed: no team writes to another team's tables, no table declares a foreign key into another team's tables, and only the core's tables are yours to reference from models.py. When a column copies a value from another team at load time, its note in schema.dbml says so.

The requirements team owns which programs a student is in. There are three programs: the CS major, the CS minor, and the IT major. A student is in one or two of them, in every combination except the CS major with the CS minor. Some CS and IT students are also in the Robotics minor. Loading the Robotics requirements is optional in GP4 and required in GP5. Plans, advising, and scheduling read student_program and program.code in their load.py, rather than guessing, as the GP3 feedback describes.

The early merge

The requirements team goes first, since three teams read its tables:

  • Wednesday, Nov 4, 11:59 pm: the requirements team opens a pull request from requirements/gp4 with only the student_program and program.code fixes.
  • Wednesday, Nov 4, 11:59 pm: the plans team opens a pull request from plans/gp4 with only the fix to the session.flush() in load.py, which makes everyone's load into PostgreSQL take minutes instead of seconds (see the Nov 3 slides).
  • Thursday, Nov 5, before class: I merge those pull requests, along with a small update to the core: scripts/build.py starts building every team's queries.sql, and planner.core gains TODAY and CURRENT_TERM.
  • Thursday, Nov 5, in class: every team merges main into its own gp4 branch. We'll do that step together.

The requirements team then keeps committing to the same branch and opens its final pull request from it as usual. Until Thursday, write code that reads whatever student_program holds now.

The other shared vocabularies in the GP3 feedback, the attribute codes and the room types, are due in GP5.

The views

A view is a named query that the database stores and runs whenever someone selects from it. Put yours in queries.sql, one after another, each with a comment saying what question it answers and who wrote it:

-- Which active students have logged in least this term, within each catalog year?
-- Author: Pat Doe
DROP VIEW IF EXISTS advising_quiet_student;
CREATE VIEW advising_quiet_student AS
WITH login_count AS (
    SELECT l.person_id, COUNT(*) AS logins
    FROM login_event AS l
    JOIN term AS t ON t.term_code = 1268
    WHERE l.succeeded AND l.occurred_at >= t.start_date
    GROUP BY l.person_id
)
SELECT s.person_id, p.first_name, p.last_name, s.catalog_year,
    COALESCE(c.logins, 0) AS logins,
    RANK() OVER (PARTITION BY s.catalog_year ORDER BY COALESCE(c.logins, 0)) AS quiet_rank
FROM student AS s
JOIN person AS p ON p.person_id = s.person_id
LEFT JOIN login_count AS c ON c.person_id = s.person_id
WHERE s.status = 'active';

Complex means at least two of these: an outer join, a subquery or WITH clause, GROUP BY with HAVING, a window function, or a set operation. The example has three. A complex view also reads at least three tables, and answers a question from your README rather than one invented to use a feature.

Names. A view shares one namespace with every table in the database, so start its name with your area, as you did for enum types: advising_quiet_student, not quiet_student.

Leave out the parameters. A view cannot take an argument such as "for a given advisor." Write the view for every advisor, include the advisor's id as a column, and let the Python function choose one.

Keep it portable. The check builds your views in SQLite, and the class database is PostgreSQL, so a view must run in both:

  • Joins, subqueries, WITH, CASE, COALESCE, window functions, and set operations all work in both.
  • Compare dates with '2026-11-01'-style strings, but do no date arithmetic in SQL: date() and julianday() exist only in SQLite, and INTERVAL only in PostgreSQL. Do that arithmetic in Python instead.
  • Integer division truncates in both, so write 100.0 * part / whole for a percentage.
  • CAST('499A' AS integer) is an error in PostgreSQL and 499 in SQLite, so never cast text that might contain letters.
  • Start each view with DROP VIEW IF EXISTS, since SQLite has no CREATE OR REPLACE VIEW.

Order. A view may read your own views if they come earlier in the file, and another team's views if that team comes earlier in the list above.

The Python functions

queries.py holds two kinds of function, and every one takes the session as its first argument.

One SQLModel query per person. This query must have a join, plus an aggregate, a subquery, or an outer join, and must be written with select() rather than SQL text. Use the Nov 5 notes as a reference.

def logins_by_kind(session: Session, term_code: int = CURRENT_TERM) -> list[tuple[EmployeeKind, int]]:
    """Successful logins this term for each kind of employee, most first."""
    term = session.get(Term, term_code)
    logins = func.count(LoginEvent.login_id).label("logins")
    statement = (
        select(Employee.kind, logins)
        .join(LoginEvent, LoginEvent.person_id == Employee.person_id)
        .where(LoginEvent.succeeded, LoginEvent.occurred_at >= term.start_date)
        .group_by(Employee.kind)
        .order_by(logins.desc())
    )
    return list(session.exec(statement))

One function for each view. SQLModel has no class for a view, so write one, without table=True, with a field for each column the view returns. Then select from the view with text(), and turn each row into an object:

class QuietStudent(SQLModel):
    """One row of advising_quiet_student."""

    person_id: int
    first_name: str
    last_name: str
    catalog_year: int
    logins: int
    quiet_rank: int


def quiet_students(session: Session, catalog_year: int) -> list[QuietStudent]:
    """Active students in one catalog year, fewest logins this term first."""
    sql = text(
        "SELECT * FROM advising_quiet_student"
        " WHERE catalog_year = :year ORDER BY quiet_rank"
    )
    rows = session.exec(sql, params={"year": catalog_year}).mappings()
    return [QuietStudent.model_validate(row) for row in rows]

Do not give this class table=True. That would tell create_all() to make a table named after the view, and the view would fail to build.

The parameter goes in with :year, never with an f-string, for the same reason as in HW2: text pasted into SQL is an injection. These classes will be your response models in GP5, so give them names someone else would understand.

Run them all. End queries.py with a block that calls every function on planner.db and prints a few rows of each, so that a reviewer can run python -m planner.<area>.queries and see every answer at once.

Indexes and materialized views

Pick the view your team expects to be slowest as the data grows, and look at it in PostgreSQL with EXPLAIN ANALYZE, as in the Nov 3 notes. If an index on one of your own tables helps, add it in models.py, with index=True or an Index in __table_args__, and say so in schema.dbml. Then decide whether the view should be materialized: how often its data changes, how stale an answer may be, and who would refresh it.

Write what you found in a short "Performance" section of your README, with the times before and after. Do not put a materialized view in queries.sql, since SQLite has none.

Checking the answers

Until Thursday's merge, build your views by hand: in DB Browser, open planner.db, paste queries.sql into the Execute SQL tab, and run it. After the merge, python scripts/load.py --drop builds them for you, and the check fails if any view does not build.

Before you open your pull request, load everything into your own schema on PostgreSQL, as in GP3, and run your functions there too:

DATABASE_URL=postgresql+psycopg://username@localhost/sec1 python scripts/load.py --drop
DATABASE_URL=postgresql+psycopg://username@localhost/sec1 python -m planner.<area>.queries

The two databases should give the same answers. If they do not, find out why before a reviewer does.

Updating the README

Rename "Proposed queries" to "Queries". After each question, name the view or function that answers it, and its author. A question with no answer stays, struck through, with one sentence saying why: the data cannot answer it yet, or another team's table does not exist. Then add the "Performance" section described above, after "Data".

Grading criteria

I will run your functions and read your SQL looking for the following, roughly in order of weight:

  • The answers are right. Each query answers the question it claims to, on the loaded data, in both databases. An answer that is empty, constant, or obviously wrong counts against the query, even when the SQL is fine.
  • The queries are worth asking. They come from your README, use the tools the question needs and no more, and include at least one that reads another team's tables.
  • Every person wrote a view and a query. At least one complex view and one SQLModel query per person, each with its author named and committed by that person.
  • Every view runs from Python. A class and a function for each view, with parameters bound rather than pasted, and a __main__ block that runs them all.
  • The rules are followed. Portable SQL, view names that start with your area, no CURRENT_DATE, and reading across teams only in the order above.
  • The performance section is real. It reports times you measured and a decision you can defend.

I am also looking at whether the GP3 data was fixed, at the commit history, and at how your team responds to the review.

Submission and review

Switch to your <area>/gp4 branch, commit as you go, and open a pull request by the deadline. See the GitHub Workflow for how branches, pull requests, and reviews work, and Code Reviews for what the reviewers are asked to look at.