Affiliate extract

affiliate.sql

SELECT '7298' AS CampaignId,
'12959' AS ActionTrackerId,
token AS OrderId,
placed_at AS EventDate,
round(sum(i.products_price_cents 
    + i.products_tax_cents
    - i.products_discount_cents
    + i.shipping_fee_cents 
    + i.shipping_tax_cents
    - i.shipping_discount_cents
    + i.wrapping_price_cents 
    + i.wrapping_tax_cents
    - i.wrapping_discount_cents
    )/100.0, 2) AS Amount,
'USD' AS CurrencyCode,
om.irclickid ClickId
FROM store_orders o
JOIN store_order_line_items l ON (o.id = l.order_id)
join store_order_metadatas om on (om.order_id = o.id)
JOIN store_invoice_line_items i ON (l.id = i.order_line_item_id)
WHERE token IN ('W57551700')
GROUP BY o.id, om.irclickid;