/*
 * Days until first order
 *
 * Data Source: incuda BI Access Layer
 * View: users
 * 2020-12-18 10:38:48
 */

SELECT DISTINCT
   "dim_channel_type"."channel_type_name" AS C1,
   "dim_client"."client_name" AS C2,
   "dim_first contact date 2"."week_date" AS C3,
   'CU-10',
   CASE
      WHEN "dim_first order date"."day_date" IS NULL THEN 100
      ELSE "dim_first order date"."day_date" - "dim_first contact date 2"."day_date"
   END,
   COUNT(DISTINCT("user_rfm_flat"."master_user_id")),
   COUNT(DISTINCT("user_rfm_flat"."master_user_id")),
   COUNT(DISTINCT("user_rfm_flat"."master_user_id"))
FROM "user_rfm_flat" AS "user_rfm_flat"
INNER JOIN "users" AS "users"
ON (
   "user_rfm_flat"."master_user_id" = "users"."master_user_id"
)
INNER JOIN "dim_client" AS "dim_client"
ON (
   "user_rfm_flat"."client_id" = "dim_client"."client_id"
)
INNER JOIN "dim_date" AS "reference date"
ON (
   "user_rfm_flat"."refdate" = "reference date"."date_id"
)
INNER JOIN "dim_date" AS "dim_first contact date 2"
ON (
   "users"."first_contact_date_id" = "dim_first contact date 2"."date_id"
)
INNER JOIN "dim_channel" AS "dim_channel"
ON (
   "user_rfm_flat"."cohort_channel_id" = "dim_channel"."channel_id"
)
INNER JOIN "dim_channel_type" AS "dim_channel_type"
ON (
   "dim_channel"."channeltype_id" = "dim_channel_type"."channel_type_id"
)
LEFT OUTER JOIN "dim_date" AS "dim_first order date"
ON (
   "users"."first_order_date_id" = "dim_first order date"."date_id"
)
WHERE (
   "reference date"."month_date" = '2020-12-01'
   AND (CASE
      WHEN "dim_first order date"."day_date" IS NULL THEN 100
      ELSE "dim_first order date"."day_date" - "dim_first contact date 2"."day_date"
   END) <= 100
   AND "dim_first contact date 2"."week_date" >= '2012-11-01'
)
GROUP BY 
   CASE
      WHEN "dim_first order date"."day_date" IS NULL THEN 100
      ELSE "dim_first order date"."day_date" - "dim_first contact date 2"."day_date"
   END,
   "dim_channel_type"."channel_type_name",
   "dim_client"."client_name",
   "dim_first contact date 2"."week_date"