tomas_query.sql

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