Project 2: Advanced SQL Analytics

Weight	6% of course grade
Released	Monday, September 21, 2026
Due	Friday, October 23, 2026 at 11:59 PM

---

Goal

Use your dataset (loaded in Project 1) to answer 10 or more analytics questions that require advanced SQL: subqueries, common table expressions, window functions, and recursive CTEs.

Eight of those questions come from the required patterns below, one question per pattern. Pick the remaining two or more yourself.

Each query must show the kind of work a senior data engineer does. Naive solutions using only SELECT … FROM … WHERE … GROUP BY are not sufficient.

---

Project 1 with the Class Scenario

Project 1 asked for two things before any analytics could happen: a schema.sql that creates your tables and a way to load data into them. You should already have both working for your own dataset. If you do not, build them here first, against the small class scenario below, then get your own dataset’s versions running before you start the analytics section.

The class scenario is the university schema from lecture, department, faculty, student, course, section, and enrollment. It is small enough to hold in your head, so the SQL pattern is the only thing left to focus on.

Loading it

The steps below assume a Linux or macOS shell, the same as the rest of this course. You already have a working DATABASE_URL from Project 0 and Project 1, exported the same way you exported it then.

set -a; source .env; set +a

Save the two files below, then run them in order.

psql "$DATABASE_URL" -f class-schema.sql
psql "$DATABASE_URL" -f class-load.sql

class-schema.sql (click to expand)

-- class-schema.sql
-- Creates the university schema from lecture: department, faculty, student,
-- course, section, and enrollment, plus a small standalone
-- faculty_supervision table for the recursive-hierarchy pattern.
-- Safe to run more than once.

DROP TABLE IF EXISTS enrollment CASCADE;
DROP TABLE IF EXISTS section CASCADE;
DROP TABLE IF EXISTS course CASCADE;
DROP TABLE IF EXISTS student CASCADE;
DROP TABLE IF EXISTS faculty CASCADE;
DROP TABLE IF EXISTS department CASCADE;
DROP TABLE IF EXISTS faculty_supervision CASCADE;

CREATE TABLE department (
  dname     text PRIMARY KEY,
  building  text
);

CREATE TABLE faculty (
  name       text PRIMARY KEY,
  dname      text REFERENCES department(dname),
  salary     int,
  hire_date  date
);

CREATE TABLE student (
  student_id  bigint       PRIMARY KEY,
  name        text         NOT NULL,
  major       text,
  gpa         numeric(3,2) CHECK (gpa BETWEEN 0 AND 4.0)
);

CREATE TABLE course (
  course_id  text PRIMARY KEY,
  title      text NOT NULL,
  credits    int
);

CREATE TABLE section (
  course_id    text NOT NULL REFERENCES course(course_id),
  section_num  int  NOT NULL,
  term         text NOT NULL,
  instructor   text REFERENCES faculty(name),
  room         text,
  PRIMARY KEY (course_id, section_num, term)
);

CREATE TABLE enrollment (
  student_id   bigint NOT NULL REFERENCES student(student_id),
  course_id    text   NOT NULL,
  section_num  int    NOT NULL,
  term         text   NOT NULL,
  grade        text,
  PRIMARY KEY (student_id, course_id, section_num, term),
  FOREIGN KEY (course_id, section_num, term) REFERENCES section(course_id, section_num, term),
  CONSTRAINT valid_grade CHECK (grade IS NULL OR grade IN ('A','B','C','D','F'))
);

-- A standalone table for the recursive-hierarchy pattern. It does not
-- connect to the faculty roster above; it is its own small org chart.
CREATE TABLE faculty_supervision (
  faculty_id     bigint PRIMARY KEY,
  name           text NOT NULL,
  supervisor_id  bigint REFERENCES faculty_supervision(faculty_id)
);

class-load.sql (click to expand)

-- class-load.sql
-- Populates the tables class-schema.sql creates, in foreign-key order.
-- Run class-schema.sql first.

INSERT INTO department (dname, building) VALUES
  ('Computer Science',       'E301'),
  ('Computer Engineering',   'E302'),
  ('Mathematics',            'LIT 200'),
  ('Physics',                'NPB 100'),
  ('Electrical Engineering', 'NEB 200');

INSERT INTO faculty (name, dname, salary, hire_date) VALUES
  ('Ada Byron',     'Computer Science',     152000, DATE '2024-08-16'),
  ('Tenured Ted',   'Computer Science',     168000, DATE '2008-01-10'),
  ('Bright Beth',   'Computer Science',     168000, DATE '2020-01-15'),
  ('New Nadia',     'Computer Engineering', 128000, DATE '2025-08-18'),
  ('Mid Marcus',    'Mathematics',          141000, DATE '2017-03-01'),
  ('Grace Hopper',  'Computer Science',     175000, DATE '2015-06-01'),
  ('Rosalind P.',   'Physics',              136000, DATE '2019-09-01');

INSERT INTO student (student_id, name, major, gpa) VALUES
  (1,  'Ada Lovelace',      'CS',      3.90),
  (2,  'Alan Turing',       'CS',      3.70),
  (3,  'Grace Hopper',      'EE',      4.00),
  (4,  'Edsger Dijkstra',   'CS',      2.80),
  (5,  'Barbara Liskov',    'EE',      3.50),
  (6,  'Donald Knuth',      'CS',      4.00),
  (7,  'Radia Perlman',     'CpE',     3.60),
  (8,  'Katherine Johnson', 'Math',    3.95),
  (9,  'Claude Shannon',    'EE',      3.85),
  (10, 'Marie Curie',       'Physics', 3.92);

INSERT INTO course (course_id, title, credits) VALUES
  ('COP5725', 'Database Management Systems',    3),
  ('COP3530', 'Data Structures and Algorithms', 3),
  ('COP5536', 'Advanced Data Structures',       3);

INSERT INTO section (course_id, section_num, term, instructor, room) VALUES
  ('COP5725', 1, 'Fall 2026',   'Ada Byron',   'E301'),
  ('COP5725', 2, 'Fall 2026',   'Tenured Ted', 'E301'),
  ('COP5725', 1, 'Spring 2026', 'Ada Byron',   'E301'),
  ('COP3530', 1, 'Fall 2026',   'New Nadia',   'E302'),
  ('COP5536', 1, 'Fall 2026',   'Tenured Ted', 'E301');

INSERT INTO enrollment (student_id, course_id, section_num, term, grade) VALUES
  (1, 'COP5725', 1, 'Fall 2026',   'A'),
  (2, 'COP5725', 1, 'Fall 2026',   'B'),
  (3, 'COP5725', 2, 'Fall 2026',   'A'),
  (4, 'COP5725', 2, 'Fall 2026',   NULL),
  (6, 'COP5725', 1, 'Fall 2026',   'A'),
  (7, 'COP5725', 1, 'Fall 2026',   'B'),
  (1, 'COP5725', 1, 'Spring 2026', 'A'),
  (5, 'COP5725', 1, 'Spring 2026', 'B'),
  (9, 'COP5725', 1, 'Spring 2026', NULL),
  (2, 'COP3530', 1, 'Fall 2026',   'A'),
  (4, 'COP3530', 1, 'Fall 2026',   'B'),
  (8, 'COP3530', 1, 'Fall 2026',   'A'),
  (1, 'COP5536', 1, 'Fall 2026',   'B'),
  (3, 'COP5536', 1, 'Fall 2026',   'A');

INSERT INTO faculty_supervision (faculty_id, name, supervisor_id) VALUES
  (1, 'Dr. Provost', NULL),
  (2, 'Dr. Dean',    1),
  (3, 'Dr. Chair',   2),
  (4, 'Dr. Grant',   3),
  (5, 'Dr. Sahni',   3),
  (6, 'Dr. Lee',     2);

Run any of the example queries below the same way queries/ worked in Project 1, by pointing psql at a file. Or save a few of them into a scratch folder alongside a copy of run_queries.py (below) and let the script run all of them at once, the same workflow you will use for real in analytics/.

Example queries

Top 2 highest-paid faculty per department, window function (click to expand)

-- top-2-per-department.sql
-- Question: Who are the two highest-paid faculty members in each department?
-- Technique: row_number() over (PARTITION BY ... ORDER BY ...)

SELECT dname, name, salary
FROM (
  SELECT
    dname, name, salary,
    row_number() OVER (PARTITION BY dname ORDER BY salary DESC, name) AS rk
  FROM   faculty
) ranked
WHERE  rk <= 2
ORDER BY dname, rk;

ORDER BY salary DESC, name breaks the tie between Tenured Ted and Bright Beth, both at $168,000, the same tie the aggregation practice problems discuss. Drop the second sort key and PostgreSQL is free to pick either one.

dname	name	salary
Computer Engineering	New Nadia	128000
Computer Science	Grace Hopper	175000
Computer Science	Bright Beth	168000
Mathematics	Mid Marcus	141000
Physics	Rosalind P.	136000

Running payroll total by hire date, window aggregate (click to expand)

-- running-payroll.sql
-- Question: What was the cumulative payroll after each hire, in hire order?
-- Technique: sum() OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SELECT
  name, hire_date, salary,
  sum(salary) OVER (ORDER BY hire_date
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM   faculty
ORDER BY hire_date;

The running total after the last hire lands at $1,068,000, the same grand total the ROLLUP practice problem computes a different way. Matching totals across two techniques is a quick way to check a window frame is right.

Same question, naive and windowed, the a07 pattern (click to expand)

-- a07a-naive.sql
-- Question: Who is the highest-paid faculty member in each department?
-- Technique: correlated subquery

SELECT f.dname, f.name, f.salary
FROM   faculty f
WHERE  f.salary = (SELECT max(salary) FROM faculty f2 WHERE f2.dname = f.dname)
ORDER BY f.dname;

-- a07b-window.sql
-- Question: Who is the highest-paid faculty member in each department?
-- Technique: row_number() over (PARTITION BY ... ORDER BY ...)

SELECT dname, name, salary
FROM (
  SELECT dname, name, salary,
         row_number() OVER (PARTITION BY dname ORDER BY salary DESC) AS rk
  FROM   faculty
) ranked
WHERE  rk = 1
ORDER BY dname;

Both return the same four rows, Grace Hopper, New Nadia, Mid Marcus, and Rosalind P. a07a.sql and a07b.sql in your own submission follow this same split, one file per form, so run_queries.py can run each on its own and time them separately.

Org chart with a recursive CTE (click to expand)

-- org-chart.sql
-- Question: What does the reporting chain look like, top to bottom?
-- Technique: WITH RECURSIVE

WITH RECURSIVE chart AS (
  SELECT faculty_id, name, supervisor_id, 1 AS depth, name AS path
  FROM   faculty_supervision
  WHERE  supervisor_id IS NULL

  UNION ALL

  SELECT f.faculty_id, f.name, f.supervisor_id, c.depth + 1, c.path || ' > ' || f.name
  FROM   faculty_supervision f
  JOIN   chart c ON f.supervisor_id = c.faculty_id
)
SELECT depth, repeat('  ', depth - 1) || name AS indented, path
FROM   chart
ORDER BY path;

This is the same org chart from the recursive-queries lecture, run here against faculty_supervision, the standalone table class-schema.sql includes just for this pattern.

---

Deliverables

cop5725fa26-project/
├── ... (from previous projects)
├── analytics/
│   ├── run_queries.py              # given to you; runs every .sql file here and prints results
│   ├── a01-running-totals.sql
│   ├── a02-top-n-per-group.sql
│   ├── a03-period-over-period.sql
│   ├── a04-cumulative-distribution.sql
│   ├── a05-recursive-hierarchy.sql
│   ├── a06-cte-pipeline.sql
│   ├── a07a-naive.sql              # required: naive form of the same question
│   ├── a07b-window.sql             # required: window-function form
│   ├── a08-deduplication.sql
│   ├── ... 2 or more of your own choosing, a09 onward
│   └── README.md                   # purpose and interesting result per query
└── README.md                       # updated

Required Patterns

Every pattern needs its own query in its own file. One query does not satisfy two patterns, even when it happens to use both features, so a window function inside a CTE pipeline still leaves the top-N and running-total rows unanswered. Eight patterns give you eight questions in nine files, since the naive-vs-window pair writes one question twice, and your own two or more bring the total to ten.

Pattern	Required SQL features
Top-N per group	row_number() or rank() with PARTITION BY
Running total	window aggregate with explicit frame
Period-over-period	lag() or lead()
Cumulative distribution	cume_dist() or percent_rank()
Hierarchical traversal	WITH RECURSIVE (org chart, comment tree, etc.)
Multi-step transformation	A CTE pipeline (WITH a AS (...), b AS (...))
Naive vs window comparison	Same question, naive and windowed, each its own file
Deduplication by key	row_number() = 1 pattern

Each query file should have a header comment naming the question and the technique:

-- a02-top-n-per-group.sql
-- Question: Top 3 most-active users per region per month
-- Technique: row_number() over (PARTITION BY ... ORDER BY ...)
-- Project 2 requirement: top-N per group

WITH ranked AS (
  SELECT
    region, user_id, ym,
    activity_count,
    row_number() OVER (PARTITION BY region, ym ORDER BY activity_count DESC) AS rk
  FROM monthly_activity
)
SELECT region, user_id, ym, activity_count
FROM   ranked
WHERE  rk <= 3
ORDER BY region, ym, rk;

Comment past the header, too. A -- line above a CTE or a window clause costs one line and saves you from re-deriving the query’s logic when you revisit it in three weeks.

The Naive-vs-Window Query (a07a, a07b)

Write the same question two ways, one file per form, so run_queries.py can run each on its own.

-- a07a-naive.sql
-- Question: <your question>
-- Technique: correlated subquery or self-join

SELECT ... ;

-- a07b-window.sql
-- Question: <your question>
-- Technique: window function

SELECT ... ;

Capture the timing of both with EXPLAIN ANALYZE and put the numbers in analytics/README.md.

The window form will be 10-100× faster in most cases. Show this concretely.

run_queries.py

A script we have already written. Copy it into analytics/run_queries.py unchanged. It reads every .sql file in its own folder, runs each one against DATABASE_URL, and prints the question, the result, and the row count to the terminal.

"""Run every SQL file in this folder and print its results.

Usage:
    uv run --env-file .env analytics/run_queries.py
"""

import os
from pathlib import Path
import sys
import time

from loguru import logger
import psycopg


def run_query(cursor: psycopg.Cursor, path: Path) -> None:
    """Run one .sql file and print its result set.

    Args:
        cursor: An open psycopg cursor.
        path: Path to the .sql file to run.
    """
    sql = path.read_text()
    question = next(
        (line.split("Question:", 1)[1].strip() for line in sql.splitlines() if "Question:" in line),
        None,
    )

    start = time.perf_counter()
    cursor.execute(sql)
    elapsed_ms = (time.perf_counter() - start) * 1000

    print(f"\n=== {path.name} ({elapsed_ms:.1f} ms) ===")
    if question:
        print(question)
    if cursor.description is None:
        print("(statement produced no result set)")
        return

    columns = [col.name for col in cursor.description]
    rows = cursor.fetchall()
    widths = [len(name) for name in columns]
    for row in rows:
        for i, value in enumerate(row):
            widths[i] = max(widths[i], len(str(value)))

    print("  ".join(name.ljust(w) for name, w in zip(columns, widths)))
    for row in rows:
        print("  ".join(str(v).ljust(w) for v, w in zip(row, widths)))
    print(f"({len(rows)} row{'s' if len(rows) != 1 else ''})")


def main() -> None:
    """Run every *.sql file next to this script, in filename order."""
    database_url = os.environ.get("DATABASE_URL")
    if not database_url:
        logger.error("DATABASE_URL is not set. Run with: uv run --env-file .env analytics/run_queries.py")
        sys.exit(1)

    sql_files = sorted(Path(__file__).parent.glob("*.sql"))
    if not sql_files:
        logger.warning("No .sql files found next to run_queries.py")
        return

    logger.info(f"Running {len(sql_files)} quer{'y' if len(sql_files) == 1 else 'ies'} in {Path(__file__).parent}")
    with psycopg.connect(database_url) as conn:
        with conn.cursor() as cursor:
            for path in sql_files:
                run_query(cursor, path)


if __name__ == "__main__":
    main()

It imports loguru for its own progress messages. Add it once if load.py did not already:

uv add loguru

Run every query with:

uv run --env-file .env analytics/run_queries.py

analytics/README.md

A second README, scoped to this folder, so a reader can understand every query without opening the .sql file first. One section per query, in file order.

## a01: <short title>

- Question: <the one-sentence question this query answers>
- Purpose: <why your stakeholder would ask it>
- Technique: <the advanced SQL feature this query demonstrates>
- Sample result: <the first 3-5 rows the query returned, pasted as a small table>
- Interesting result: <what your data actually showed>

Do not commit exported result files; there is no results/ folder in this project. A few rows pasted under Sample result show us what the query returns, and we run run_queries.py ourselves against your database for the rest.

Question restates the header comment from the .sql file. Purpose is the justification: name the stakeholder from your design scenario and say what decision this query would inform. Technique names the advanced SQL feature from the Required Patterns table. Interesting result is not the question restated as a sentence; it is what the numbers showed once you ran the query against your data, including a result you did not expect.

For a07a and a07b, add the two EXPLAIN ANALYZE timings and a sentence on why they differ.

Updated README.md

Add a short “Project 2 Analytics” section:

• What your queries cover, in a sentence or two
• How to run them: uv run --env-file .env analytics/run_queries.py
• A pointer to analytics/README.md, where each query’s purpose and interesting result live

---

Submission

Tag the commit you want graded v2 and push it with main by October 23.

git add analytics/ README.md
git commit -m "Project 2: advanced SQL analytics"
git tag v2
git push origin main v2

We grade the commit the v2 tag points at, not the tip of main, so anything you push after tagging goes unread. To replace a tag before the deadline, delete it on both sides and tag again:

git tag -d v2
git push origin :refs/tags/v2
git tag v2
git push origin main v2

---

Grading Rubric

100 points total. The staff grade the v2 tag by running run_queries.py against your database, then reading analytics/README.md and the README.

Component	Points	Criteria
Query coverage	30	All required patterns present and correct
Query craftsmanship	20	Clean SQL; readable formatting; appropriate window frames
Naive vs window (a07a, a07b)	15	Both forms work; timings captured; explanation accurate
Analytics script and README	15	run_queries.py runs end to end against every file in analytics/; analytics/README.md states a purpose and an interesting result for each query
Documentation	10	README explains the analytical questions clearly
Repo hygiene	10	Tag v2; commit history; one query per file

---

Common Pitfalls

• Window functions in WHERE (they evaluate after; wrap in subquery)
• last_value() without explicit frame (returns current row; use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
• Recursive CTE without termination (use a depth bound or path array)
• Reusing Project 1’s queries with light tweaks — they should be new analytical questions
• An analytics/README.md that restates the question instead of saying what the result showed

---

FAQ

Q: Can I use DuckDB for Project 2? A: Yes. DuckDB supports all the same window functions and recursive CTEs as PostgreSQL. Some students find DuckDB faster for large analytical queries. run_queries.py connects with psycopg, so DuckDB users can swap the connection block for duckdb.connect(), or just run each file directly with the duckdb CLI.

Q: My dataset doesn’t have a natural hierarchy. How do I write a recursive CTE? A: Most datasets have some hierarchy: timestamps form a sequence (Fibonacci-style numeric sequence), categories form parent-child relationships, threads form trees, social networks form graphs. Be creative.

Q: My naive query is faster than the window function version! A: That is great! Document it honestly. Sometimes the simpler form is better. The optimizer is smart.

---

back