Root Cause Analysis and Diagnostics Questions

# Inputs:
# x: 1D time series (e.g., daily active users)
# min_size: minimum segment length (e.g., 7 days)
# penalty: scalar penalty for adding a change point (e.g., BIC or lambda)
# seasonal_period: optional (e.g., 7 for weekly)

def preprocess(x, seasonal_period=None):
    if seasonal_period:
        # remove seasonal component (moving-average or STL)
        x = x - seasonal_deseasonalize(x, seasonal_period)
    x = smooth_outliers(x)  # e.g., median filter
    return x

def cost(seg):
    # sum squared deviations from segment mean
    mu = mean(seg)
    return sum((seg - mu)**2)

def PELT(x, min_size, penalty):
    n = len(x)
    F = [0] + [inf]*n         # best cost up to t
    cp = [[] for _ in range(n+1)]
    R = {0}
    for t in range(1, n+1):
        candidates = []
        for s in list(R):
            if t - s < min_size: 
                continue
            c = F[s] + cost(x[s:t]) + penalty
            candidates.append((c, s))
        best_c, best_s = min(candidates)
        F[t] = best_c
        cp[t] = cp[best_s] + [best_s]
        # prune: keep s in R if F[s] + cost(x[s:t]) + penalty <= F[t] + threshold
        R = prune(R, F, x, t, penalty)
    return cp[n]  # list of change point indices

# Main usage:
x_proc = preprocess(x, seasonal_period=7)
cps = PELT(x_proc, min_size=7, penalty=choose_penalty(x_proc))

WITH users AS (
  SELECT user_id, DATE_TRUNC('week', MIN(event_date)) AS cohort_week
  FROM events
  GROUP BY user_id
),
returns AS (
  SELECT u.cohort_week, u.user_id,
         MIN(e.event_date) FILTER (WHERE e.event_date BETWEEN u.cohort_week + INTERVAL '7 days' AND u.cohort_week + INTERVAL '7 days' + INTERVAL '1 day') AS returned_on_day7
  FROM users u
  LEFT JOIN events e ON e.user_id = u.user_id
  GROUP BY u.cohort_week, u.user_id
)
SELECT cohort_week,
       COUNT(returned_on_day7)::float / COUNT(user_id) AS day7_retention
FROM returns
GROUP BY cohort_week
ORDER BY cohort_week;

WITH days AS (
  SELECT generate_series(min(dt), max(dt), interval '1 day')::date AS day
  FROM (SELECT min(order_date) AS min, max(order_date) AS max FROM orders) x
)
SELECT d.day
FROM days d
LEFT JOIN (SELECT DISTINCT order_date::date AS day FROM orders) o USING (day)
WHERE o.day IS NULL;
-- assertion: should return 0 rows

SELECT order_id, COUNT(*) cnt
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
-- assertion: should return 0 rows