deposits.sql

WITH first_deposits AS
  (SELECT pt.user_id,
          MIN(created_at) AS first_deposit_date
   FROM affiliate_customer c,
        play_transaction pt
   WHERE pt.user_id = c.user_id
     AND pt.payment_status = '1'
     AND pt.transaction_type = '1'
     AND affiliate_id = 350
   GROUP BY pt.user_id),
     conversions_month as
  (select count(DISTINCT user_id) as conversions,
          date_trunc('month', first_deposit_date::date) as conversion_date,
          ROW_NUMBER() OVER () as rn
   from first_deposits
   WHERE first_deposit_date >= '2018-07-01'
     and first_deposit_date < '2018-10-01'
   group by date_trunc('month', first_deposit_date::date)
   order by 2),
     aff_data as
  (select sum(signups) as signups,
          sum(clicks) as clicks,
          sum(impressions) as impressions,
          date_trunc('month', aft.date)::date as track_month,
          ROW_NUMBER() OVER () as rn
   from affiliate_tracking aft
   join affiliate_trackingcode aftc on aftc.id = aft.tracking_code_id
   where aftc.affiliate_id = 350
     and (aft.date > '2018-07-01'
          and aft.date < '2018-10-01')
   group by date_trunc('month', aft.date)::date
   order by date_trunc('month', aft.date)::date)
select *
from aff_data a
join conversions_month b on b.rn = a.rn;