Claude vs GPT-4 for Writing SQL Queries 2026

-- Users and their subscription tiers
CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    email      TEXT UNIQUE NOT NULL,
    plan       TEXT NOT NULL CHECK (plan IN ('free', 'pro', 'enterprise')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Events: actions users take in the product
CREATE TABLE events (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT REFERENCES users(id),
    event_type TEXT NOT NULL,
    properties JSONB DEFAULT '{}',
    occurred_at TIMESTAMPTZ DEFAULT NOW()
);

-- Subscriptions: billing history
CREATE TABLE subscriptions (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT REFERENCES users(id),
    plan        TEXT NOT NULL,
    started_at  TIMESTAMPTZ NOT NULL,
    ended_at    TIMESTAMPTZ
);

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('week', created_at) AS cohort_week
    FROM users
),
cohort_sizes AS (
    SELECT
        cohort_week,
        COUNT(*) AS cohort_size
    FROM cohorts
    GROUP BY cohort_week
),
user_activity AS (
    SELECT DISTINCT
        e.user_id,
        c.cohort_week,
        DATE_TRUNC('week', e.occurred_at) - DATE_TRUNC('week', c.cohort_week) AS weeks_since_signup
    FROM events e
    JOIN cohorts c USING (user_id)
    WHERE DATE_TRUNC('week', e.occurred_at) >= c.cohort_week  -- exclude pre-signup noise
),
retention_by_week AS (
    SELECT
        cohort_week,
        COUNT(DISTINCT CASE WHEN weeks_since_signup = INTERVAL '1 week' THEN user_id END) AS w1,
        COUNT(DISTINCT CASE WHEN weeks_since_signup = INTERVAL '2 weeks' THEN user_id END) AS w2,
        COUNT(DISTINCT CASE WHEN weeks_since_signup = INTERVAL '3 weeks' THEN user_id END) AS w3,
        COUNT(DISTINCT CASE WHEN weeks_since_signup = INTERVAL '4 weeks' THEN user_id END) AS w4
    FROM user_activity
    GROUP BY cohort_week
)
SELECT
    r.cohort_week,
    cs.cohort_size,
    ROUND(100.0 * r.w1 / cs.cohort_size, 1) AS retained_w1,
    ROUND(100.0 * r.w2 / cs.cohort_size, 1) AS retained_w2,
    ROUND(100.0 * r.w3 / cs.cohort_size, 1) AS retained_w3,
    ROUND(100.0 * r.w4 / cs.cohort_size, 1) AS retained_w4
FROM retention_by_week r
JOIN cohort_sizes cs USING (cohort_week)
ORDER BY r.cohort_week DESC
NULLS LAST;

WITH daily_activity AS (
    SELECT DISTINCT
        user_id,
        DATE(occurred_at AT TIME ZONE 'UTC') AS active_date
    FROM events
),
date_gaps AS (
    SELECT
        user_id,
        active_date,
        active_date - ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY active_date
        ) * INTERVAL '1 day' AS streak_group
        -- Consecutive dates have the same streak_group
        -- because (date - sequential_rownum) is constant
    FROM daily_activity
),
streaks AS (
    SELECT
        user_id,
        streak_group,
        COUNT(*) AS streak_length,
        MAX(active_date) AS streak_end
    FROM date_gaps
    GROUP BY user_id, streak_group
),
user_streaks AS (
    SELECT
        user_id,
        MAX(streak_length) AS longest_streak_ever,
        MAX(streak_length) FILTER (
            WHERE streak_end >= CURRENT_DATE - INTERVAL '1 day'
        ) AS current_streak
    FROM streaks
    GROUP BY user_id
)
SELECT
    user_id,
    COALESCE(current_streak, 0) AS current_streak,
    longest_streak_ever
FROM user_streaks
ORDER BY current_streak DESC NULLS LAST;

WITH RECURSIVE reporting_chain AS (
    -- Anchor: start with the target user
    SELECT
        id,
        email,
        manager_id,
        0 AS depth,
        ARRAY[id] AS path,
        email AS path_names
    FROM users
    WHERE id = 42

    UNION ALL

    -- Recursive: join to manager
    SELECT
        u.id,
        u.email,
        u.manager_id,
        rc.depth + 1,
        rc.path || u.id,
        rc.path_names || ' → ' || u.email
    FROM users u
    JOIN reporting_chain rc ON u.id = rc.manager_id
    WHERE u.id <> ALL(rc.path)  -- cycle guard
)
SELECT
    id,
    email,
    depth,
    path,
    path_names AS hierarchy_path
FROM reporting_chain
ORDER BY depth DESC;
-- depth DESC = root first

-- Main checkout metrics
WITH checkout_events AS (
    SELECT
        properties,
        (properties->>'order_value')::NUMERIC AS order_value
    FROM events
    WHERE event_type = 'checkout'
),
payment_breakdown AS (
    SELECT
        properties->>'payment_method' AS payment_method,
        COUNT(*) AS count,
        ROUND(AVG((properties->>'order_value')::NUMERIC), 2) AS avg_value
    FROM checkout_events
    GROUP BY 1
),
product_counts AS (
    SELECT
        product_id,
        COUNT(*) AS frequency
    FROM checkout_events,
         LATERAL jsonb_array_elements_text(properties->'product_ids') AS t(product_id)
    GROUP BY product_id
    ORDER BY frequency DESC
    LIMIT 5
)
SELECT
    (SELECT COUNT(*) FROM checkout_events) AS total_checkouts,
    (SELECT ROUND(AVG(order_value), 2) FROM checkout_events) AS avg_order_value,
    (SELECT jsonb_agg(row_to_json(payment_breakdown.*)) FROM payment_breakdown) AS by_payment_method,
    (SELECT jsonb_agg(row_to_json(product_counts.*)) FROM product_counts) AS top_products;

-- The Seq Scan with 8.4M rows removed means no useful index exists.
-- Create a composite index matching the WHERE clause columns:

CREATE INDEX CONCURRENTLY idx_events_type_occurred
ON events (event_type, occurred_at DESC)
WHERE occurred_at > NOW() - INTERVAL '90 days';  -- partial index to keep it small

-- After index creation, verify it's used:
EXPLAIN (ANALYZE, BUFFERS) SELECT user_id, COUNT(*) as event_count
FROM events
WHERE occurred_at > NOW() - INTERVAL '30 days'
  AND event_type = 'page_view'
GROUP BY user_id
ORDER BY event_count DESC
LIMIT 100;

-- Expected improvement: Index Scan instead of Seq Scan,
-- reducing rows examined from 10M to ~1.6M
-- Estimated query time after index: 800ms-1.2s

-- BigQuery version (mostly identical — standard SQL window functions are portable)
SELECT
  user_id,
  SUM(order_value) OVER (
    PARTITION BY user_id
    ORDER BY occurred_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM `project.dataset.events`;

-- Key differences Claude notes:
-- 1. Table reference uses backtick notation: `project.dataset.table`
-- 2. TIMESTAMPTZ → TIMESTAMP (BigQuery uses TIMESTAMP with UTC semantics)
-- 3. BIGSERIAL → INT64 (no sequences in BigQuery)
-- 4. JSONB → JSON (BigQuery uses JSON type, operators differ: JSON_VALUE instead of ->>)
-- 5. NOW() → CURRENT_TIMESTAMP() in BigQuery standard SQL