vgrabovets
10/25/2016 - 3:08 PM

sales

sales

SELECT
  video_id
, title
, ROUND(SUM(main.amount * cur.to_usd), 2) sales_usd
, count(video_id) sales_qty

FROM
  (
    SELECT
      YEAR(date) * 100 + MONTH(date) month_id
    , amount
    , shown_amount
    , currency_id
    , geo
    , video_id
    , v.title
    , title_en
    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 ms.type IN (1, 5)
    AND video_id IN
    (9328)
    AND 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
        HAVING month_id <> 201609 UNION SELECT
          currency_id
        , to_usd
        , YEAR
          (date) * 100 + MONTH(date) month_id
        FROM
          test.currency_201609
        HAVING month_id = 201609
      ) cur ON cur.currency_id = main.currency_id
        AND main.month_id = cur.month_id
  GROUP BY video_id, title
  
  
  
  #################################
  
  
  
  
  SELECT
  date
, video_id
, title
, COUNT(video_id) sales_qty

FROM
  (
    SELECT
      YEAR(date) * 100 + MONTH(date) month_id
    , DATE(date)                     date
    , amount
    , shown_amount
    , currency_id
    , geo
    , video_id
    , v.title
    , title_en
    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 ms.type NOT IN (1, 5)
    AND o.closed = '1'
    AND o.type IN
    (
      SELECT
        type
      FROM
        daily.dict_pay_system m
      WHERE m.group = 'M'
    )
    AND o.video_id IN (1913061, 2590891, 2015481, 2029171, 2489411)
    AND date > '2016-10-01'
  ) main


  GROUP BY video_id, date, title