Skip to content

Instantly share code, notes, and snippets.

@josemoralesp
Created July 12, 2018 19:55
Show Gist options
  • Save josemoralesp/abd3a46b8365c5c005465f88c6fdcc0e to your computer and use it in GitHub Desktop.
Save josemoralesp/abd3a46b8365c5c005465f88c6fdcc0e to your computer and use it in GitHub Desktop.
WITH 90s AS (
SELECT
s.amz_instance_id, ap.id as id, %s as period,
SUM(sl.product_uom_qty) qty, SUM(sl.price_subtotal) amount
FROM sale_order_line sl
JOIN sale_order s ON
sl.order_id = s.id AND s.date_order >= %s
AND s.amz_instance_id in %s
JOIN amazon_product_ept ap ON
ap.instance_id = s.amz_instance_id AND
ap.product_id = sl.product_id
WHERE
ap.id IN %s AND sl.state = 'sale'
GROUP BY s.amz_instance_id, ap.id
), 30s AS (
SELECT
s.amz_instance_id, ap.id as id, %s as period,
SUM(sl.product_uom_qty) qty, SUM(sl.price_subtotal) amount
FROM sale_order_line sl
JOIN sale_order s ON
sl.order_id = s.id AND s.date_order >= %s
AND s.amz_instance_id in %s
JOIN amazon_product_ept ap ON
ap.instance_id = s.amz_instance_id AND
ap.product_id = sl.product_id
WHERE
ap.id IN %s AND sl.state = 'sale'
GROUP BY s.amz_instance_id, ap.id
) UPDATE amazon_product_ept AS p
SET
sales_90d_amount=COALESCE(m.amount, 0)
sales_90d_qty=COALESCE(m.qty, 0)
sales_30d_amount=COALESCE(n.amount, 0)
sales_30d_qty=COALESCE(n.qty, 0)
FROM
90s AS m ON m.id=p.id
LEFT OUTER JOIN
30s AS n ON n.id=p.id
WHERE id IN %s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment