Untitled

with customers AS
        (select *
         from mart.f_sales
         join mart.d_calendar on f_sales.date_id = d_calendar.date_id
         where week_of_year = 4),
     new_customers AS
        (select customer_id
         from mart.f_sales
         join mart.d_calendar on f_sales.date_id = d_calendar.date_id
         where week_of_year = 4
           AND status = 'shipped'
         GROUP BY customer_id
         HAVING count(customer_id) = 1),
     returning_customers AS
        (select customer_id
         from mart.f_sales
         join mart.d_calendar on f_sales.date_id = d_calendar.date_id
         where week_of_year = 4
           AND status = 'shipped'
         GROUP BY customer_id
         HAVING count(customer_id) > 1),
     refunded_customers AS
        (select customer_id
         from mart.f_sales
         join mart.d_calendar on f_sales.date_id = d_calendar.date_id
         where week_of_year = 4
           AND payment_amount < 0
         GROUP BY customer_id)
select COALESCE(new_customers.customers, 0) as new_customers_count,
       --COALESCE(returning_customers.customers, 0) as returning_customers_count,
       --COALESCE(refunded_customers.customers, 0) as refunded_customer_count,
       'weekly' AS period_name
       --period_id.period_id
FROM (SELECT count(customer_id) AS customers
      FROM new_customers) new_customers