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

SELECT ?month (ROUND(AVG(?days) * 10) / 10 AS ?daysToShip)
WHERE {
    GRAPH ?orderGraph {
        ?order a schema:Order ;
               schema:orderDate ?orderDate ;
               schema:orderDelivery ?delivery .
        # the dispatch is a DeliveryEvent leg of the parcel's journey, so orders that
        # never shipped simply have no event and drop out of the average
        ?delivery schema:deliveryStatus ?shipment .
        ?shipment schema:startDate ?shippedDate .
        BIND(SUBSTR(STR(?orderDate), 1, 7) AS ?month)
        BIND((?shippedDate - ?orderDate) / xsd:dayTimeDuration("P1D") AS ?days)
    }
    # The order book stops part-way through its final month, which would render as a
    # cliff rather than a trend. Drop that month — derived from the data, not pinned
    # to a date, so it stays correct if the dataset grows.
    {
        SELECT (SUBSTR(STR(MAX(?anyDate)), 1, 7) AS ?partialMonth)
        WHERE {
            GRAPH ?g { ?anyOrder a schema:Order ; schema:orderDate ?anyDate }
        }
    }
    FILTER (?month < ?partialMonth)
}
GROUP BY ?month
ORDER BY ?month

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

SELECT ?carrier (ROUND(SUM(IF(?status = schema:OrderProblem, 1, 0)) * 1000 / COUNT(?order)) / 10 AS ?latePercent)
WHERE {
    GRAPH ?orderGraph {
        ?order a schema:Order ;
               schema:orderStatus ?status ;
               schema:orderDelivery ?delivery .
        ?delivery schema:provider ?shipper .
    }
    GRAPH ?shipperGraph {
        ?shipper schema:legalName ?carrier .
    }
    # not yet dispatched is not the same as delivered late
    FILTER (?status != schema:OrderProcessing)
}
GROUP BY ?carrier
ORDER BY DESC(?latePercent)

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

SELECT ?month (ROUND(SUM(IF(?status = schema:OrderDelivered, 1, 0)) * 1000 / COUNT(?order)) / 10 AS ?onTimePercent)
WHERE {
    GRAPH ?orderGraph {
        ?order a schema:Order ;
               schema:orderDate ?orderDate ;
               schema:orderStatus ?status .
        BIND(SUBSTR(STR(?orderDate), 1, 7) AS ?month)
    }
    # an order still awaiting dispatch has no outcome yet - counting it as a miss would
    # drag the rate down for the months at the end of the book
    FILTER (?status != schema:OrderProcessing)
    # The order book stops part-way through its final month, which would render as a
    # cliff rather than a trend. Drop that month — derived from the data, not pinned
    # to a date, so it stays correct if the dataset grows.
    {
        SELECT (SUBSTR(STR(MAX(?anyDate)), 1, 7) AS ?partialMonth)
        WHERE {
            GRAPH ?g { ?anyOrder a schema:Order ; schema:orderDate ?anyDate }
        }
    }
    FILTER (?month < ?partialMonth)
}
GROUP BY ?month
ORDER BY ?month

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
    {
        # the order's title, exactly as orders.rq writes it: the two imports run concurrently, and whichever
        # reaches an order's document first creates it, which a document without a dct:title cannot be
        ?graph dct:title ?orderID ;
            foaf:topic ?orderItem, ?listPrice .

        ?order schema:orderedItem ?orderItem .

        # schema:OrderItem carries no price in schema.org - its only properties are
        # orderedItem, orderQuantity, orderItemNumber, orderItemStatus and orderDelivery.
        # Prices belong to schema:Offer, so the line is both: the item that was ordered
        # and the offer that was accepted for it. schema:Offer's own schema:price is the
        # price of the offer as a whole, i.e. what this line came to.
        ?orderItem a schema:OrderItem, schema:Offer ;
            schema:orderedItem ?product ;
            schema:itemOffered ?product ;
            schema:orderQuantity ?quantity ;
            schema:price ?lineTotal ;
            schema:priceCurrency "USD" ;
            schema:priceSpecification ?listPrice .

        # Northwind's per-line discount rate has no home in schema.org - schema:discount
        # is an Order-level property. Nothing is lost by dropping it: the undiscounted
        # unit price below, the quantity above and the line total pin the rate exactly,
        # and unlike a stored rate they cannot disagree with each other.
        ?listPrice a schema:UnitPriceSpecification ;
            schema:name "List price" ;
            schema:priceType schema:ListPrice ;
            schema:price ?price ;
            schema:priceCurrency "USD" .
    }
}
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)
    # a line is identified by its order and product (an order has at most one line per product), so re-running
    # the import writes the same resources rather than minting a second copy of every line
    BIND(uri(concat(str(?graph), "#", sha1(concat(?orderID, "/", ?productID)))) AS ?orderItem)
    BIND(uri(concat(str(?orderItem), "-list-price")) AS ?listPrice)
    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)
    BIND (STRDT(str(round(?quantity * ?price * (1 - ?discount) * 100) / 100), xsd:float) AS ?lineTotal)
}

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, ?shipment,
                ?shippingOffer, ?freightCharge .

        ?order a schema:Order ;
            schema:identifier ?orderID ;
            schema:name ?orderID ;
            schema:customer ?customer ;
            schema:broker ?employee ;
            schema:orderDate ?orderDate ;
            schema:orderStatus ?status ;
            schema:acceptedOffer ?shippingOffer ;
            schema:orderDelivery ?orderDelivery .

        ?orderDelivery a schema:ParcelDelivery ;
            foaf:page ?graph ;
            schema:partOfOrder ?order ;
            schema:expectedArrivalUntil ?requiredDate ;
            schema:deliveryStatus ?shipment ;
            schema:deliveryAddress ?deliveryAddress ;
            schema:provider ?shipper .

        # schema:ParcelDelivery has no price property either. A delivery charge is a
        # schema:DeliveryChargeSpecification, and the only way into a PriceSpecification
        # is schema:priceSpecification from an Offer - so the freight the customer
        # accepted is spelled out as exactly that.
        ?shippingOffer a schema:Offer ;
            foaf:page ?graph ;
            schema:name "Shipping" ;
            schema:priceSpecification ?freightCharge .

        ?freightCharge a schema:DeliveryChargeSpecification ;
            foaf:page ?graph ;
            schema:name "Freight" ;
            schema:price ?freight ;
            schema:priceCurrency "USD" ;
            schema:appliesToDeliveryMethod schema:ParcelService .

        # schema:ParcelDelivery has no date of its own for "when did this leave the warehouse".
        # The vocabulary models the journey as a series of schema:DeliveryEvent legs hanging
        # off schema:deliveryStatus; Northwind knows exactly one leg, the dispatch.
        ?shipment a schema:DeliveryEvent ;
            foaf:page ?graph ;
            schema:name ?shipmentName ;
            schema:startDate ?shippedDate .

        ?deliveryAddress a schema:PostalAddress ;
            foaf:page ?graph ;
            schema:addressCountry ?shipCountry ;
            schema:addressLocality ?shipCity ;
            schema:postalCode ?shipPostalCode ;
            schema:streetAddress ?shipAddress ;
            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 ;
        <#shipCountry> ?shipCountry .

    # ?shipment is bound inside the OPTIONAL so that the 21 orders that were never
    # dispatched get no DeliveryEvent at all, rather than an empty one. It is built from
    # $base (a constant, substituted before execution) and a re-matched ?orderID rather
    # than from ?graph: an OPTIONAL group is evaluated standalone, so variables BINDed in
    # the enclosing group are not in scope inside it.
    OPTIONAL {
        ?order_row <#orderID> ?orderID ;
            <#shippedDate> ?shippedDateString .
        BIND(strdt(?shippedDateString, xsd:date) AS ?shippedDate)
        BIND(uri(concat(str($base), "orders/", encode_for_uri(?orderID), "/#shipment")) AS ?shipment)
        BIND(concat("Shipped ", ?shippedDateString) AS ?shipmentName)
    }
    OPTIONAL {
        ?order_row <#shipRegion> ?shipRegion
    }
    # 19 orders carry no postal code; requiring one here dropped them from the import
    OPTIONAL {
        ?order_row <#shipPostalCode> ?shipPostalCode
    }

    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(?graph), "#shipping")) AS ?shippingOffer)
    BIND(uri(concat(str(?graph), "#freight")) AS ?freightCharge)
    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)
    BIND(IF(BOUND(?shippedDate),
            IF(?shippedDate > ?requiredDate, schema:OrderProblem, schema:OrderDelivered),
            schema:OrderProcessing) AS ?status)
}

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/>

SELECT ?month
       (ROUND(SUM(IF(?carrier = "Speedy Express", ?lineTotal, 0))) AS ?speedyExpress)
       (ROUND(SUM(IF(?carrier = "United Package", ?lineTotal, 0))) AS ?unitedPackage)
       (ROUND(SUM(IF(?carrier = "Federal Shipping", ?lineTotal, 0))) AS ?federalShipping)
WHERE {
    GRAPH ?orderGraph {
        ?order a schema:Order ;
               schema:orderDate ?orderDate ;
               schema:orderDelivery ?delivery ;
               schema:orderedItem ?orderItem .
        ?orderItem schema:price ?lineTotal .
        ?delivery schema:provider ?shipper .
    }
    GRAPH ?shipperGraph {
        ?shipper schema:legalName ?carrier .
    }
    BIND(SUBSTR(STR(?orderDate), 1, 7) AS ?month)
    # Drop the final, incomplete month - derived from the data rather than pinned to a date.
    {
        SELECT (SUBSTR(STR(MAX(?anyDate)), 1, 7) AS ?partialMonth)
        WHERE {
            GRAPH ?g { ?anyOrder a schema:Order ; schema:orderDate ?anyDate }
        }
    }
    FILTER (?month < ?partialMonth)
}
GROUP BY ?month
ORDER BY ?month

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 ;
                  schema:orderStatus ?status
        }
    }
  ORDER BY DESC(?orderDate)