Homework 07 — SQL Exercises

Lesson 07 · Data Engineering with Python · 3 exercises

All three tasks run against the same GitHub events Parquet partitions used in the lab (data-engineering/sample-pipeline/data/parquet/date=*/*.parquet). Open DuckDB and recreate the events view before you start:

CREATE OR REPLACE VIEW events AS
SELECT * FROM read_parquet(
    'data/parquet/date=*/*.parquet',
    hive_partitioning => true
);

Task 1 — Sessions: detect a session boundary with LAG

Define a session as a stretch of one actor's events where no consecutive gap exceeds 30 minutes. Write a single query (you may use multiple CTEs) that returns:

actor
session_id (a sequential number per actor, starting at 1)
session_start — timestamp of the first event in the session
session_end — timestamp of the last event in the session
n_events — number of events in the session

Order the result by session_start DESC.

Hint: use LAG(created_at) OVER (PARTITION BY actor ORDER BY created_at) to compute the gap to the previous event. Flag rows where the gap exceeds 30 minutes (or where the previous event is NULL) as a session start. Then use a running SUM over the start-flag to assign a session ID per actor.

✓ Self-check: the row count should be far smaller than the total event count (sessions aggregate many events). Spot-check one actor's sessions by comparing the raw rows from WHERE actor = '<name>' ORDER BY created_at against the session boundaries you computed.

Task 2 — Top-K with ties: actors tied at rank 1

Write a query that returns all actors tied for the highest total event count (e.g., if three actors each have 142 events and that is the maximum, return all three rows). Use RANK() — not ROW_NUMBER() — so ties are preserved, and filter with QUALIFY.

The result should include actor and n (the event count), ordered by actor.

✓ Self-check: explain in 2–3 sentences why using ROW_NUMBER() = 1 would give the wrong answer (it silently drops tied actors and returns exactly one row regardless of ties). Your explanation should mention how ROW_NUMBER handles ties differently from RANK.

Task 3 — GROUPING SETS dashboard query

Write a single query that returns all four of the following aggregates in one result set:

Total events per day.
Total events per (day, type) combination.
Total events per type across all days.
Grand total across all days and types.

Tag each row with two boolean flag columns:

day_total — TRUE when the day dimension is rolled up.
type_total — TRUE when the type dimension is rolled up.

A downstream renderer can substitute "All days" / "All types" for NULL based on these flags.

✓ Self-check: the detail rows (day + type) should have both flags FALSE; the grand-total row should have both flags TRUE; there should be exactly one row with both flags TRUE.

View solutions →