@prefix ex: <https://kg.earthmetabolome.org/metrin/api/.well-known/examples/> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix schema: <https://schema.org/> .
@prefix sh: <http://www.w3.org/ns/shacl#> .
@prefix spex:<https://purl.expasy.org/sparql-examples/ontology#> .

ex:009 a sh:SPARQLExecutable,
        sh:SPARQLSelectExecutable ;
    rdfs:comment "Which samples have features annotated as Aspidosperma_type alkaloids by CANOPUS with a probability score above 0.5, ordered by the decreasing count of features?"@en ;
    sh:prefixes _:sparql_examples_prefixes ;
    sh:select """PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX prov: <http://www.w3.org/ns/prov#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX emi: <https://w3id.org/emi#>
PREFIX sosa: <http://www.w3.org/ns/sosa/>

SELECT ?extract ?organe ?species_name ?genus_name ?family_name ?count_of_selected_class
WHERE {
    ?material sosa:hasSample ?extract ;
        sosa:isSampleOf ?organe .
    ?organe emi:inTaxon ?wd_sp .
    #OPTIONAL {
        SERVICE <https://query.wikidata.org/sparql> {
            ?wd_sp wdt:P225 ?species_name .
            ?family wdt:P31 wd:Q16521 ;
                wdt:P105 wd:Q35409 ;
                wdt:P225 ?family_name ;
                ^wdt:P171* ?wd_sp .
            ?genus wdt:P31 wd:Q16521 ;
                wdt:P105 wd:Q34740 ;
                wdt:P225 ?genus_name ;
                ^wdt:P171* ?wd_sp .
        }
    #}
    {
        SELECT ?extract (COUNT(DISTINCT ?feature) AS ?count_of_selected_class)
        WHERE {
            ?extract a emi:ExtractSample ;
                sosa:isFeatureOfInterestOf ?lcms .
            ?lcms a emi:LCMSAnalysis ;
                emi:hasLCMSFeatureSet ?feature_list .
            ?feature_list emi:hasLCMSFeature ?feature .
            ?feature emi:hasAnnotation ?canopus .
            ?canopus a emi:ChemicalTaxonAnnotation ;
                emi:hasClass ?np_class ;
                emi:hasClassProbability ?class_prob .
            ?np_class rdfs:label "Aspidosperma type" .
            FILTER((?class_prob > 0.5)) .
        } GROUP BY ?extract ORDER BY DESC(?count_of_selected_class)
    }
}""" ;
    schema:target <https://kg.earthmetabolome.org/metrin/api/> ;
    spex:federatesWith <https://query.wikidata.org/sparql> .