SQL Problems

WITH per_week AS (
  SELECT
    DATE_TRUNC('week', started_at)::DATE AS week,
    customer_id,
    SUM(EXTRACT(EPOCH FROM ended_at - started_at) / 3600.0) AS gpu_hours
  FROM gpu_sessions
  WHERE started_at >= NOW() - INTERVAL '8 weeks'
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    week, customer_id, gpu_hours,
    DENSE_RANK() OVER (PARTITION BY week ORDER BY gpu_hours DESC) AS rk
  FROM per_week
)
SELECT week, customer_id, gpu_hours, rk
FROM ranked
WHERE rk <= 3
ORDER BY week, rk;

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('week', signed_up_at)::DATE AS cohort_week
  FROM customers
  WHERE signed_up_at >= NOW() - INTERVAL '12 weeks'
),
cohort_size AS (
  SELECT cohort_week, COUNT(*) AS size FROM cohorts GROUP BY 1
),
activity AS (
  SELECT DISTINCT
    g.customer_id,
    DATE_TRUNC('week', g.started_at)::DATE AS active_week
  FROM gpu_sessions g
),
retained AS (
  SELECT
    c.cohort_week,
    (a.active_week - c.cohort_week) / 7 AS weeks_since,
    COUNT(DISTINCT c.customer_id) AS retained
  FROM cohorts c
  JOIN activity a USING (customer_id)
  WHERE a.active_week >= c.cohort_week
  GROUP BY 1, 2
)
SELECT
  r.cohort_week, r.weeks_since,
  r.retained, s.size,
  r.retained * 1.0 / s.size AS retention_rate
FROM retained r
JOIN cohort_size s USING (cohort_week)
WHERE r.weeks_since IN (1, 2, 4, 8)
ORDER BY 1, 2;

WITH flagged AS (
  SELECT
    customer_id, event_at,
    CASE
      WHEN LAG(event_at) OVER (PARTITION BY customer_id ORDER BY event_at) IS NULL
        OR EXTRACT(EPOCH FROM event_at -
             LAG(event_at) OVER (PARTITION BY customer_id ORDER BY event_at)) / 60 > 30
      THEN 1 ELSE 0
    END AS is_new_session
  FROM events
),
sessioned AS (
  SELECT
    customer_id, event_at,
    SUM(is_new_session) OVER (PARTITION BY customer_id ORDER BY event_at) AS session_id
  FROM flagged
)
SELECT
  customer_id, session_id,
  MIN(event_at) AS session_start,
  MAX(event_at) AS session_end,
  COUNT(*)      AS n_events
FROM sessioned
GROUP BY 1, 2
ORDER BY 1, 2;

WITH signup AS (
  SELECT customer_id, MIN(signed_up_at) AS signup_at
  FROM customers
  WHERE signed_up_at >= NOW() - INTERVAL '30 days'
  GROUP BY 1
),
first_session AS (
  SELECT s.customer_id, s.signup_at, MIN(g.started_at) AS first_at
  FROM signup s
  JOIN gpu_sessions g
    ON g.customer_id = s.customer_id
   AND g.started_at BETWEEN s.signup_at AND s.signup_at + INTERVAL '7 days'
  GROUP BY 1, 2
),
second_session AS (
  SELECT f.customer_id
  FROM first_session f
  JOIN gpu_sessions g
    ON g.customer_id = f.customer_id
   AND g.started_at > f.first_at
   AND g.started_at <= f.first_at + INTERVAL '30 days'
  GROUP BY 1
)
SELECT
  (SELECT COUNT(*) FROM signup)         AS signups,
  (SELECT COUNT(*) FROM first_session)  AS first_session_within_7d,
  (SELECT COUNT(*) FROM second_session) AS second_session_within_30d;

WITH daily AS (
  SELECT DISTINCT customer_id, DATE_TRUNC('day', started_at)::DATE AS d
  FROM gpu_sessions
),
keyed AS (
  SELECT
    customer_id, d,
    d - (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY d))::INT AS run_key
  FROM daily
),
runs AS (
  SELECT customer_id, run_key, COUNT(*) AS streak_days
  FROM keyed GROUP BY 1, 2
)
SELECT customer_id, MAX(streak_days) AS longest_streak
FROM runs
GROUP BY 1
ORDER BY longest_streak DESC;

SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM gpu_sessions g WHERE g.customer_id = c.customer_id
);

WITH current_region AS (
  SELECT DISTINCT ON (customer_id)
    customer_id, region
  FROM customer_region_history
  ORDER BY customer_id, changed_at DESC
),
customer_revenue AS (
  SELECT customer_id, SUM(revenue) AS total
  FROM gpu_sessions GROUP BY 1
)
SELECT c.customer_id, r.region, COALESCE(cr.total, 0) AS total
FROM customers c
LEFT JOIN current_region r    ON r.customer_id = c.customer_id
LEFT JOIN customer_revenue cr ON cr.customer_id = c.customer_id;

SELECT
  customer_id,
  LAST_VALUE(plan) OVER (
    PARTITION BY customer_id
    ORDER BY changed_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS latest_plan
FROM plan_changes;

-- Cleaner alternative: filter to the most recent row
SELECT customer_id, plan FROM (
  SELECT customer_id, plan,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC) AS rn
  FROM plan_changes
) WHERE rn = 1;

SELECT
  DATE_TRUNC('day', started_at)::DATE AS day,
  COUNT(DISTINCT customer_id)         AS dau
FROM gpu_sessions
WHERE started_at >= CURRENT_DATE - INTERVAL '7 days'
  AND started_at <  CURRENT_DATE + INTERVAL '1 day'
GROUP BY 1;

SELECT COUNT(DISTINCT customer_id) AS active
FROM gpu_sessions
WHERE started_at >= CURRENT_TIMESTAMP - INTERVAL '7 days'
  AND started_at <  CURRENT_TIMESTAMP;