Q. How many filings have been submitted in inline XBRL?

SELECT count(*) 
FROM accession 
WHERE entry_type = 'inline'

Q. What are all the details of filings made by Apple Inc in an XBRL format?

FROM accession 
WHERE entity_name ilike '%Apple Inc%'

Q. What is the list of base Taxonomies in the database?

	, ns.taxonomy_version_id
	, prefix
	, ns.name AS ns_namespace
	, version
	, tx.name AS taxonomy_name
taxonomy_version txv
JOIN namespace ns
  ON ns.taxonomy_version_id = txv.taxonomy_version_id
JOIN taxonomy tx
  ON tx.taxonomy_id = txv.taxonomy_id
WHERE is_base = true
ORDER BY tx.name, version DESC, ns.name

Q. What facts were reported for SEC accession 0001393905-17-00010? What were the fiscal and calendar periods of those facts and what were the dimensions of those facts?

SELECT q.namespace
	, q.local_name
	, list_dimensional_qualifications_string2(f.context_id) dimension
	,CASE WHEN c.period_instant is not null 
	THEN c.period_instant::varchar
	WHEN c.period_start is not null 
	THEN c.period_start::varchar || ' - ' || c.period_end::varchar 
	ELSE 'forever'
	END AS period
	, f.fiscal_period
FROM fact f
JOIN accession a
  ON f.accession_id = a.accession_id
JOIN element e
  ON f.element_id = e.element_id
JOIN qname q
  ON e.qname_id = q.qname_id
JOIN context c
  ON f.context_id = c.context_id
WHERE a.filing_accession_number = '0001393905-17-000104'

Q. What is the latest year end for Apple Inc?

SELECT year_end 
	, 	(SELECT 
		FROM accession 
		WHERE entity_name ilike '%Apple Inc%')
	)  as year_end

Q. What is the structure of the DTS for the latest BARRACUDA NETWORKS filing?

SELECT 	dt.starts_loop AS starts_to_loop
	, repeat('  ', dt.level) ||  d.document_uri AS tree_structure
FROM dts_tree((SELECT MAX(dts_id) 
		FROM report 
JOIN document d
  ON d.document_id = dt.document_id
ORDER BY tree_order

Q. What are the details of all the US GAAP taxonomies published to date?

FROM taxonomy t
JOIN taxonomy_version tv
	ON t.taxonomy_id = tv.taxonomy_id
JOIN taxonomy_version_dts tvd
	ON tv.taxonomy_version_id = tvd.taxonomy_version_id
JOIN dts
	ON tvd.dts_id = dts.dts_id
JOIN dts_document dd 
	ON dts.dts_id = dd.dts_id
JOIN document d 
	ON d.document_id = dd.document_id
WHERE t.name = 'US GAAP' 
	AND top_level = true
   	AND dts_name like name || '%' 

Q. How many facts have ever been reported that use an extension line item?

select sum(primary_count) as facts_with_line_item_extensions
from report_element
where is_base = false

Q. How many facts have ever been reported that use an extension dimension?

select sum(dimension_count) as facts_with_dimension_extensions
from report_element
where is_base = false

Q. How many facts have ever been reported that use an extension member?

select sum(member_count) as facts_with_member_extensions
from report_element
where is_base = false

Q. How many facts have ever been reported that have either an extension line item extension member or extension dimension?

select count(*) from fact where is_extended is true

Q. What are the values for the following elements for filings on 2018-03-01:

  • Current Fiscal Year EndDate
  • Entity Public Float
  • Entity Filer Category
  • Document Fiscal Year Focus
  • Document Fiscal_Period Focus
  • Document Period End Date
    , entity_name
    , ticker(entity_id) as ticker
    , document_type(a.accession_id) as Document_Type
    , get_simple_fact_by_accession('dei','CurrentFiscalYearEndDate',a.accession_id, 'text'::character varying) as Current_Fiscal_Year_EndDate
    , get_simple_fact_by_accession('dei','EntityPublicFloat',a.accession_id, null::numeric) as Entity_Public_Float
    , get_simple_fact_by_accession('dei','EntityFilerCategory',a.accession_id, 'text'::character varying) as Entity_Filer_Category
    , get_simple_fact_by_accession('dei','DocumentFiscalYearFocus',a.accession_id, 'text'::character varying) as Document_Fiscal_Year_Focus
    , get_simple_fact_by_accession('dei','DocumentFiscalPeriodFocus',a.accession_id, 'text'::character varying) as Document_Fiscal_Period_Focus
    , get_simple_fact_by_accession('dei','DocumentPeriodEndDate',a.accession_id, null::date) as Document_Period_End_Date
    FROM accession a 
    filing_date = '2018-03-01'

What amounts have been reported with an element using the string ‘derivative liabilit’ since 2013?

SELECT DISTINCT on (element_local_name) element_local_name
    , fact_id
    , element_namespace
    , fiscal_period
    , fiscal_year
    , calendar_period
    , calendar_year
    , fact_value
FROM fact f
WHERE LOWER(element_local_name) LIKE '%derivative%liabilit%'
and fiscal_year > 2013
ORDER by element_local_name, fact_id DESC;

Q. What were the last 100 IFRS filings made to the SEC?

FROM document d
JOIN dts_document dd
  ON d.document_id = dd.document_id
JOIN report r
  ON dd.dts_id = r.dts_id
WHERE d.document_uri like 'http://xbrl.ifrs.org/taxonomy/%'
ORDER BY accepted_timestamp DESC
limit 100

Q. How many filings have been made using the 2017 taxonomy?

SELECT count(*)
FROM  document d
JOIN dts_document dd 
  ON dd.document_id = d.document_id
JOIN report r 
  ON r.dts_id = dd.dts_id
WHERE d.document_uri = 'http://xbrl.fasb.org/us-gaap/2017/elts/us-gaap-2017-01-31.xsd'

Q. What examples of non numerical transforms have been used in the latest SEC inline filings?

SELECT a.entity_name
	, document_type
	, filing_date
	, filing_accession_number
	, creation_software
	, f.fact_value
	, f.inline_display_value
	, f.element_local_name
	, element_namespace
	, inline_scale
	, q.local_name as transform
FROM accession a
JOIN fact f 
	ON f.accession_id = a.accession_id
JOIN qname q 
	ON q.qname_id = f.inline_format_qname_id
WHERE entry_type = 'inline' 
AND f.inline_is_hidden = false
AND inline_format_qname_id <> 22041680
AND inline_format_qname_id <> 22041682
AND filing_date > '2018-01-01'
ORDER BY a.accession_id desc
LIMIT 1000

Q. What are the largest XBRL filings (In facts Reported) since the 1st March 2018?

SELECT r.entity_name, r.source_report_identifier, count(*) 
report r
JOIN fact f ON f.accession_id = r.report_id
WHERE true
AND accepted_timestamp > '2018-03-01'
ORDER BY count(*) DESC

Q. How many hidden elements have ben used in inline filings by report since the start of 2018?

	, entry_url
	, r.properties ->> 'document_type'::text AS document_type
	, r.properties ->> 'filing_date'::text AS document_type
	, count(*) 
FROM fact f
JOIN report r
	ON r.report_id = f.accession_id
WHERE true
	AND entry_type = 'inline'
	AND inline_is_hidden
	AND accepted_timestamp > '2018-01-01'
GROUP BY  1,2,3,4
ORDER BY count(*) DESC

Q. What elements has Microsoft used in its presentation linkbases?

, r.entity_name
, (r.properties ->> 'standard_industrial_classification'::text)::integer AS standard_industrial_classification
, r.properties ->> 'document_type'::text AS document_type
, r.report_id
, r.dts_id
, q.local_name
, n.description
, q.namespace
from report r
join entity en ON r.entity_id = en.entity_id
JOIN dts_network n ON n.dts_id = r.dts_id
join dts_relationship re ON re.dts_network_id = n.dts_network_id
join element e ON e.element_id = re.to_element_id
join qname q ON q.qname_id = e.qname_id
where r.entity_name ilike '%microsoft%'
and period_index = 1
--and abstract = false
and n.extended_link_qname_id = 10 
order by description, tree_sequence

Q. What Taxonomy versions were used for Federal Signature Corporation?

SELECT  base_taxonomy_name(report_id), * 
FROM report
WHERE entity_name = 'Federal Signal Corporation'