SQL Problems II — Advanced

SELECT u.event_id, u.machine_id, u.started_at,
       u.gpu_seconds / 3600.0 * p.price_per_hr AS cost
FROM usage_events u
JOIN price_history p
  ON p.machine_id = u.machine_id
 AND u.started_at >= p.valid_from
 AND u.started_at <  p.valid_to;

WITH ordered AS (
  SELECT machine_id, started_at, ended_at,
    MAX(ended_at) OVER (PARTITION BY machine_id ORDER BY started_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_max_end
  FROM rentals
),
flagged AS (
  SELECT *, CASE WHEN prev_max_end IS NULL OR started_at > prev_max_end
                 THEN 1 ELSE 0 END AS new_island
  FROM ordered
),
grouped AS (
  SELECT *, SUM(new_island) OVER (PARTITION BY machine_id ORDER BY started_at) AS grp
  FROM flagged
)
SELECT machine_id, MIN(started_at) AS busy_start, MAX(ended_at) AS busy_end
FROM grouped GROUP BY machine_id, grp;

WITH daily AS (
  SELECT DISTINCT customer_id, DATE(started_at) AS day FROM usage_events
),
spine AS (
  SELECT generate_series(DATE '2026-01-01', CURRENT_DATE, INTERVAL '1 day')::date AS day
)
SELECT s.day, COUNT(DISTINCT d.customer_id) AS rolling_30d_active
FROM spine s
JOIN daily d ON d.day > s.day - 30 AND d.day <= s.day
GROUP BY s.day
ORDER BY s.day;

WITH seq AS (
  SELECT instance_id, state, changed_at,
    LEAD(state)      OVER (PARTITION BY instance_id ORDER BY changed_at) AS next_state,
    LEAD(changed_at) OVER (PARTITION BY instance_id ORDER BY changed_at) AS next_at
  FROM instance_state_log
),
interruptions AS (
  SELECT instance_id, changed_at AS stopped_at, next_at AS resumed_at
  FROM seq
  WHERE state = 'stopped' AND next_state = 'running'   -- a true resume
)
SELECT instance_id,
       COUNT(*) AS interruptions,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY resumed_at - stopped_at) AS median_resume
FROM interruptions GROUP BY instance_id;

WITH first_search AS (
  SELECT customer_id, MIN(searched_at) AS t0 FROM searches GROUP BY customer_id
),
bid AS (
  SELECT f.customer_id, MIN(b.placed_at) AS t1
  FROM first_search f
  JOIN bids b ON b.customer_id=f.customer_id
             AND b.placed_at BETWEEN f.t0 AND f.t0 + INTERVAL '1 hour'
  GROUP BY f.customer_id
),
ran AS (
  SELECT bd.customer_id
  FROM bid bd
  JOIN instance_state_log s ON s.customer_id=bd.customer_id AND s.state='running'
             AND s.changed_at BETWEEN bd.t1 AND bd.t1 + INTERVAL '1 hour'
  GROUP BY bd.customer_id
)
SELECT (SELECT COUNT(*) FROM first_search) AS searched,
       (SELECT COUNT(*) FROM bid)          AS bid_1h,
       (SELECT COUNT(*) FROM ran)          AS ran_1h;

WITH cohort AS (
  SELECT customer_id, DATE_TRUNC('week', signed_up_at) AS wk0 FROM customers
),
size AS (SELECT wk0, COUNT(*) n FROM cohort GROUP BY wk0),
act AS (
  SELECT DISTINCT c.wk0,
    FLOOR((DATE_TRUNC('week', u.started_at) - c.wk0) / 7)::int AS wk_offset,
    u.customer_id
  FROM cohort c JOIN usage_events u USING (customer_id)
)
SELECT a.wk0, a.wk_offset,
       COUNT(DISTINCT a.customer_id) AS active,
       ROUND(100.0*COUNT(DISTINCT a.customer_id)/s.n, 1) AS pct
FROM act a JOIN size s ON s.wk0=a.wk0
WHERE a.wk_offset BETWEEN 0 AND 8
GROUP BY a.wk0, a.wk_offset, s.n
ORDER BY a.wk0, a.wk_offset;

-- "Carry forward the latest non-null per field" survivorship
SELECT customer_id,
  (ARRAY_AGG(email      ORDER BY updated_at DESC) FILTER (WHERE email      IS NOT NULL))[1] AS email,
  (ARRAY_AGG(country    ORDER BY updated_at DESC) FILTER (WHERE country    IS NOT NULL))[1] AS country,
  (ARRAY_AGG(plan_tier  ORDER BY updated_at DESC) FILTER (WHERE plan_tier  IS NOT NULL))[1] AS plan_tier
FROM customer_sources
GROUP BY customer_id;

WITH lagged AS (
  SELECT customer_id, event_ts,
    LAG(event_ts) OVER (PARTITION BY customer_id ORDER BY event_ts) AS prev
  FROM events
),
gapflag AS (
  SELECT *, CASE WHEN prev IS NULL OR event_ts-prev > INTERVAL '30 min' THEN 1 ELSE 0 END AS gap_new
  FROM lagged
),
gapsess AS (
  SELECT *, SUM(gap_new) OVER (PARTITION BY customer_id ORDER BY event_ts) AS gap_sid
  FROM gapflag
)
SELECT *,
  gap_sid::text || '-' ||
  FLOOR(EXTRACT(EPOCH FROM (event_ts -
        MIN(event_ts) OVER (PARTITION BY customer_id, gap_sid))) / 7200)::text AS session_id
FROM gapsess;

WITH d AS (
  SELECT gpu_type, price_per_hr,
    NTILE(10) OVER (PARTITION BY gpu_type ORDER BY price_per_hr) AS decile
  FROM listings WHERE status = 'available'
)
SELECT gpu_type, decile,
       MIN(price_per_hr) AS decile_low,
       MAX(price_per_hr) AS decile_high,
       COUNT(*)          AS n
FROM d GROUP BY gpu_type, decile
ORDER BY gpu_type, decile;

WITH daily AS (
  SELECT DATE(started_at) AS day, SUM(gpu_seconds)/3600.0 AS gpu_hours
  FROM usage_events GROUP BY 1
),
stats AS (
  SELECT day, gpu_hours,
    AVG(gpu_hours)    OVER w AS mean_28,
    STDDEV(gpu_hours) OVER w AS sd_28
  FROM daily
  WINDOW w AS (ORDER BY day ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING)
)
SELECT day, gpu_hours, mean_28, sd_28,
       (gpu_hours - mean_28) / NULLIF(sd_28,0) AS z
FROM stats
WHERE sd_28 > 0 AND ABS((gpu_hours - mean_28)/sd_28) > 3
ORDER BY day;

WITH spine AS (
  SELECT generate_series(DATE '2026-01-01', CURRENT_DATE, INTERVAL '1 day')::date AS day
),
m AS (SELECT DISTINCT machine_id FROM price_changes),
grid AS (SELECT m.machine_id, s.day FROM m CROSS JOIN spine s),
joined AS (
  SELECT g.machine_id, g.day, pc.new_price
  FROM grid g
  LEFT JOIN price_changes pc
    ON pc.machine_id=g.machine_id AND DATE(pc.changed_at)=g.day
)
SELECT machine_id, day,
  LAST_VALUE(new_price IGNORE NULLS) OVER (
    PARTITION BY machine_id ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS price_locf
FROM joined;

SELECT m.gpu_type,
       DATE_TRUNC('hour', b.placed_at) AS hour,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY b.bid_price) AS median_clearing_price,
       COUNT(*) AS winning_bids
FROM bids b
JOIN machines m ON m.machine_id = b.machine_id
WHERE b.won = true
GROUP BY m.gpu_type, DATE_TRUNC('hour', b.placed_at)
ORDER BY gpu_type, hour;