Customer Advocacy and Internal Communication Questions

SELECT event_date, COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_date = CURRENT_DATE - INTERVAL '1 day'
  AND event_name IN ('open_app','page_view','api_call')
GROUP BY event_date;

WITH start_users AS (
  SELECT COUNT(DISTINCT acct_id) AS start_cnt
  FROM subscriptions
  WHERE status='active' AND snapshot_date = date_trunc('month', current_date) - INTERVAL '1 month'
),
churned AS (
  SELECT COUNT(DISTINCT acct_id) AS churn_cnt
  FROM subscriptions
  WHERE status IN ('canceled','past_due') AND change_date BETWEEN date_trunc('month', current_date) - INTERVAL '1 month' AND date_trunc('month', current_date) - INTERVAL '1 day'
)
SELECT churn_cnt::float / NULLIF(start_cnt,0) AS churn_rate FROM start_users CROSS JOIN churned;

SELECT
  100.0 * (SUM(CASE WHEN score >=9 THEN 1 ELSE 0 END) - SUM(CASE WHEN score <=6 THEN 1 ELSE 0 END)) / COUNT(*) AS nps
FROM surveys
WHERE survey_type='NPS' AND response_date BETWEEN '2025-10-01' AND '2025-10-31';

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY days_to_value) AS median_ttv
FROM (
  SELECT acct_id,
         DATE_PART('day', MIN(event_date_filter) - MIN(paid_at)) AS days_to_value
  FROM (
    SELECT acct_id, MIN(paid_at) AS paid_at FROM accounts WHERE paid_at IS NOT NULL GROUP BY acct_id
  ) a
  JOIN events e ON e.acct_id = a.acct_id AND e.event_name='first_workflow_completed'
  GROUP BY acct_id
) t;

SELECT SUM(mrr)*12 AS arr
FROM subscriptions
WHERE status='active' AND snapshot_date = date_trunc('month', current_date);

WITH cleaned AS (
  SELECT
    customer_id,
    score,
    -- normalize timestamp to UTC or a specific TZ, then take first day of month
    (submitted_at AT TIME ZONE 'UTC')::date - (EXTRACT(day FROM (submitted_at AT TIME ZONE 'UTC'))::int - 1) AS month_start
  FROM nps_responses
  WHERE score IS NOT NULL
),
classified AS (
  SELECT
    month_start,
    COUNT(*) FILTER (WHERE score >= 9) AS promoters,
    COUNT(*) FILTER (WHERE score <= 6) AS detractors,
    COUNT(*) AS total_responses
  FROM cleaned
  GROUP BY month_start
)
SELECT
  month_start,
  ROUND(100.0 * promoters / NULLIF(total_responses,0), 2) AS pct_promoters,
  ROUND(100.0 * detractors / NULLIF(total_responses,0), 2) AS pct_detractors,
  ROUND(100.0 * (promoters - detractors) / NULLIF(total_responses,0), 2) AS nps,
  (total_responses < 10) AS low_confidence,
  total_responses
FROM classified
ORDER BY month_start;