queries.yaml

#
# Queries for Proceedings Title Parser Event lookup database
#
# WF 2020-08-22
'Count of Events grouped by source':
    sql: |  
        select 
            source,count(*) as count 
            from event
            group by source
        union 
        select 
            "TOTAL",count(*) as count
            from event
            order by count
    sparql: |
        PREFIX cr: <http://cr.bitplan.com/>
        SELECT  ?source (COUNT(?source) AS ?sourcecount)
        WHERE { 
           ?event cr:Event_source ?source.
        }
        GROUP by ?source
'Event View Schema':
    sql: |
        pragma table_info('event');
'Event_CEURWS Schema':
    sql: |
        pragma table_info('event_CEURWS');        
'Event_confref Schema':
    sql: |
        pragma table_info('event_confref');   
'Event_dblp Schema':
    sql: |
        pragma table_info('event_dblp');                
'Event_crossref Schema':
    sql: |
        pragma table_info('event_crossref');
'Event_or Schema':
    sql: |
        pragma table_info('event_or');
'Event_wikicfp Schema':
    sql: |
        pragma table_info('event_wikicfp');        
'Event_wikidata Schema':
    sql: |
        pragma table_info('event_wikidata');        
'Portuguese Anais with numbers':
    sql: |
        select number,title,url from event_crossref
        where number is not null and title like '%Anais%'
        order by number   
'Crossref number usage':
    sql: |
        select count(*) as count,cast(number as integer) as enum,number from event_crossref
        where number is not null
        group by enum,number
        having count>1
        order by enum        
'Crossref events with long numbers':
    sql: |
        select number,cast(number as integer) as enum,year,title 
        from event_crossref
        where  enum> 53 and enum<1952     
'Confref country distribution':
    sql: |
        select count(*) as count,country 
        from event_confref
        group by country
        order by 1 desc    
'WikiCfP year distribution':
    sql: |
        select year,count(*) as count
        from event_wikicfp
        group by year
        order by year      
'OPENRESEARCH year distribution':
    sql: |
        select year,count(*) as count
        from event_or
        group by year
        order by year     
'wikidata enums and ordinals':
    sql: |
        select count(*) as count,enum,ordinal 
        from event_wikidata 
        where enum is not null
        group by enum,ordinal
        union 
        select count(*) as count,'total',''
        from event_wikidata 
        where enum is not null
        order by 1 desc limit 20     
'Countries with capitals':
    sql: |
        select ci.*,co.*
        from city_github ci join country_github co 
        on ci.name=co.capital and co.isocode=ci.country
        order by co.isocode                             
'Predicate usage': 
    sparql: |
        SELECT ?p (count(?p) as ?numberOfPredicates) 
        WHERE {
            ?s ?p ?o .
        }
        group by ?p
        order by desc(?numberOfPredicates)    
'Predicate usage grouped by source':
    sparql: |
        PREFIX cr: <http://cr.bitplan.com/>
        SELECT ?p (count(?p) as ?numberOfPredicates) ?source
        WHERE {
            ?s ?p ?o ;
            cr:Event_source ?source.
        }
        group by ?p ?source
        order by ?source desc(?numberOfPredicates)
'Basic event information sample': 
    sparql: |
        PREFIX cr: <http://cr.bitplan.com/>
        SELECT ?eventId ?acronym ?series ?name ?year ?country ?city ?startDate ?endDate ?url ?source WHERE { 
           OPTIONAL { ?event cr:Event_eventId ?eventId. }
           OPTIONAL { ?event cr:Event_acronym ?acronym. }
           OPTIONAL { ?event cr:Event_series ?series. }
           OPTIONAL { ?event cr:Event_title ?title. }
           OPTIONAL { ?event cr:Event_name ?name. }
           OPTIONAL { ?event cr:Event_year ?year.  }
           OPTIONAL { ?event cr:Event_country ?country. }
           OPTIONAL { ?event cr:Event_city ?city. }
           OPTIONAL { ?event cr:Event_startDate ?startDate. }
           OPTIONAL { ?event cr:Event_endDate ?endDate. }
           OPTIONAL { ?event cr:Event_url ?url. }
           ?event cr:Event_source ?source.
        }    
        limit 20