vgrabovets
12/27/2016 - 4:49 PM

user sales

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