Business Context and Metrics Understanding Questions

-- canonical_mau_v1
select date_trunc('month', event_date) as month,
       count(distinct user_id) as mau
from events
where is_bot = false and env = 'prod'
group by 1;

WITH conversions AS (
  SELECT
    user_id,
    touch_id   AS conversion_touch_id,
    occurred_at AS conversion_ts
  FROM touches
  WHERE is_conversion = TRUE
),
touches_in_window AS (
  -- join touches to each conversion for same user where touch occured within window
  SELECT
    c.user_id,
    c.conversion_touch_id,
    c.conversion_ts,
    t.touch_id,
    t.channel,
    t.occurred_at AS touch_ts
  FROM conversions c
  JOIN touches t
    ON t.user_id = c.user_id
   AND t.occurred_at BETWEEN (c.conversion_ts - INTERVAL '30' DAY) AND c.conversion_ts
),
credits AS (
  -- compute number of touches per conversion and assign equal credit
  SELECT
    user_id,
    conversion_touch_id,
    conversion_ts,
    touch_id,
    channel,
    1.0 / COUNT(*) OVER (PARTITION BY user_id, conversion_touch_id) AS credit_per_touch
  FROM touches_in_window
)
SELECT
  channel,
  SUM(credit_per_touch) AS attributed_conversions
FROM credits
GROUP BY channel
ORDER BY attributed_conversions DESC;

SELECT ip, COUNT(DISTINCT user_id) as users
FROM events
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-07'
GROUP BY ip
ORDER BY users DESC
LIMIT 50;

WITH ip_counts AS (
  SELECT ip, COUNT(DISTINCT user_id) AS users FROM events
  WHERE event_date BETWEEN '2025-11-01' AND '2025-11-07'
  GROUP BY ip
)
SELECT SUM(users*users)/POWER(SUM(users),2) AS herfindahl
FROM ip_counts;

SELECT user_agent, COUNT(*) AS cnt
FROM events
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-07'
GROUP BY user_agent
ORDER BY cnt DESC
LIMIT 100;

SELECT user_id, COUNT(*) AS sessions,
  AVG(session_duration_seconds) AS avg_dur,
  SUM(CASE WHEN session_duration_seconds < 2 THEN 1 ELSE 0 END) AS very_short
FROM sessions
WHERE session_date BETWEEN '2025-11-01' AND '2025-11-07'
GROUP BY user_id
ORDER BY very_short DESC
LIMIT 100;

SELECT user_id, COUNT(*)/(EXTRACT(EPOCH FROM (MAX(ts)-MIN(ts)))/60) AS ev_per_min
FROM events
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-07'
GROUP BY user_id
HAVING (MAX(ts)-MIN(ts)) > INTERVAL '1 minute'
ORDER BY ev_per_min DESC
LIMIT 100;

SELECT country, COUNT(DISTINCT user_id) as users
FROM events JOIN ip_geo USING (ip)
WHERE event_date BETWEEN '2025-11-01' AND '2025-11-07'
GROUP BY country ORDER BY users DESC;