Shopware Kibana Item Sales Query

item_sales.sql

SELECT
    # Reference numbers and IDs
    sod.id as orderDetailsID, sod.id as doc_id, NOW() as indexed_at, sod.orderID, sod.modus, sod.ordernumber, sod.articleordernumber, sod.name, cat.description as category, su.customergroup,
    # Prices
    sod.price, sod.quantity, (sod.price * sod.quantity) as total_price, sod.tax_rate, (sod.price / so.currencyFactor) as price_eur, (sod.price * sod.quantity / so.currencyFactor) as total_price_eur,
    # Article status
    sod_status_scs.`description` as item_order_status,
    # Shipping
    sod_shipping_scss.`description` as item_shipping_status, sod.shipped,
    # Parent invoice
    so_scs.`description` as order_status, so.ordertime, scp.name as payment_method, so.net, so.taxfree, so.currency, so.currencyFactor, so.subshopID, scs.name as shop_name, so.deviceType,
    # Billing
    sob.customernumber as billing_customernumber, sob.zipcode as billing_zip, sob.city as billing_city, scc_sob.countryname as billing_country, scc_sob.countryiso as billing_country_iso,
    # Shipping
    sos.zipcode as shipping_zip, sos.city as shipping_city, scc_sos.countryname as shipping_country, scc_sos.countryiso as shipping_country_iso,
    # Article
    sod.ean, soda.attribute1 as orderitem_attr1
FROM
    s_order_details sod
        # Order tables joined
        LEFT JOIN s_order_details_attributes soda ON sod.id = soda.detailID
        INNER JOIN s_order so ON sod.orderID = so.id
        LEFT JOIN s_order_attributes soa ON so.id = soa.orderID
        # Article category
        LEFT JOIN (SELECT articleID, MIN(categoryID) as categoryID FROM  s_articles_categories GROUP BY articleID) catId ON catId.articleID = sod.articleID
        LEFT JOIN s_categories cat ON catId.categoryID = cat.id
        # User
        LEFT JOIN s_user su ON so.userID = su.id
        # Shop name
        LEFT JOIN s_core_shops scs ON so.subshopID = scs.id
        # Payment info
        LEFT JOIN s_core_paymentmeans scp ON so.paymentID = scp.id
        # Order state
        LEFT JOIN s_core_states so_scs ON so.status = so_scs.id
        # Order state of article
        LEFT JOIN s_core_detail_states sod_status_scs ON sod.status = sod_status_scs.id
        # Shipping state of article
        LEFT JOIN s_core_states sod_shipping_scss ON sod.shipped = sod_shipping_scss.id
        # Billing info
        LEFT JOIN s_order_billingaddress sob ON so.id = sob.orderID
        LEFT JOIN s_core_countries scc_sob ON sob.countryID = scc_sob.id
        # Shipping info
        LEFT JOIN s_order_shippingaddress sos ON so.id = sos.orderID
        LEFT JOIN s_core_countries scc_sos ON sos.countryID = scc_sos.id
        # Article info
        LEFT JOIN s_articles_details sad ON sod.articleordernumber = sad.ordernumber
        LEFT JOIN s_articles_attributes saa ON sad.id = saa.articledetailsID