#standardSQL
select COALESCE(pci_ytd.mrr_date, pci_qoq.mrr_date, pci_qtd.mrr_date,pci_mom.mrr_date, pci_mtd.mrr_date, pci_dod.mrr_date) as mrr_date,
COALESCE(pci_ytd.crm_account_id, pci_qoq.crm_account_id, pci_qtd.crm_account_id,pci_mom.crm_account_id, pci_mtd.crm_account_id,pci_dod.crm_account_id) as crm_account_id,
paid_crm_ind_qtd, paid_crm_ind_mtd,paid_crm_ind_ytd, paid_crm_ind_qoq, paid_crm_ind_mom,paid_crm_ind_dod
  from
  #YTD_returncustomers
      (
      select a.mrr_date, a.crm_account_id, case when sum(b.net_mrr_usd)>0 then 'Y' else 'N' end as paid_crm_ind_ytd
      from
        (
        select mrr_date, mrr_date_prior_yr_end, crm_account_id, sum(net_mrr_usd) net_mrr_usd, sum(net_mrr_usd_prior_yr_end) net_mrr_usd_prior_yr_end
        from edw_analyst_general.pop_mrr_granular_stage
        group by mrr_date, mrr_date_prior_yr_end, crm_account_id
        )a
      join
        (
        select mrr_date, crm_account_id, sum(net_mrr_usd) as net_mrr_usd from edw_analyst_general.pop_mrr_granular_stage
        group by mrr_date, crm_account_id
        )b on b.crm_account_id=a.crm_account_id
      where a.net_mrr_usd>0 and a.net_mrr_usd_prior_yr_end =0
      and b.mrr_date<a.mrr_date_prior_yr_end and b.mrr_date >= date_sub(a.mrr_date, interval 1 year)
      and a.crm_account_id ='0018000000xxTFjAAM'
      group by a.mrr_date, a.crm_account_id
    ) pci_ytd
  #QoQ_returncustomers
LEFT JOIN
      (
      select a.mrr_date, a.crm_account_id, case when sum(b.net_mrr_usd)>0 then 'Y' else 'N' end as paid_crm_ind_qoq
      from
        (
        select mrr_date, mrr_date_same_day_prior_qtr, crm_account_id, sum(net_mrr_usd) net_mrr_usd, sum(net_mrr_usd_same_day_prior_qtr) net_mrr_usd_same_day_prior_qtr
        from edw_analyst_general.pop_mrr_granular_stage
        group by mrr_date, mrr_date_same_day_prior_qtr, crm_account_id
        )a
      join
        (
        select mrr_date, crm_account_id, sum(net_mrr_usd) as net_mrr_usd from edw_analyst_general.pop_mrr_granular_stage
        group by mrr_date, crm_account_id
        )b on b.crm_account_id=a.crm_account_id
      where a.net_mrr_usd>0 and a.net_mrr_usd_same_day_prior_qtr =0
      and b.mrr_date<a.mrr_date_same_day_prior_qtr and b.mrr_date >= date_sub(a.mrr_date, interval 1 year)
      and a.crm_account_id ='0018000000xxTFjAAM'
      group by a.mrr_date, a.crm_account_id
    ) pci_qoq
    ON pci_ytd.mrr_date=pci_qoq.mrr_date and pci_ytd.crm_account_id=pci_qoq.crm_account_id
#qtd_returncustomers
LEFT JOIN
  (
  select a.mrr_date, a.crm_account_id, case when sum(b.net_mrr_usd)>0 then 'Y' else 'N' end as paid_crm_ind_qtd
  from
    (
    select mrr_date, mrr_date_prior_qtr_end, crm_account_id, sum(net_mrr_usd) net_mrr_usd, sum(net_mrr_usd_prior_qtr_end) net_mrr_usd_prior_qtr_end
    from edw_analyst_general.pop_mrr_granular_stage
    group by mrr_date, mrr_date_prior_qtr_end, crm_account_id
    )a
  join
    (
    select mrr_date, crm_account_id, sum(net_mrr_usd) as net_mrr_usd from edw_analyst_general.pop_mrr_granular_stage
    group by mrr_date, crm_account_id
    )b on b.crm_account_id=a.crm_account_id
  where a.net_mrr_usd>0 and a.net_mrr_usd_prior_qtr_end =0
  and b.mrr_date<a.mrr_date_prior_qtr_end and b.mrr_date >= date_sub(a.mrr_date, interval 1 year)
  and a.crm_account_id ='0018000000xxTFjAAM'
  group by a.mrr_date, a.crm_account_id
) pci_qtd
ON pci_qtd.mrr_date=pci_qoq.mrr_date and pci_qtd.crm_account_id=pci_qoq.crm_account_id
#MoM_returncustomers
LEFT JOIN
    (
    select a.mrr_date, a.crm_account_id, case when sum(b.net_mrr_usd)>0 then 'Y' else 'N' end as paid_crm_ind_mom
    from
      (
      select mrr_date, mrr_date_same_day_prior_mo, crm_account_id, sum(net_mrr_usd) net_mrr_usd, sum(net_mrr_usd_same_day_prior_mo) net_mrr_usd_same_day_prior_mo
      from edw_analyst_general.pop_mrr_granular_stage
      group by mrr_date, mrr_date_same_day_prior_mo, crm_account_id
      )a
    join
      (
      select mrr_date, crm_account_id, sum(net_mrr_usd) as net_mrr_usd from edw_analyst_general.pop_mrr_granular_stage
      group by mrr_date, crm_account_id
      )b on b.crm_account_id=a.crm_account_id
    where a.net_mrr_usd>0 and a.net_mrr_usd_same_day_prior_mo =0
    and b.mrr_date<a.mrr_date_same_day_prior_mo and b.mrr_date >= date_sub(a.mrr_date, interval 1 year)
    and a.crm_account_id ='0018000000xxTFjAAM'
    group by a.mrr_date, a.crm_account_id
  ) pci_mom
  ON pci_mom.mrr_date=pci_qtd.mrr_date and pci_mom.crm_account_id=pci_qtd.crm_account_id
#mtd_returncustomers
LEFT JOIN
  (
  select a.mrr_date, a.crm_account_id, case when sum(b.net_mrr_usd)>0 then 'Y' else 'N' end as paid_crm_ind_mtd
  from
    (
    select mrr_date, mrr_date_prior_mo_end, crm_account_id, sum(net_mrr_usd) net_mrr_usd, sum(net_mrr_usd_prior_mo_end) net_mrr_usd_prior_mo_end
    from edw_analyst_general.pop_mrr_granular_stage
    group by mrr_date, mrr_date_prior_mo_end, crm_account_id
    )a
  join
    (
    select mrr_date, crm_account_id, sum(net_mrr_usd) as net_mrr_usd from edw_analyst_general.pop_mrr_granular_stage
    group by mrr_date, crm_account_id
    )b on b.crm_account_id=a.crm_account_id
  where a.net_mrr_usd>0 and a.net_mrr_usd_prior_mo_end =0
  and b.mrr_date<a.mrr_date_prior_mo_end and b.mrr_date >= date_sub(a.mrr_date, interval 1 year)
  and a.crm_account_id ='0018000000xxTFjAAM'
  group by a.mrr_date, a.crm_account_id
) pci_mtd
  ON pci_mtd.mrr_date=pci_mom.mrr_date and pci_mtd.crm_account_id=pci_mom.crm_account_id
  #DoD_returncustomers
LEFT JOIN
      (
      select a.mrr_date, a.crm_account_id, case when sum(b.net_mrr_usd)>0 then 'Y' else 'N' end as paid_crm_ind_dod
      from
        (
        select mrr_date, mrr_date_prior_day, crm_account_id, sum(net_mrr_usd) net_mrr_usd, sum(net_mrr_usd_prior_day) net_mrr_usd_prior_day
        from edw_analyst_general.pop_mrr_granular_stage
        group by mrr_date, mrr_date_prior_day, crm_account_id
        )a
      join
        (
        select mrr_date, crm_account_id, sum(net_mrr_usd) as net_mrr_usd from edw_analyst_general.pop_mrr_granular_stage
        group by mrr_date, crm_account_id
        )b on b.crm_account_id=a.crm_account_id
      where a.net_mrr_usd>0 and a.net_mrr_usd_prior_day =0
      and b.mrr_date<a.mrr_date_prior_day and b.mrr_date >= date_sub(a.mrr_date, interval 1 year)
      and a.crm_account_id ='0018000000xxTFjAAM'
      group by a.mrr_date, a.crm_account_id
    ) pci_dod
  ON pci_dod.mrr_date=pci_mtd.mrr_date and pci_dod.crm_account_id=pci_mtd.crm_account_id
ORDER BY 1