Homework 07 — Solutions

Lesson 07 · Data Engineering with Python · worked solutions

All queries assume the events view has been created as shown in the homework preamble, pointing at data-engineering/sample-pipeline/data/parquet/date=*/*.parquet with hive_partitioning => true.

Solution to Task 1 — Sessions with LAG

WITH gaps AS (
  SELECT
    actor,
    created_at,
    LAG(created_at) OVER (PARTITION BY actor ORDER BY created_at) AS prev_at
  FROM events
),
flagged AS (
  SELECT
    actor,
    created_at,
    CASE
      WHEN prev_at IS NULL
        OR EXTRACT(EPOCH FROM (created_at - prev_at)) > 1800
      THEN 1 ELSE 0
    END AS is_session_start
  FROM gaps
),
sessioned AS (
  SELECT
    actor,
    created_at,
    SUM(is_session_start) OVER (
      PARTITION BY actor ORDER BY created_at
    ) AS session_id
  FROM flagged
)
SELECT
  actor,
  session_id,
  MIN(created_at) AS session_start,
  MAX(created_at) AS session_end,
  COUNT(*) AS n_events
FROM sessioned
GROUP BY actor, session_id
ORDER BY session_start DESC;

The query builds the result in four logical steps, each a named CTE:

1. gaps — attach the previous event's timestamp to each row using LAG.
2. flagged — mark each row as a session start if the gap to the previous event exceeds 1800 seconds (30 minutes), or if there is no previous event (prev_at IS NULL).
3. sessioned — assign a session ID per actor using a running SUM over the start-flag. Every time a new session starts, the sum increments by 1.
4. Final SELECT — aggregate within each (actor, session_id) group to compute start, end, and event count.

Solution to Task 2 — Top-K with ties

SELECT
  actor,
  COUNT(*) AS n
FROM events
GROUP BY actor
QUALIFY RANK() OVER (ORDER BY COUNT(*) DESC) = 1
ORDER BY actor;

QUALIFY filters after the window function, so there is no need for a CTE. RANK() assigns the same rank to all tied actors, meaning every actor with the maximum event count receives rank 1 and is kept in the result.

Solution to Task 3 — GROUPING SETS dashboard

SELECT
  date_trunc('day', created_at) AS day,
  type,
  COUNT(*) AS n,
  GROUPING(date_trunc('day', created_at)) = 1 AS day_total,
  GROUPING(type) = 1 AS type_total
FROM events
GROUP BY GROUPING SETS (
  (date_trunc('day', created_at), type),
  (date_trunc('day', created_at)),
  (type),
  ()
)
ORDER BY day NULLS LAST, type NULLS LAST;

GROUPING(col) returns 1 when the column was rolled up by the grouping set and 0 otherwise. Comparing it to 1 (i.e., GROUPING(...) = 1) yields a boolean flag. The ORDER BY ... NULLS LAST clause pushes the rollup rows (which have NULL for the rolled-up dimension) to the bottom of the result.