User:Jarekt/queries

Many authors sharing OTRS number

SELECT ?otrs ?author ?countAuthors (sample(?file) as ?exampleFile) (COUNT(?file) AS ?countFiles)
WITH { 
	SELECT ?otrs (COUNT(distinct ?author) AS ?countAuthors)  {
	  ?file wdt:P6305 ?otrs .
	  ?file p:P170 ?statement .
	  ?statement ps:P170 ?authorS .                    # copyright status = public domain
	  Optional {?statement pq:P2093 ?authorStr . }
	  Optional {?statement pq:P4174 ?username .  }
	  BIND(IF(isBlank(?authorS), IF(isBlank(?username), IF(isBlank(?authorStr), "no value", ?authorStr), concat("User:",?username)), ?authorS) as ?author)
	} 
	group by ?otrs
	having (?countAuthors>1)
} AS %otrsList

WHERE {
      include %otrsList .
	  ?file wdt:P6305 ?otrs .
	  ?file p:P170 ?statement .
	  ?statement ps:P170 ?authorS .                    # copyright status = public domain
	  Optional {?statement pq:P2093 ?authorStr . }
	  Optional {?statement pq:P4174 ?username .  }
	  BIND(IF(isBlank(?authorS), IF(isBlank(?username), IF(isBlank(?authorStr), "no value", ?authorStr), concat("User:",?username)), ?authorS) as ?author)
} group by ?otrs ?author ?countAuthors
order by desc(?countAuthors)

Try it!