Applied SQL Analysis.

WITH first_month AS (
  SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders GROUP BY customer_id
), activity AS (
  SELECT o.customer_id, f.cohort_month,
    DATE_TRUNC('month', o.order_date) AS activity_month
  FROM orders o JOIN first_month f USING (customer_id)
)
SELECT cohort_month, activity_month, COUNT(DISTINCT customer_id) AS active_customers
FROM activity
GROUP BY 1, 2;

WITH first_order AS (
  SELECT customer_id, MIN(order_date) AS first_date
  FROM orders GROUP BY customer_id
)
SELECT
  DATE_PART('week', o.order_date - f.first_date) AS week_number,
  COUNT(DISTINCT o.customer_id) AS retained_customers
FROM orders o JOIN first_order f USING (customer_id)
GROUP BY 1 ORDER BY 1;

SELECT
  COUNT(DISTINCT CASE WHEN event_name='visit' THEN user_id END) AS visits,
  COUNT(DISTINCT CASE WHEN event_name='signup' THEN user_id END) AS signups,
  COUNT(DISTINCT CASE WHEN event_name='checkout' THEN user_id END) AS checkouts,
  COUNT(DISTINCT CASE WHEN event_name='purchase' THEN user_id END) AS purchases
FROM events
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days';

SELECT customer_id,
  MAX(order_date) AS last_order_date,
  CURRENT_DATE - MAX(order_date)::date AS days_inactive,
  CASE
    WHEN CURRENT_DATE - MAX(order_date)::date > 60 THEN 'Churned'
    WHEN CURRENT_DATE - MAX(order_date)::date > 30 THEN 'At risk'
    ELSE 'Active'
  END AS lifecycle_state
FROM orders
GROUP BY customer_id;

SELECT customer_id,
  COUNT(*) AS orders,
  SUM(amount_bdt) AS spend,
  CASE
    WHEN SUM(amount_bdt) >= 50000 THEN 'VIP'
    WHEN COUNT(*) >= 5 THEN 'Frequent'
    WHEN COUNT(*) = 1 THEN 'New'
    ELSE 'Occasional'
  END AS segment
FROM orders
GROUP BY customer_id;

WITH rfm AS (
  SELECT customer_id,
    CURRENT_DATE - MAX(order_date)::date AS recency_days,
    COUNT(*) AS frequency,
    SUM(amount_bdt) AS monetary
  FROM orders GROUP BY customer_id
)
SELECT *,
  NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
  NTILE(5) OVER (ORDER BY frequency) AS f_score,
  NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM rfm;

WITH gaps AS (
  SELECT user_id, event_time,
    event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS gap
  FROM events
), flags AS (
  SELECT *, CASE WHEN gap IS NULL OR gap > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS new_session
  FROM gaps
)
SELECT *, SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM flags;

SELECT user_id,
  MIN(CASE WHEN event_name='signup' THEN event_time END) AS signup_time,
  MIN(CASE WHEN event_name='purchase' THEN event_time END) AS first_purchase_time,
  MIN(CASE WHEN event_name='purchase' THEN event_time END)
    - MIN(CASE WHEN event_name='signup' THEN event_time END) AS time_to_purchase
FROM events
GROUP BY user_id;

SELECT
  COUNT(DISTINCT CASE WHEN event_name='saved_search_used' THEN user_id END) AS adopters,
  COUNT(DISTINCT user_id) AS active_users,
  1.0 * COUNT(DISTINCT CASE WHEN event_name='saved_search_used' THEN user_id END)
    / NULLIF(COUNT(DISTINCT user_id),0) AS adoption_rate
FROM events
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days';

SELECT experiment_group,
  COUNT(DISTINCT user_id) AS users,
  AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) AS conversion_rate,
  AVG(order_value_bdt) AS avg_order_value
FROM experiment_users
GROUP BY experiment_group;

WITH daily AS (
  SELECT event_date, COUNT(*) AS transactions
  FROM payments GROUP BY event_date
)
SELECT event_date, transactions,
  AVG(transactions) OVER (ORDER BY event_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS trailing_avg,
  transactions - AVG(transactions) OVER (ORDER BY event_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS delta
FROM daily;