SELECT
  f.repo_name,
  f.path,
  c.copies,
  c.size,
  c.content,
  l.license
FROM
 (select f.*, row_number() over (partition by id order by path desc) as seqnum
      from `bigquery-public-data.github_repos.files` AS f) f
JOIN
  `bigquery-public-data.github_repos.contents` AS c
ON
  f.id = c.id AND seqnum=1
JOIN
  `bigquery-public-data.github_repos.licenses` AS l
ON
  f.repo_name = l.repo_name
WHERE
  NOT c.binary
  AND ((f.path LIKE '%.asm'
      OR f.path LIKE '%.bat'
      OR f.path LIKE '%.cmd'
      OR f.path LIKE '%.c'
      OR f.path LIKE '%.h'
      OR f.path LIKE '%.cs'
      OR f.path LIKE '%.cpp'
      OR f.path LIKE '%.hpp'
      OR f.path LIKE '%.c++'
      OR f.path LIKE '%.h++'
      OR f.path LIKE '%.cc'
      OR f.path LIKE '%.hh'
      OR f.path LIKE '%.C'
      OR f.path LIKE '%.H'
      OR f.path LIKE '%.cmake'
      OR f.path LIKE '%.css'
      OR f.path LIKE '%.dockerfile'
      OR f.path LIKE '%.f90'
      OR f.path LIKE '%.f'
      OR f.path LIKE '%.f03'
      OR f.path LIKE '%.f08'
      OR f.path LIKE '%.f77'
      OR f.path LIKE '%.f95'
      OR f.path LIKE '%.for'
      OR f.path LIKE '%.fpp'
      OR f.path LIKE '%.go'
      OR f.path LIKE '%.hs'
      OR f.path LIKE '%.html'
      OR f.path LIKE '%.java'
      OR f.path LIKE '%.js'
      OR f.path LIKE '%.jl'
      OR f.path LIKE '%.lua'
      OR f.path LIKE '%.md'
      OR f.path LIKE '%.markdown'
      OR f.path LIKE '%.php'
      OR f.path LIKE '%.php3'
      OR f.path LIKE '%.php4'
      OR f.path LIKE '%.php5'
      OR f.path LIKE '%.phps'
      OR f.path LIKE '%.phpt'
      OR f.path LIKE '%.pl'
      OR f.path LIKE '%.pm'
      OR f.path LIKE '%.pod'
      OR f.path LIKE '%.perl'
      OR f.path LIKE '%.ps1'
      OR f.path LIKE '%.psd1'
      OR f.path LIKE '%.psm1'
      OR f.path LIKE '%.py'
      OR f.path LIKE '%.rb'
      OR f.path LIKE '%.rs'
      OR f.path LIKE '%.sql'
      OR f.path LIKE '%.scala'
      OR f.path LIKE '%.sh'
      OR f.path LIKE '%.bash'
      OR f.path LIKE '%.command'
      OR f.path LIKE '%.zsh'
      OR f.path LIKE '%.ts'
      OR f.path LIKE '%.tsx'
      OR f.path LIKE '%.tex'
      OR f.path LIKE '%.vb'
      OR f.path LIKE '%Dockerfile'
      OR f.path LIKE '%Makefile')
    AND (c.size BETWEEN 0
      AND 1048575))