PREFIX foaf:       <http://xmlns.com/foaf/0.1/>
PREFIX dct:        <http://purl.org/dc/terms/>
PREFIX schema:     <https://schema.org/>
PREFIX xsd:        <http://www.w3.org/2001/XMLSchema#>

CONSTRUCT
{
    GRAPH ?graph
    {
        ?order schema:orderedItem ?orderItem .

        ?orderItem a schema:OrderItem ;
            schema:orderedItem ?product ;
            schema:orderQuantity ?quantity ;
            schema:discount ?discount ;
            schema:price ?price .
    }
}
WHERE
{
    ?order_detail_row <#orderID> ?orderID ;
        <#productID> ?productID ;
        <#unitPrice> ?unitPrice ;
        <#quantity> ?quantityString ;
        <#discount> ?discountString .

    BIND(uri(concat(str($base), "orders/", encode_for_uri(?orderID), "/")) AS ?graph)
    BIND(uri(concat(str(?graph), "#this")) AS ?order)
    BIND(uri(concat(str(?graph), "#", STRUUID())) AS ?orderItem)
    BIND(uri(concat(str($base), "products/", encode_for_uri(?productID), "/#this")) AS ?product)
    BIND (STRDT(?unitPrice, xsd:float) AS ?price)
    BIND (STRDT(?quantityString, xsd:integer) AS ?quantity)
    BIND (STRDT(?discountString, xsd:float) AS ?discount)
}

PREFIX foaf:       <http://xmlns.com/foaf/0.1/>
PREFIX dct:        <http://purl.org/dc/terms/>
PREFIX schema:     <https://schema.org/>
PREFIX xsd:        <http://www.w3.org/2001/XMLSchema#>

CONSTRUCT
{
    GRAPH ?graph
    {
        ?graph dct:title ?orderID ;
            foaf:primaryTopic ?order ;
            foaf:topic ?orderDelivery, ?deliveryAddress .

        ?order a schema:Order ;
            schema:identifier ?orderID ;
            dct:title ?orderID ;
            schema:customer ?customer ;
            schema:broker ?employee ;
            schema:orderDate ?orderDate ;
            schema:orderDelivery ?orderDelivery .

        ?orderDelivery a schema:ParcelDelivery ;
            foaf:page ?graph ;
            schema:expectedArrivalUntil ?requiredDate ;
            # ?shippedDate ;
            schema:deliveryAddress ?deliveryAddress ;
            schema:provider ?shipper .

        ?deliveryAddress a schema:PostalAddress ;
            foaf:page ?graph ;
            schema:addressCountry ?shipCountry ;
            schema:addressLocality ?shipCity ;
            schema:postalCode ?shipPostalCode ;
            schema:streetAddress ?shipAddress ;
            schema:location ?deliveryLocation ;
            schema:addressRegion ?shipRegion .
    }
}
WHERE
{
    ?order_row <#orderID> ?orderID ;
        <#customerID> ?customerID ;
        <#employeeID> ?employeeID ;
        <#orderDate> ?orderDateString ;
        <#requiredDate> ?requiredDateString ;
        <#shipVia> ?shipVia ;
        <#freight> ?freightString ;
        <#shipName> ?shipName ;
        <#shipAddress> ?shipAddress ;
        <#shipCity> ?shipCity ;
        <#shipPostalCode> ?shipPostalCode ;
        <#shipCountry> ?shipCountry .

    OPTIONAL {
        ?order_row <#shippedDate> ?shippedDateString
        BIND(strdt(?shippedDateString, xsd:date) AS ?shippedDate)
    }
    OPTIONAL {
        ?order_row <#shipRegion> ?shipRegion
    }

    BIND(uri(concat(str($base), "orders/")) AS ?container)
    BIND(uri(concat(str(?container), encode_for_uri(?orderID), "/")) AS ?graph)
    BIND(uri(concat(str(?graph), "#this")) AS ?order)
    BIND(uri(concat(str(?graph), "#delivery")) AS ?orderDelivery)
    BIND(uri(concat(str(?graph), "#delivery-address")) AS ?deliveryAddress)
    BIND(uri(concat(str($base), "employees/", encode_for_uri(?employeeID), "/#this")) AS ?employee)
    BIND(uri(concat(str($base), "customers/", encode_for_uri(?customerID), "/#this")) AS ?customer)
    BIND(uri(concat(str($base), "shippers/", encode_for_uri(?shipVia), "/#this")) AS ?shipper)
    BIND(strdt(?orderDateString, xsd:date) AS ?orderDate)
    BIND(strdt(?requiredDateString, xsd:date) AS ?requiredDate)
    BIND(strdt(?freightString, xsd:float) AS ?freight)
}

PREFIX schema: <https://schema.org/>

SELECT ?country (COUNT(DISTINCT ?order) AS ?orderCount)
WHERE {
    GRAPH ?orderGraph {
        ?order schema:orderDelivery ?delivery .
        ?delivery schema:deliveryAddress ?address .
        ?address schema:addressCountry ?country .
    }
}
GROUP BY ?country
ORDER BY DESC(?orderCount)
LIMIT 10

PREFIX schema: <https://schema.org/>

SELECT ?month (COUNT(DISTINCT ?order) AS ?orderCount)
WHERE {
    GRAPH ?orderGraph {
        ?order a schema:Order ;
               schema:orderDate ?orderDate .
        BIND(SUBSTR(STR(?orderDate), 1, 7) AS ?month)
    }
}
GROUP BY ?month
ORDER BY ?month

PREFIX  schema: <https://schema.org/>
PREFIX  xsd:  <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT  ?year ?regionName (SUM(?sale) AS ?totalSales)
WHERE
  { GRAPH ?orderGraph
      { ?order  schema:orderDate    ?orderDate ;
                schema:broker       ?employee ;
                schema:orderedItem  ?orderItem
        BIND(year(xsd:dateTime(?orderDate)) AS ?year)
        ?orderItem  schema:orderedItem  ?product ;
                  schema:orderQuantity  ?quantity ;
                  schema:price          ?price
        BIND(( ?quantity * ?price ) AS ?sale)
      }
    GRAPH ?employeeGraph
      { ?employee  schema:areaServed  ?territory }
    GRAPH ?territoryGraph
      { ?territory  schema:containedInPlace  ?region }
    GRAPH ?regionGraph
      { ?region  schema:name  ?regionName }
  }
GROUP BY ?year ?regionName
ORDER BY DESC(?year) ?regionName
LIMIT   100

PREFIX  schema: <https://schema.org/>

  SELECT DISTINCT  ?order
  WHERE
    { GRAPH ?doc
        { ?order  a                  schema:Order ;
                  schema:identifier  ?orderID ;
                  schema:orderDate   ?orderDate ;
                  schema:customer    ?customer ;
                  schema:broker      ?employee
        }
    }
  ORDER BY ?orderID