Cacher is the code snippet organizer for pro developers

SELECT
user_id,
  period,
  sum(sales_usd) sales_usd
from
(SELECT
u.user_id,
  date_purchase,
  case when date_purchase < date_promo then 'before' when date_purchase > date_promo then 'after' else 'never' end period,
 ROUND(SUM(main.amount * cur.to_usd), 2) sales_usd


FROM

(select 
  user_id,
  date date_promo
  from billing.orders
  where promo_code = 'TVTEST2311' and year(date) = 2016 and closed = 1
  group by user_id) u

left join

  (
    SELECT
      YEAR(date) * 100 + MONTH(date) month_id
    , amount
    , shown_amount
    , currency_id
    , geo
    , user_id
    , date date_purchase
    FROM
      billing.orders o
--         LEFT JOIN megogo2.service ms ON ms.service_id = o.service_id
--         LEFT JOIN megogo2.video v ON v.obj_id = o.video_id

    WHERE o.closed = '1'
    AND o.type IN
    (
      SELECT
        type
      FROM
        daily.dict_pay_system m
      WHERE m.group = 'M'
    )
  ) main on u.user_id = main.user_id

    LEFT JOIN (
        SELECT
          currency_id
        , to_usd
        , YEAR(date) * 100 + MONTH(date) month_id
        FROM
          megogo_db.currency_daily
      ) cur ON cur.currency_id = main.currency_id
        AND main.month_id = cur.month_id



  GROUP BY u.user_id, date_purchase
 ) a
group by user_id, period;