SQL Problems Worked Out

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY ordered_at DESC, order_id DESC  -- tiebreaker!
         ) AS rn
  FROM orders
)
SELECT customer_id, order_id, ordered_at, status
FROM ranked
WHERE rn = 1;

SELECT customer_id, order_id, ordered_at, status
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY ordered_at DESC, order_id DESC
) = 1;

SELECT *
FROM orders o
WHERE ordered_at = (
  SELECT MAX(ordered_at)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY revenue DESC, order_id DESC
         ) AS rn
  FROM orders
)
SELECT *
FROM ranked
WHERE rn <= 3;

SELECT
  date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_revenue
ORDER BY date;

SELECT
  customer_id,
  ordered_at,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY customer_id
    ORDER BY ordered_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS lifetime_revenue
FROM orders;

SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d_avg
FROM daily_revenue;

WITH numbered AS (
  SELECT
    customer_id,
    date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date) AS rn
  FROM subscriptions
),
grouped AS (
  SELECT
    customer_id,
    date,
    date - INTERVAL '1 day' * rn AS island_key  -- groups consecutive dates
  FROM numbered
)
SELECT
  customer_id,
  MIN(date) AS start_date,
  MAX(date) AS end_date
FROM grouped
GROUP BY customer_id, island_key
ORDER BY customer_id, start_date;

WITH with_gap AS (
  SELECT
    customer_id,
    date,
    CASE
      WHEN LAG(date) OVER (PARTITION BY customer_id ORDER BY date)
           = date - INTERVAL '1 day' THEN 0
      ELSE 1
    END AS is_new_island
  FROM subscriptions
),
with_island_id AS (
  SELECT
    customer_id,
    date,
    SUM(is_new_island) OVER (
      PARTITION BY customer_id ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS island_id
  FROM with_gap
)
SELECT customer_id, MIN(date) AS start_date, MAX(date) AS end_date
FROM with_island_id
GROUP BY customer_id, island_id;

WITH with_gap AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts)
           >= event_ts - INTERVAL '30 minutes' THEN 0
      ELSE 1
    END AS is_new_session
  FROM events
),
with_session_id AS (
  SELECT
    user_id,
    event_ts,
    SUM(is_new_session) OVER (
      PARTITION BY user_id ORDER BY event_ts
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_num
  FROM with_gap
)
SELECT
  user_id || '_' || session_num AS session_id,
  user_id,
  MIN(event_ts) AS session_start,
  MAX(event_ts) AS session_end,
  COUNT(*)      AS event_count
FROM with_session_id
GROUP BY user_id, session_num
ORDER BY user_id, session_start;

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY
             updated_at DESC,
             (phone IS NOT NULL) DESC,  -- prefer non-null phones
             user_id DESC                -- final tiebreaker for determinism
         ) AS rn
  FROM users
)
SELECT *
FROM ranked
WHERE rn = 1;

WITH cohorts AS (
  SELECT user_id,
         DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
user_activity AS (
  SELECT DISTINCT
         user_id,
         DATE_TRUNC('month', event_date) AS activity_month
  FROM events
),
joined AS (
  SELECT
    c.cohort_month,
    c.user_id,
    ua.activity_month,
    DATEDIFF('month', c.cohort_month, ua.activity_month) AS months_since_signup
  FROM cohorts c
  LEFT JOIN user_activity ua ON ua.user_id = c.user_id
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
  FROM cohorts
  GROUP BY 1
)
SELECT
  j.cohort_month,
  j.months_since_signup,
  COUNT(DISTINCT j.user_id) AS active_users,
  cs.cohort_size,
  ROUND(100.0 * COUNT(DISTINCT j.user_id) / cs.cohort_size, 2) AS retention_pct
FROM joined j
JOIN cohort_sizes cs USING (cohort_month)
WHERE j.months_since_signup IS NOT NULL
  AND j.months_since_signup >= 0
GROUP BY j.cohort_month, j.months_since_signup, cs.cohort_size
ORDER BY j.cohort_month, j.months_since_signup;

-- Snowflake / Postgres / Databricks (ordered-set aggregate)
-- BigQuery: PERCENTILE_CONT(latency_ms, 0.5) OVER (PARTITION BY model_id), a window function with no GROUP BY form
SELECT
  model_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY latency_ms) AS median_latency
FROM inference_requests
GROUP BY model_id;

-- Snowflake / Databricks support APPROX_PERCENTILE for huge data; BigQuery uses APPROX_QUANTILES(latency_ms, 100)[OFFSET(50)]
SELECT
  model_id,
  APPROX_PERCENTILE(latency_ms, 0.5) AS approx_median
FROM inference_requests
GROUP BY model_id;

WITH ranked AS (
  SELECT
    model_id,
    latency_ms,
    ROW_NUMBER() OVER (PARTITION BY model_id ORDER BY latency_ms) AS rn,
    COUNT(*)    OVER (PARTITION BY model_id) AS total
  FROM inference_requests
)
SELECT
  model_id,
  AVG(latency_ms::FLOAT) AS median_latency  -- AVG handles even-count case (avg of two middle values)
FROM ranked
WHERE rn IN (
  FLOOR((total + 1) / 2.0),      -- middle when odd
  FLOOR((total + 2) / 2.0)       -- second middle when even
)
GROUP BY model_id;

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    2
  ) AS mom_pct_change
FROM monthly_revenue
ORDER BY month;

WITH user_funnel AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_type = 'signup'             THEN occurred_at END) AS signup_at,
    MIN(CASE WHEN event_type = 'visited_pricing'    THEN occurred_at END) AS pricing_at,
    MIN(CASE WHEN event_type = 'started_checkout'   THEN occurred_at END) AS checkout_at,
    MIN(CASE WHEN event_type = 'completed_purchase' THEN occurred_at END) AS purchase_at
  FROM events
  GROUP BY user_id
)
SELECT
  COUNT(*)                                                  AS users_signed_up,
  COUNT(*) FILTER (WHERE pricing_at  >= signup_at)          AS reached_pricing,
  COUNT(*) FILTER (WHERE pricing_at  >= signup_at
                     AND checkout_at >= pricing_at)         AS started_checkout,
  COUNT(*) FILTER (WHERE pricing_at  >= signup_at
                     AND checkout_at >= pricing_at
                     AND purchase_at >= checkout_at)        AS completed_purchase
FROM user_funnel
WHERE signup_at IS NOT NULL;

WITH counts AS (
  -- (above query, but selecting just the four counts)
)
SELECT
  users_signed_up,
  reached_pricing,
  ROUND(100.0 * reached_pricing / NULLIF(users_signed_up, 0), 2)  AS signup_to_pricing_pct,
  started_checkout,
  ROUND(100.0 * started_checkout / NULLIF(reached_pricing, 0), 2) AS pricing_to_checkout_pct,
  completed_purchase,
  ROUND(100.0 * completed_purchase / NULLIF(started_checkout, 0), 2) AS checkout_to_purchase_pct
FROM counts;

SELECT
  customer_id,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_revenue,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_revenue,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3_revenue,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4_revenue
FROM orders
GROUP BY customer_id;

-- Snowflake / BigQuery
SELECT *
FROM orders
PIVOT (
  SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);