Investigating a Metric Drop

The query below computes the main chart on the language learning app's dashboard. It counts daily active learners, the learners who finished at least one lesson that day, and compares each day with the same weekday one week earlier. LAG(active, 7) reads the value from seven rows before the current row, which here is seven days earlier.

WITH daily AS (
  SELECT day_no, COUNT(DISTINCT learner_id) AS active
  FROM daily_learner_events
  WHERE event_type = 'lesson_complete'
  GROUP BY day_no
), compared AS (
  SELECT day_no, active,
Copy