BigQuery Interview Questions

-- Bad: scans 10TB
SELECT *
FROM `project.dataset.events`
WHERE event_type = 'purchase'
  AND user_id = '12345';

-- Good: scans ~100GB
SELECT user_id, event_ts, amount_usd
FROM `project.dataset.events`
WHERE _PARTITIONDATE >= '2026-01-01'
  AND _PARTITIONDATE < '2026-02-01'
  AND event_type = 'purchase'
  AND user_id = '12345';
-- Required: table partitioned by _PARTITIONDATE
-- and clustered by (event_type, user_id)

SELECT
  user_id,
  ARRAY_AGG(
    STRUCT(event_type, event_ts, page_url)
    ORDER BY event_ts
  ) AS event_sequence
FROM events
WHERE _PARTITIONDATE >= CURRENT_DATE() - 7
GROUP BY user_id;

-- Downstream UNNEST to flatten back to rows when needed:
SELECT user_id, e.event_type, e.event_ts
FROM user_events_with_sequence,
UNNEST(event_sequence) AS e
WHERE e.event_type = 'purchase';

-- Approximate count distinct (~2% error, constant memory)
SELECT
  date_trunc(event_ts, DAY) AS day,
  APPROX_COUNT_DISTINCT(user_id) AS approx_dau
FROM events
WHERE _PARTITIONDATE >= '2026-01-01'
GROUP BY day;

-- Mergeable HLL sketches for rolling unique counts
WITH daily_sketches AS (
  SELECT
    date_trunc(event_ts, DAY) AS day,
    HLL_COUNT.INIT(user_id) AS sketch
  FROM events
  WHERE _PARTITIONDATE >= '2026-01-01'
  GROUP BY day
)
SELECT
  day,
  HLL_COUNT.MERGE(sketch) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_unique_users
FROM daily_sketches
ORDER BY day;

-- Top 1 event per user
SELECT user_id, event_ts, event_type
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY event_ts DESC
) = 1;

-- Equivalent without QUALIFY (more verbose):
WITH ranked AS (
  SELECT
    user_id, event_ts, event_type,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_ts DESC
    ) AS rn
  FROM events
)
SELECT user_id, event_ts, event_type
FROM ranked
WHERE rn = 1;