Explaining Technical Decisions to Business Stakeholders Questions

SELECT date_trunc('hour', event_time) AS hr,
       COUNT(DISTINCT user_id) AS users,
       SUM(CASE WHEN event='conversion' THEN 1 ELSE 0 END) AS conversions
FROM events
WHERE event_time BETWEEN '2025-11-01' AND '2025-11-14'
GROUP BY 1
ORDER BY 1;

SELECT event_id, COUNT(*) FROM raw_events GROUP BY event_id HAVING COUNT(*)>1 LIMIT 10;

WITH monthly AS (
  SELECT
    date_trunc('month', sale_date) AS month_start,
    SUM(amount)::numeric(18,2) AS revenue
  FROM sales
  GROUP BY 1
)
SELECT
  to_char(month_start, 'YYYY-MM') AS month,
  revenue,
  LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
  CASE
    WHEN LAG(revenue) OVER (ORDER BY month_start) IS NULL THEN NULL
    ELSE ROUND( (revenue - LAG(revenue) OVER (ORDER BY month_start)) 
                / NULLIF(LAG(revenue) OVER (ORDER BY month_start), 0) * 100, 2 )
  END AS pct_change
FROM monthly
ORDER BY month_start;

-- 1. cohort assignment
SELECT user_id, DATE_TRUNC('month', sign_up_date) AS cohort_month
FROM users

-- 2. monthly activity per user
SELECT u.user_id, u.cohort_month, DATE_TRUNC('month', e.event_date) AS activity_month
FROM users u
LEFT JOIN events e USING (user_id)
GROUP BY 1,2,3

-- 3. cohort-month aggregates
SELECT cohort_month, activity_month,
       COUNT(DISTINCT user_id) AS active_users,
       SUM(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort_month ORDER BY activity_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- optional cumulative
FROM activity_table
GROUP BY cohort_month, activity_month

-- 4. churn/retention percent
SELECT cohort_month, activity_month,
       active_users,
       cohort_size,
       1.0 - active_users::float / cohort_size AS churn_rate
FROM aggregates
JOIN (SELECT cohort_month, COUNT(*) AS cohort_size FROM users GROUP BY cohort_month) c USING (cohort_month)

WITH users AS (
  SELECT user_id, MIN(order_date)::date AS cohort_date
  FROM orders
  GROUP BY user_id
),
events AS (
  SELECT o.user_id, o.order_date::date, o.revenue,
         FLOOR(EXTRACT(EPOCH FROM (o.order_date::date - u.cohort_date))/7) AS week_index,
         u.cohort_date
  FROM orders o
  JOIN users u USING(user_id)
)
SELECT cohort_date,
       week_index,
       COUNT(DISTINCT user_id) AS users_active,
       SUM(revenue) AS revenue,
       SUM(revenue)/NULLIF(COUNT(DISTINCT user_id),0) AS revenue_per_active_user
FROM events
GROUP BY cohort_date, week_index
ORDER BY cohort_date, week_index;