Time-Series SQL: Date Spines, Sessions, Streaks, Cohorts and Funnels

Dates are text in SQLite. ISO strings like '2024-06-01' sort correctly as text. date(d, '+1 day') adds a day, strftime('%Y-%m', d) truncates to a month, julianday(a) - julianday(b) is the number of days between two dates, and strftime('%s', ts) is seconds since 1970, which is exact for time-of-day math.

A date spine fills the holes. Days with no rows do not exist in the data, so a chart or a GROUP BY skips them. Generate every day with a recursive CTE, then LEFT JOIN the data to it. COUNT of a column from the data side is 0 on an empty day.

WITH RECURSIVE days(d) AS (
  SELECT '2024-06-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM days WHERE d < '2024-06-07'
)
SELECT d, COUNT(s.day) AS sales
FROM days LEFT JOIN sales s ON s.day = d
GROUP BY d;

Sessionization turns events into visits. A new session starts at a user's first event, and again whenever the gap since their previous event exceeds a threshold. Flag those rows with LAG, then add the flags up to number the sessions or count them.

WITH flagged AS (
  SELECT user_id, ts,
         CASE WHEN LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL
                OR strftime('%s', ts) - strftime('%s', LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)) > 1800
              THEN 1 ELSE 0 END AS starts_session
  FROM clicks
)
SELECT user_id, SUM(starts_session) AS sessions FROM flagged GROUP BY user_id;

Gaps and islands find streaks. Number each user's days with ROW_NUMBER. Along an unbroken run the date and the row number both go up by one, so date minus row number is constant, and it jumps after every gap. Group by that constant to get each streak, then take the longest.

WITH numbered AS (
  SELECT user_id, day,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS rn
  FROM logins
), streaks AS (
  SELECT user_id, COUNT(*) AS len
  FROM numbered
  GROUP BY user_id, date(day, '-' || rn || ' days')
)
SELECT user_id, MAX(len) AS longest FROM streaks GROUP BY user_id;

Cohorts group people by when they started. Label each person with their starting month, then count how many did something within a window after that start. A correlated EXISTS per person is the clearest way, and in SQLite it evaluates to 1 or 0, so it can be summed.

SELECT strftime('%Y-%m', c.signup_date) AS cohort,
       COUNT(*) AS size,
       SUM(EXISTS (SELECT 1 FROM purchases p
                   WHERE p.customer_id = c.id
                     AND julianday(p.day) - julianday(c.signup_date) BETWEEN 0 AND 30)) AS bought
FROM customers c
GROUP BY cohort;

Funnels count people, not events. Use COUNT(DISTINCT user_id) per step and divide by the top step. A viewer who opens twenty pages is still one viewer. Be explicit about whether a step must come after the previous one; the simple version below only counts who ever did each step.

SELECT step, COUNT(DISTINCT user_id) AS users,
       ROUND(100.0 * COUNT(DISTINCT user_id) / (SELECT COUNT(DISTINCT user_id) FROM funnel WHERE step = 'view'), 1) AS pct
FROM funnel
GROUP BY step;

Pitfalls. Cohorts that started recently have not had the full window yet, so a low number may only mean 'not yet'. Compare timestamps in UTC, use half-open ranges (>= '2024-06-01' AND < '2024-06-02') rather than BETWEEN for days, and decide on purpose whether a gap of exactly the threshold starts a new session.