Google BigQuery


SEC Dataset



https://console.cloud.google.com/bigquery?project=crested-disk-129222&folder=&organizationId=&j=bq:US:bquxjob_67d5369b_1737c3e24f0&page=queryresults


https://console.cloud.google.com/bigquery?sq=1096910439146:a77c5c548ae949a8a7e31c98d3dc58b4


This is a query of the table "quick_summary"

SELECT 
  *
  
FROM
  [bigquery-public-data:sec_quarterly_financials.quick_summary]




SELECT 
  measure_tag
  
FROM
  [bigquery-public-data:sec_quarterly_financials.quick_summary]
  
GROUP BY
  measure_tag





SELECT 
  measure_tag, COUNT(measure_tag) as Count
  
FROM
  [bigquery-public-data:sec_quarterly_financials.quick_summary]
  
GROUP BY
  measure_tag
  





SELECT 
  measure_tag, COUNT(measure_tag) as Count
  
FROM
  [bigquery-public-data:sec_quarterly_financials.quick_summary]
  
GROUP BY
  measure_tag


ORDER BY Count DESC


//LIST OF ALL SUBMISSIONS

SELECT 
  central_index_key, company_name, COUNT(submission_number) as Submissions
  
FROM
  [bigquery-public-data:sec_quarterly_financials.submission]
  
GROUP BY
  central_index_key, company_name
  
ORDER BY
  central_index_key



//Get the count of submissions for MICROSOFT 0000789019

SELECT 
  central_index_key, company_name, COUNT(submission_number) as Submissions
  
FROM
  [bigquery-public-data:sec_quarterly_financials.submission]
  
WHERE
  central_index_key = 789019
  
GROUP BY
  central_index_key, company_name
  
ORDER BY
  central_index_key


//get each submission:

SELECT 
  central_index_key, company_name, submission_number, fiscal_year, fiscal_period_focus
  
FROM
  [bigquery-public-data:sec_quarterly_financials.submission]
  
WHERE
  central_index_key = 789019
  
  
ORDER BY
  fiscal_year, fiscal_period_focus





//get each of the networks for the filing

SELECT 
  submission_number, shortname, longname, roleuri
  
FROM
  [bigquery-public-data:sec_quarterly_financials.rendering]
  
WHERE
  submission_number = '0001564590-19-027952'
  
  
ORDER BY
  longname




//Get the report elements for the first network in the report
SELECT 
  *
  
FROM
  [bigquery-public-data:sec_quarterly_financials.presentation]
  
WHERE

  submission_number = '0001564590-17-007547' and report = 1

ORDER BY
  report, line




SELECT 
  fiscal_year, fiscal_period_focus, sum(value) as Total

FROM
  [bigquery-public-data:sec_quarterly_financials.quick_summary]
WHERE
  (measure_tag = 'LiabilitiesAndStockholdersEquity' or measure_tag = 'LiabilitiesAndPartnersCapital') and fiscal_period_focus = 'FY'
 GROUP BY
  fiscal_year, fiscal_period_focus
ORDER BY
  fiscal_year



SELECT 
  measure_tag, fiscal_year, fiscal_period_focus, count(company_name) as entities, sum(value) as Total

FROM
  [bigquery-public-data:sec_quarterly_financials.quick_summary]
WHERE
  measure_tag = 'Assets' and fiscal_period_focus = 'FY'
 GROUP BY
  measure_tag, fiscal_year, fiscal_period_focus
ORDER BY
  fiscal_year



//Querying across multiple data sets

SELECT zipba as ZipCode2 FROM `bigquery-public-data.sec_quarterly_financials.submission` 

UNION ALL

SELECT zipcode as ZipCode2 FROM `bigquery-public-data.utility_us.zipcode_area` LIMIT 10