Data Manipulation and Transformation Questions

WITH cleaned AS (
  SELECT user_id, amount, occurred_at::date AS occurred_at
  FROM transactions
  WHERE occurred_at IS NOT NULL
),
ranked AS (
  SELECT
    user_id,
    amount,
    occurred_at,
    LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_date
  FROM cleaned
),
diffs AS (
  SELECT
    user_id,
    amount,
    occurred_at,
    CASE WHEN prev_date IS NULL THEN NULL ELSE (occurred_at - prev_date) END AS interval_days
  FROM ranked
)
SELECT
  user_id,
  SUM(amount) AS total_spend,
  MAX(occurred_at) AS last_purchase_date,
  CASE WHEN COUNT(interval_days) = 0 THEN NULL ELSE AVG(interval_days) END AS avg_purchase_interval_days
FROM diffs
GROUP BY user_id;

import pandas as pd

df = pd.DataFrame(...)  # columns: user_id, amount, occurred_at
df['occurred_at'] = pd.to_datetime(df['occurred_at'], errors='coerce')
df = df.dropna(subset=['occurred_at'])  # or keep and handle separately

# total and last
agg = df.groupby('user_id').agg(
    total_spend=('amount', 'sum'),
    last_purchase_date=('occurred_at', 'max')
).reset_index()

# avg interval
df_sorted = df.sort_values(['user_id','occurred_at'])
df_sorted['prev'] = df_sorted.groupby('user_id')['occurred_at'].shift(1)
df_sorted['interval_days'] = (df_sorted['occurred_at'] - df_sorted['prev']).dt.days
avg_interval = df_sorted.groupby('user_id')['interval_days'].mean().reset_index()

result = agg.merge(avg_interval, on='user_id', how='left')
result['interval_days'] = result['interval_days'].where(result['interval_days'].notna(), None)
result.rename(columns={'interval_days':'avg_purchase_interval_days'}, inplace=True)

import unicodedata, spacy
nlp = spacy.load("en_core_web_sm", disable=["ner"])
def clean(text):
    raw = text
    text = unicodedata.normalize("NFKC", text)
    text = text.lower()
    doc = nlp(text)
    tokens = [tok.lemma_ for tok in doc if not tok.is_punct]
    return {"raw_text": raw, "clean_text": " ".join(tokens)}