DROP TABLE user_dates
CREATE TABLE user_dates AS (
    WITH filtered_accounts AS (
        SELECT COALESCE(master_account_id, id) AS id_acc, *
        FROM accounts
        WHERE NOT (name ~* '(test)|(qa)' OR client_type IN (4, 5))
    ),
         cookie_last_click AS (
             SELECT
                    last_value(utmcampaign) OVER (partition BY clientid ORDER BY visits.DATE)  cookie_utmcampaign_lc,
                    last_value(utmsource) OVER (partition BY clientid ORDER BY visits.DATE)  cookie_utmsource_lc,
                    last_value(utmmedium) OVER (partition BY clientid ORDER BY visits.DATE)  cookie_utmmedium_lc,
                    last_value(utmcontent) OVER (partition BY clientid ORDER BY visits.DATE)  cookie_utmcontent_lc,
                    visits.watchids,
                    visits.clientid  AS  clientid_
             FROM etl.stg_ym_visits_unnest visits
             WHERE (utmcampaign IS NOT NULL) AND (utmsource IS NOT NULL) AND (utmmedium IS NOT NULL)
         ),

         visits_with_accounts AS (
             SELECT first_value(lasttrafficsource) OVER (partition BY visits.clientid ORDER BY DATE) original_source,
                    first_value(utmcontent) OVER (partition BY visits.clientid ORDER BY DATE)        original_utmcontent,
                    first_value(utmsource) OVER (partition BY visits.clientid ORDER BY DATE)         original_utmsource,
                    first_value(utmcampaign) OVER (partition BY visits.clientid ORDER BY DATE)       original_utmcampaign,
                    first_value(utmmedium) OVER (partition BY visits.clientid ORDER BY DATE)         original_utmmedium,
                    COALESCE(utmcontent, utmcampaign)  AS utm_union_,
                    first_value(created_at) OVER (partition BY a.id_acc ORDER BY created_at)    AS account_creation_date,
                    *

             FROM etl.stg_ym_visits_unnest visits
             LEFT JOIN cookie_last_click cc ON (visits.watchids = cc.watchids) AND (visits.clientid = cc.clientid_)
                      FULL OUTER JOIN filtered_accounts a ON visits.account_id = a.id
         ),

         user_agg AS (
             SELECT MIN(DATE)                     AS user_creation_date,
                   /* first_value(cast(min(created_at) as date)) over (partition by id_acc order by min(created_at))
                        as account_creation_date,*/ /*!после агрегации получаются несколько значений*/
                    MIN(original_source)          AS original_source,
                    CASE
                    WHEN MIN(original_utmcampaign)='courses'
                        THEN MIN(original_utmcontent)
                        ELSE MIN(original_utmcampaign)
                        END AS utmcampaign_union,
                    MIN(original_utmcontent)      AS utmcontent,
                    MIN(original_utmsource)       AS utmsource,
                    MIN(original_utmmedium)       AS utmmedium,
                    MIN(master_account_id)        AS master_account_id,
                    MIN(utm_union_)               AS utm_union,
                    CASE
                    WHEN MIN(cookie_utmcampaign_lc)='courses'
                        THEN MIN(cookie_utmcontent_lc)
                        ELSE MIN(cookie_utmcampaign_lc)
                        END AS cookie_campaign_lc,
                    MIN(cookie_utmsource_lc)    AS cookie_source_lc,
                    MIN(cookie_utmmedium_lc)    AS cookie_medium_lc,
                    id_acc,
                    account_creation_date,
                    clientid
             FROM visits_with_accounts
             GROUP BY id_acc, clientid, account_creation_date
         ),

         acc_last_click AS (
             SELECT CASE
                        WHEN last_value(va.utmcampaign) OVER (partition BY va.id_acc ORDER BY DATE) = 'courses'
                        THEN last_value(va.utmcontent) OVER (partition BY va.id_acc ORDER BY DATE)
                        ELSE last_value(va.utmcampaign) OVER (partition BY va.id_acc ORDER BY DATE)
                        END AS acc_campaign_lc,
                    last_value(va.utmmedium) OVER (partition BY va.id_acc ORDER BY DATE) AS acc_utmmedium_lc,
                    last_value(va.utmsource) OVER (partition BY va.id_acc ORDER BY DATE) AS acc_utmsource_lc,
                    last_value(va.utmcontent) OVER (partition BY va.id_acc ORDER BY DATE ) AS acc_utmcontent_lc,
                    va.id_acc AS id_ac
             FROM visits_with_accounts va
             WHERE  (va.date<=va.account_creation_date) AND (va.utmcampaign IS NOT NULL) AND (va.utmsource IS NOT NULL)
             AND (va.utmmedium IS NOT NULL)
         ),

         with_fucd AS (
             SELECT MIN(user_creation_date) OVER (partition BY ug.id_acc)      AS first_user_creation_date,
                    first_value(original_source) OVER (partition BY ug.id_acc ORDER BY user_creation_date) AS account_original_source,
                    first_value(utmcampaign_union) OVER (partition BY ug.id_acc ORDER BY user_creation_date) AS account_utmcampaign_source,
                    /*first_value(ads.cpc_avg) over (partition by ug.id_acc order by user_creation_date) as account_cpc_avg,*/
                    first_value(utmsource) OVER (partition BY ug.id_acc ORDER BY user_creation_date) AS account_utmsource_source,
                    first_value(utmmedium) OVER (partition BY ug.id_acc ORDER BY user_creation_date) AS account_utmmedium_source,
                    COUNT(clientid) OVER (partition BY ug.id_acc) AS clientid_count,
                    *
             FROM user_agg ug
             LEFT JOIN acc_last_click acl ON acl.id_ac = ug.id_acc

         ),


         acc_id_orders AS (
             SELECT id_acc, o.*
             FROM filtered_accounts
                      JOIN orders o ON (o.account_id = filtered_accounts.id)
             WHERE o.STATUS = 3
         ),
         id_acc_stats AS (
             SELECT id_acc,
                    COUNT(DISTINCT id) order_id_count,
                    MIN(id)            first_order_id,
                    SUM(amount)        order_amount_sum
             FROM acc_id_orders
             GROUP BY id_acc
         ),
         id_acc_counts AS (
             SELECT id_acc,
                    account_id_count
             FROM (
                      SELECT master_account_id,
                             COUNT(DISTINCT id) AS account_id_count
                      FROM filtered_accounts
                      GROUP BY master_account_id
                  ) stats
                      JOIN filtered_accounts
                           ON filtered_accounts.master_account_id = stats.master_account_id
             UNION ALL
             SELECT master_account_id,
                    COUNT(DISTINCT id) AS account_id_count
             FROM filtered_accounts
             GROUP BY master_account_id
             UNION ALL
             SELECT id, 0
             FROM filtered_accounts
             WHERE id NOT IN (
                 SELECT master_account_id
                 FROM accounts
                 WHERE filtered_accounts.master_account_id IS NOT NULL
             )
         ),
         user_dates AS (
             SELECT CASE
                        WHEN MIN(user_creation_date) OVER (partition BY clientid) >
                             MIN(account_creation_date) OVER (partition BY clientid)
                            AND MIN(account_creation_date) OVER (partition BY clientid) >
                                MIN(first_user_creation_date) OVER (partition BY clientid)
                            THEN MIN(first_user_creation_date) OVER (partition BY clientid)
                        WHEN MIN(user_creation_date) OVER (partition BY clientid) >
                             MIN(account_creation_date) OVER (partition BY clientid)
                            AND
                             MIN(first_user_creation_date) OVER (partition BY clientid) >
                             MIN(account_creation_date) OVER (partition BY clientid)
                            THEN MIN(account_creation_date) OVER (partition BY clientid)
                        ELSE MIN(user_creation_date) OVER (partition BY clientid)
                        END AS calculated_user_creation_date,
                    *
             FROM with_fucd
         )
    SELECT CASE
               WHEN u.id_acc IS NOT NULL THEN account_creation_date
               ELSE user_creation_date
               END AS unit_date,
           CASE
               WHEN u.id_acc IS NULL AND o.id IS NULL THEN 1
               WHEN u.id_acc IS NOT NULL AND o.id IS NULL THEN 2
               WHEN u.id_acc IS NOT NULL AND o.id IS NOT NULL THEN 3
               END AS stage,
           CASE
               WHEN s.order_id_count > 1 THEN 2
               WHEN s.order_id_count = 1 THEN 1
               WHEN s.order_id_count = 0 THEN 0
               END AS retention,
           CASE
               WHEN u.clientid_count > 0 THEN s.order_amount_sum/u.clientid_count/s.order_id_count
               ELSE 0
               END AS test_sum,
            CASE
               WHEN u.clientid_count > 0 AND s.order_id_count>0 THEN 1.0/u.clientid_count
               ELSE 0
               END AS test_order_id_sum,
           u.*,
           s.order_id_count,
           s.first_order_id,
           s.order_amount_sum,
           c.account_id_count,
           o.account_id,
           o.act_content_type,
           o.act_file_name,
           o.act_file_size,
           o.act_number,
           o.act_updated_at,
           o.additional_memory,
           o.amount,
           o.created_at,
           o.description,
           o.disposable,
           o.doc_content_type,
           o.doc_file_name,
           o.doc_file_size,
           o.doc_updated_at,
           o.expiration_time,
           o.id,
           o.invoice_content_type,
           o.invoice_file_name,
           o.invoice_file_size,
           o.invoice_updated_at,
           o.mandatory,
           o.mode,
           o.month_count,
           o.name,
           o.params,
           o.payment_till,
           o.payment_time,
           o.payment_uid,
           o.starting_time,
           o.STATUS,
           o.subscription_id,
           o.updated_at,
           o.user_id
    FROM user_dates u
             LEFT JOIN acc_id_orders o ON (u.id_acc = o.id_acc)
             LEFT JOIN id_acc_stats s ON (u.id_acc = s.id_acc)
             LEFT JOIN id_acc_counts c ON (u.id_acc = c.id_acc)
);