promo code
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;