user sales
SELECT
user_id
, date
, ROUND(SUM(main.amount * cur.to_usd), 2) sales_usd
FROM
(
SELECT
YEAR(date) * 100 + MONTH(date) month_id
,date(date) date
, user_id
, amount
, shown_amount
, currency_id
, geo
, video_id
FROM
billing.orders o
WHERE o.closed = '1'
AND o.type IN
(
SELECT
type
FROM
daily.dict_pay_system m
WHERE m.group = 'M'
)
) main
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 user_id, date