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
  • SELECT  
        , 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?

    SELECT r.*
    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'
    GROUP BY 1,2
    ORDER BY count(*) DESC
    LIMIT 10

    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