Homework 09 — DuckDB Extensions

Task 1 — Add a weekly_repo_growth view

Extend query.py with a third CREATE OR REPLACE VIEW named weekly_repo_growth. The view should, per repository per ISO week, compute the delta of PushEvent count versus the previous week using a window function.

Columns required:

• repo — the repository name (from repo.name)
• iso_week — the ISO week start date (use date_trunc('week', date::date))
• pushes — count of PushEvent rows in that week
• delta — pushes minus the previous week’s pushes for the same repo

Self-check:

• SELECT * FROM weekly_repo_growth WHERE repo = 'torvalds/linux' ORDER BY iso_week DESC LIMIT 4 returns 4 rows, and the most-recent row’s delta column is non-NULL (i.e., there is at least two weeks of data for that repo).
• The first week for a given repo has delta = NULL (there is no prior week to subtract from). That is the expected behaviour — lag with no default returns NULL.

Task 2 — Write the result of top_repos.py to Parquet

Modify top_repos.py to accept an optional --out argument. When --out is provided, write the query result to a Parquet file using DuckDB’s COPY … TO syntax instead of printing to stdout.

python -m duckdb.top_repos \
    --since 2024-01-01 --until 2024-01-07 \
    --n 50 --out /tmp/top.parquet

Use COPY (SELECT …) TO '{out}' (FORMAT PARQUET) inside the open_pipeline_db() connection for the write path. Keep the existing print-to-stdout path when --out is not provided.

Self-check:

python -c "
import duckdb
result = duckdb.read_parquet('/tmp/top.parquet')
print(result.count('*').fetchone()[0])
"

Expected output: 50 (or fewer if fewer than 50 repositories exist in the window).

Task 3 — Benchmark pandas vs DuckDB

Write a standalone script bench_pandas_vs_duckdb.py (anywhere in your working directory) that runs the same count_by_type aggregation twice:

1. pandas path: pd.read_parquet on all partition files concatenated, then .groupby('type').size().
2. DuckDB path: a single SELECT type, count(*) FROM read_parquet(…, hive_partitioning=true) GROUP BY type.

Time both with time.perf_counter. Take the minimum of three runs for each to avoid GC noise. Report the ratio in the form pandas=X.XXXs duckdb=X.XXXs ratio=X.Xx.

Add a one-sentence comment at the bottom of the script (prefixed # COMMENT:) explaining why DuckDB wins on this workload.

Self-check:

• On one day of L02 partitions, DuckDB should be at least 2× faster (often 5–20× depending on row-group size and number of partitions).
• The script exits with code 0 and prints the ratio line.
• Your # COMMENT: line references either column projection, partition pruning, or vectorised execution as the mechanism.