vgrabovets
12/29/2016 - 10:17 AM

subscription sales from titles

subscription sales from titles

SELECT
  video_id
, title
, geo
, COUNT(video_id) sales_qty

FROM
  (
    SELECT
      o.service_id
    , ms.title sub_title
    , amount
    , shown_amount
    , currency_id
    , geo
    , video_id
    , v.title
    , title_en
    , o.date
    , o.description
    , o.operation
    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 video_id IN (1956471, 1301471, 14391, 9237, 2365681)
    AND o.closed = '1'
    AND o.geo = 'RU'
    AND o.service_id IN (12661,
    12671,
    14971,
    15251,
    15731,
    16761,
    18251
    )
    --     and o.date >= '2016-11-02'
    AND o.type IN
    (
      SELECT
        type
      FROM
        daily.dict_pay_system m
      WHERE m.group = 'M'
    )
  ) main

  GROUP BY video_id, title, geo