Culture Building and Organizational Impact Questions

WITH params AS (
  SELECT
    date_trunc('month', current_date) - interval '1 month' AS month_start,
    (date_trunc('month', current_date) - interval '1 day')::date       AS month_end
),
team_users AS (
  -- users who existed by the end of the previous month
  SELECT team_id, user_id
  FROM users, params
  WHERE created_at::date <= params.month_end
),
views AS (
  -- distinct users who viewed a dashboard during the previous month
  SELECT DISTINCT de.user_id
  FROM dashboard_events de
  JOIN params ON true
  WHERE de.event_type = 'view'
    AND de.occurred_at::date BETWEEN params.month_start::date AND params.month_end
)
SELECT
  tu.team_id,
  COUNT(DISTINCT tu.user_id)                                 AS total_users,
  COUNT(DISTINCT CASE WHEN v.user_id IS NOT NULL THEN tu.user_id END) AS users_who_viewed,
  CASE
    WHEN COUNT(DISTINCT tu.user_id) = 0 THEN 0.0
    ELSE ROUND(
      COUNT(DISTINCT CASE WHEN v.user_id IS NOT NULL THEN tu.user_id END)::numeric
      / COUNT(DISTINCT tu.user_id)::numeric
      , 4)
  END                                                       AS adoption_rate,
  CASE
    WHEN COUNT(DISTINCT tu.user_id) = 0 THEN false
    WHEN (COUNT(DISTINCT CASE WHEN v.user_id IS NOT NULL THEN tu.user_id END)::numeric
          / COUNT(DISTINCT tu.user_id)::numeric) < 0.20 THEN true
    ELSE false
  END                                                       AS low_adoption_flag
FROM team_users tu
LEFT JOIN views v ON v.user_id = tu.user_id
GROUP BY tu.team_id
ORDER BY adoption_rate ASC;