Example of a query with double unnest

table structure for this lab

SELECT
  p.v2ProductName,
  p.v2ProductCategory,
  SUM(p.productQuantity) AS units_sold,
  ROUND(SUM(p.localProductRevenue/1000000),2) AS revenue
FROM
  `data-to-insights.ecommerce.web_analytics`,
  UNNEST(hits) AS h,
  UNNEST(h.product) AS p
GROUP BY
  1,
  2
ORDER BY
  revenue DESC
LIMIT

Query with table preparation and conditional value

# visitors who bought on a return visit (could have bought on first as well
  WITH
    all_visitor_stats AS (
    SELECT
      fullvisitorid,
      # 741,721 unique visitors
    IF
      (COUNTIF(totals.transactions > 0
          AND totals.newVisits IS NULL) > 0,
        1,
        0) AS will_buy_on_return_visit
    FROM
      `data-to-insights.ecommerce.web_analytics`
    GROUP BY
      fullvisitorid )
  SELECT
    COUNT(DISTINCT fullvisitorid) AS total_visitors,
    will_buy_on_return_visit
  FROM
    all_visitor_stats
  GROUP BY
    will_buy_on_return_visit

Sample a biquery table just based on some simple hash-like strategy given the data is not skewed

%%bigquery trips
SELECT
    FORMAT_TIMESTAMP(
        "%Y-%m-%d %H:%M:%S %Z", pickup_datetime) AS pickup_datetime,
    pickup_longitude, pickup_latitude, 
    dropoff_longitude, dropoff_latitude,
    passenger_count,
    trip_distance,
    tolls_amount,
    fare_amount,
    total_amount
FROM
    `nyc-tlc.yellow.trips`
WHERE
    ABS(MOD(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING)), 100000)) = 1