YAr

имеено такой запрос используется у нас для подсчета кол-во установок

SELECT date_trunc('day', src.install_date) AS date_install,

      COUNT(DISTINCT dev.id) AS users

  FROM sources AS src

  INNER JOIN devices AS dev ON src.device_id=dev.id

  AND src.application_id=dev.application_id

  WHERE 1 = 1

   AND src.application_id = 1

   AND (src.install_date BETWEEN to_date('15-11-2017', 'DD-MM-YYYY') AND to_date('15-11-2017', 'DD-MM-YYYY') )

  GROUP BY date_install;

по какому запросу вы получаете revenue

Это пример

SELECT date_trunc('day', src.install_date) AS date_install,

      COALESCE(date_trunc('day', st.date), src.install_date) AS date_advt,

      COALESCE(SUM(st.revenue_per_impression), 0) AS impression_revenue,

      COALESCE(SUM(st.revenue_per_click), 0) AS click_revenue,

      COALESCE(SUM(ip.revenue), 0) AS inapps_revenue

  FROM sources AS src

  LEFT OUTER JOIN daily_stats AS st ON src.device_id=st.device_id

  AND st.date >= to_date('01-10-2017', 'DD-MM-YYYY')

  AND st.application_id=src.application_id

  INNER JOIN devices AS dev ON src.device_id=dev.id

  AND src.application_id=dev.application_id

  LEFT OUTER JOIN daily_inapps AS ip ON src.device_id=ip.device_id

  AND st.date=ip.date

  WHERE 1 = 1

   AND src.application_id = 2

   AND (src.install_date BETWEEN to_date('01-10-2017', 'DD-MM-YYYY') AND to_date('31-10-2017', 'DD-MM-YYYY') )

   AND src.install_date < date_trunc('day', now()) - INTERVAL '1 day'

  GROUP BY date_install,

      date_advt

https://prod-bitool.easybrain.com/cohort_data?report_type=1&period=1&date=15-11-2017+-+16-11-2017&days_limit=30&without_cache=1&application=1&advt_revenue_type=1&zero_idfa=1#

Мой запрос на получение когорты

SELECT install_date, count(*) FROM sources WHERE application_id=1 AND install_date BETWEEN '2017-11-15' AND '2017-11-16' and device_id is not null GROUP BY install_date

Мой revenue

SELECT 'u_15',count(DISTINCT device_id) FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-15 00:00:00' AND sources.application_id = 1 

UNION ALL

SELECT 'u_16',count(DISTINCT device_id) FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-16 00:00:00' AND sources.application_id = 1 UNION ALL

SELECT 'umpr_15',sum(revenue_per_impression) FROM daily_stats WHERE device_id IN (SELECT device_id FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-15 00:00:00' AND sources.application_id = 1) AND application_id = 1 AND DATE < '2017-11-16'

UNION ALL

SELECT 'umpr_16',sum(revenue_per_impression) FROM daily_stats WHERE device_id IN (SELECT device_id FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-16 00:00:00' AND sources.application_id = 1) AND application_id = 1 AND DATE < '2017-11-17'

Мой запрос на Impressions

SELECT 'u_23',count(DISTINCT device_id) FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-15 00:00:00' AND sources.application_id = 1 AND device_id IN (SELECT id FROM devices WHERE idfa <> '00000000-0000-0000-0000-000000000000')

UNION ALL

SELECT 'u_24',count(DISTINCT device_id) FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-16 00:00:00' AND sources.application_id = 1 AND device_id IN (SELECT id FROM devices WHERE idfa <> '00000000-0000-0000-0000-000000000000')

UNION ALL

SELECT 'umpr_23',sum(impressions) FROM daily_stats WHERE device_id IN (SELECT device_id FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-15 00:00:00' AND sources.application_id = 1 AND device_id IN (SELECT id FROM devices WHERE idfa <> '00000000-0000-0000-0000-000000000000')) AND application_id = 1 AND DATE < '2017-11-17'

UNION ALL

SELECT 'umpr_24',sum(impressions) FROM daily_stats WHERE device_id IN (SELECT device_id FROM sources WHERE device_id IS NOT NULL AND install_date='2017-11-16 00:00:00' AND sources.application_id = 1 AND device_id IN (SELECT id FROM devices WHERE idfa <> '00000000-0000-0000-0000-000000000000')) AND application_id = 1 AND DATE < '2017-11-18'