big query example.sql

# RULE
# It is a kind of Column Store
# Avoid using * (star) to return all columns, instead use preview
# Check the amount of the processing size by changing query) (500 MB, 1T..)
# Always with LIMIT
# format converts number into string (cannot add)
# cannot use aliased column in where clause like income
# StandardSQL or legacySQL https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql

#standardSQL
SELECT totrevenue as revenue, format("%'d", totrevenue) as revenue_formatted_string
  FROM `bigquery-public-data.irs_990.irs_990_2015` 
 LIMIT 100
 
#standardSQL
SELECT totrevenue as revenue
, format("%'d", totrevenue) as revenue_formatted_string
, (totrevenue - totfuncexpns) as income
FROM bigquery-public-data.irs_990.irs_990_2015
ORDER BY income desc
LIMIT 100

#standardSQL
SELECT 
    SUM(totrevenue)       AS total_2015_revnue
  , ROUND(AVG(totrevenue), 2)       AS avg_revnenue
  , COUNT(ein)            AS nonprofits
  , COUNT(DISTINCT ein)   AS nonprofits_distinct
  , MAX( noemplyeesw3cnt ) AS num_employees
FROM
 `bigquery-public-data.irs_990.irs_990_2015`
  
#standardSQL
SELECT
  ein,
  COUNT(ein) AS ein_count
 FROM
  `bigquery-public-data.irs_990.irs_990_2015`
 GROUP BY ein
 HAVING ein_count > 1
 ORDER BY ein_count DESC
 
#standardSQL
SELECT
*
 FROM
  `bigquery-public-data.irs_990.irs_990_2015`
WHERE ein = '736103485'
limit 100; 
 
#standardSQL
SELECT
*
 FROM
  `bigquery-public-data.irs_990.irs_990_2015`
WHERE 
  EXTRACT (YEAR FROM
     PARSE_DATE('%Y%m', CAST(tax_pd AS STRING))
  ) = 2014
 LIMIT 10
 
#standardSQL
SELECT CONCAT("12345", "678")

#standardSQL
SELECT ENDS_WITH("Apple","e")

#standardSQL
SELECT LOWER("Apple")

#standardSQL
SELECT REGEXP_CONTAINS("Launchbox",r"^*box$")