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?
SELECT *
FROM accession
WHERE entity_name ilike '%Apple Inc%'
Q. What is the list of base Taxonomies in the database?
SELECT
uri
, ns.taxonomy_version_id
, prefix
, ns.name AS ns_namespace
, version
, tx.name AS taxonomy_name
FROM
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
,f.fiscal_year
,f.calendar_period
,f.calendar_year
,f.fact_id
,f.fact_value
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
FROM
get_simple_fact_by_accession('dei','CurrentFiscalYearEndDate'
, (SELECT
max(accession_id)
FROM accession
WHERE entity_name ilike '%Apple Inc%')
,'text'::varchar
) 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
WHERE entity_name ILIKE '%BARRACUDA NETWORKS INC%'))
dt
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?
SELECT *
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
filing_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
WHERE
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(*)
FROM
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?
SELECT
entity_name
, 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?
SELECT
en.entity_code
, 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'
Comment
You must be logged in to post a comment.