User:Máté - Wikidata

# musicals and rock operas first performed in Hungary with the list of authors
SELECT DISTINCT ?musical ?musicalLabel ?premieredate ?premiereplaceLabel
(GROUP_CONCAT(DISTINCT(?author); separator=" – ") as ?authors)
WHERE {
  { ?musical wdt:P31/wdt:P279* wd:Q2743 . }
  UNION
  { ?musical wdt:P31/wdt:P279* wd:Q253137 . }
  ?musical p:P1191 [
    ps:P1191 ?premieredate;
    pq:P276 ?premiereplace
  ] .
  ?premiereplace wdt:P131+ wd:Q28
  OPTIONAL { ?musical (wdt:P86|wdt:P676|wdt:P87)/rdfs:label ?author FILTER (lang(?author) = "hu") . }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "hu" .
  }
} GROUP BY ?musical ?musicalLabel ?premieredate ?premiereplaceLabel ORDER BY ASC(?premieredate)

# ratings of Hungarian films
SELECT DISTINCT ?film ?filmLabel ?ratingLabel ?age ?countryLabel
WHERE {
  [] wdt:P31/wdt:P279* wd:Q24716199; wikibase:directClaim ?p .
  ?film wdt:P31/wdt:P279* wd:Q11424;
        wdt:P495 wd:Q28;
        ?p ?rating .
  ?rating wdt:P2899 ?age; wdt:P31/wdt:P361/wdt:P17 ?country .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "hu,en" .
  }
} ORDER BY ?filmLabel ?countryLabel

# Russian film titles without Hungarian transcriptions
SELECT ?film ?filmLabel ?title
WHERE {
  ?film wdt:P31/wdt:P279* wd:Q11424;
        p:P1476 ?s .
  ?s ps:P1476 ?title .
  MINUS { ?s pq:P2719 [] . }
  FILTER ( lang(?title) = "ru" ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "hu" . }
}

# same-sex supercouples
SELECT DISTINCT ?item ?itemLabel ?workLabel ?sexLabel
WHERE {
  ?item wdt:P31 wd:Q1274180;
        wdt:P527 ?one, ?two . FILTER ( ?one != ?two )
  ?one wdt:P21 ?sex . ?two wdt:P21 ?sex .
  OPTIONAL { ?item wdt:P1441 ?work . }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
  }
}

# languages qualifier Hungarian-style transcription (P2719) is used with by number of statements, identified by their Hungarian labels
SELECT ?lang (COUNT(?v) AS ?num)
WHERE {
  [] #wikibase:propertyType wikibase:Monolingualtext;
     wikibase:statementProperty ?ps .
  ?v pq:P2719 ?trans; ?ps ?text .
  BIND (lang(?text) AS ?wmlc) .
  OPTIONAL { [] wdt:P424 ?wmlc; wdt:P31 wd:Q34770; rdfs:label ?lang .
            FILTER (lang(?lang) = "hu") . }
} GROUP BY ?lang ORDER BY DESC(?num)

SELECT ?film (count(distinct ?p) AS ?numberOfRatingProperties)
WHERE {
  [] wdt:P31/wdt:P279* wd:Q24716199; wikibase:claim ?p .
  ?film wdt:P31/wdt:P279* wd:Q11424; ?p []
} GROUP BY ?film ?filmLabel ORDER BY DESC(?numberOfRatingProperties) LIMIT 10

#check for RTC ratings with the same rating ID
SELECT ?item1 ?item2
WHERE {
  ?item1 p:P3834 [ pq:P2676 ?r ] .
  ?item2 p:P3834 [ pq:P2676 ?r ] .
  FILTER ( ?item1 != ?item2 )
}

#FSK ratings with source URL but no Prüfnummer
SELECT * WHERE {
  ?item p:P1981 ?s.
  ?s prov:wasDerivedFrom/pr:P854 ?source.
  MINUS { ?s pq:P2676 [] }
  FILTER(CONTAINS(STR(?source), "fsk.de"))
}

#number of movie ratings by date of original release
SELECT ?year ((round(avg(?age)*10)/10) AS ?avg)
       ((round(sum((?age-?avg)*(?age-?avg))*100/count(?age))/100) AS ?var)
       (count(distinct ?film) AS ?films) (count(?age) AS ?ratings)
WHERE {
  { SELECT ?film ?age (year(min(?date)) AS ?year)
    WHERE {
      [] wdt:P31/wdt:P279* wd:Q24716199; wikibase:directClaim ?p .
      ?film wdt:P31/wdt:P279* wd:Q11424; wdt:P577 ?date; ?p [ wdt:P2899 ?age ] .
    } GROUP BY ?film ?age
  }
  { SELECT ?year (avg(?avgf) AS ?avg)
    WHERE {
      { SELECT ?film (year(min(?date)) AS ?year) (avg(?age) AS ?avgf)
        WHERE {
          [] wdt:P31/wdt:P279* wd:Q24716199; wikibase:directClaim ?p .
          ?film wdt:P31/wdt:P279* wd:Q11424; wdt:P577 ?date; ?p [ wdt:P2899 ?age ] .
        } GROUP BY ?film
      }
    } GROUP BY ?year
  }
} GROUP BY ?year ORDER BY DESC(?year)

#number of films by number of ratings
SELECT (count(?item) as ?numberOfFilms) ?numberOfRatingProperties
WHERE {
  SELECT ?item (count(distinct ?p) AS ?numberOfRatingProperties)
  WHERE {
    [] wdt:P31/wdt:P279* wd:Q24716199; wikibase:claim ?p .
    ?item wdt:P31/wdt:P279* wd:Q11424; ?p [] .
  } GROUP BY ?item
} GROUP BY ?numberOfRatingProperties

#check for potential wrong Hungarian transcription of 上 in Japanese names
#居/宇
SELECT * WHERE { ?item wdt:P31 wd:Q5; wdt:P1814 []; rdfs:label ?jaLabel; rdfs:label ?huLabel .
                FILTER( lang(?jaLabel) = "ja" && CONTAINS(?jaLabel,"上") )
                FILTER( lang(?huLabel) = "hu" && CONTAINS(lcase(?huLabel),"ó") )
                #MINUS{ ?item rdfs:label ?huLabel .
                #      FILTER( lang(?huLabel) = "hu"
                #             && (CONTAINS(lcase(?huLabel),"ue")|| CONTAINS(lcase(?huLabel),"ka"))
                #            )
                #     }
               }