Simple example of BigQuery (bq) sql join on airline public data set

big-query-join-example.sql

SELECT
  w.airline_name,
  COUNT(departure_delay) AS delay
FROM
  `bigquery-samples.airline_ontime_data.flights` AS f
JOIN (
  SELECT
   REGEXP_EXTRACT( airline ,r'.*: (.*)') as airline_code,
   REGEXP_EXTRACT( airline ,r'(.*):.*') as airline_name
  FROM
    `bigquery-samples.airline_ontime_data.airline_id_codes`) AS w
ON
  w.airline_code = f.airline
WHERE
  departure_delay > 0
  AND departure_airport = 'LGA'
  AND date = '2008-05-13'
GROUP BY
  w.airline_name
  order by delay desc
LIMIT
  100