23232e4dddddd

gistfile1.txt

Select case when u.marketing_source_id = 8382 then 'Яндекс Директ - курсы_main'
when u.marketing_source_id = 8381 then 'Google Adwords - курсы_main'
end Маркетинговый_Источник,
count(u.id) as Платящие_пользователи, 
CASE 
	WHEN ((u.marketing_source_id IN (8381, 8382))
                    AND (c.marketing_source_id NOT IN (
                    SELECT ms.id AS id_ms
                    FROM marketing_sources ms
                    WHERE (ms.Source_Group_ID >= 1 AND ms.Source_Group_ID <= 53)
                )))
            THEN sum(pt.amount) -- Если источник регистрации  – 8381, 8382, источник оплаты – не маркетинг, то выручка контексту.
    	

        WHEN ((u.marketing_source_id NOT IN (
                    SELECT ms.id AS id_ms
                    FROM marketing_sources ms
                    WHERE (ms.Source_Group_ID >= 1 AND ms.Source_Group_ID <= 53)
                ))
                    AND c.marketing_source_id IN (8381, 8382))
            THEN sum(pt.amount) -- Если источник регистрации – не маркетинг, источник оплаты – 8381, 8382, то выручка контексту


        WHEN ((u.marketing_source_id IN (8381, 8382))
                    AND (c.marketing_source_id IN (
                    SELECT ms.id AS id_ms
                    FROM marketing_sources ms
                    WHERE (ms.Source_Group_ID >= 1 AND ms.Source_Group_ID <= 53)
                )))
            THEN sum(pt.amount)/2 -- Источник регистрации –8381, 8382, источник оплаты – маркетинг: выручка/2
        

        WHEN ((u.marketing_source_id IN (
                    SELECT ms.id AS id_ms
                    FROM marketing_sources ms
                    WHERE (ms.Source_Group_ID >= 1 AND ms.Source_Group_ID <= 53)
                ))
                    AND c.marketing_source_id IN (8381, 8382))
            THEN sum(pt.amount) -- Источник регистрации маркетинг, источник оплаты – 8381, 8382: выручка/2
	    ELSE sum(pt.amount)/2
    END AS VR,
    --ss.sum_avg/ss.sd as Средний_Чек,
    
u.marketing_source_id as Источник_Регистрации, ms.name as Источник_оплаты, sdu.referer as reg_ref, sdc.referer as cart_ref
From 
users u
JOIN carts c on u.id = c.user_id
JOIN payment_transactions pt on  c.id = pt.cart_id
JOIN cart_items ci on c.id = ci.cart_id
JOIN marketing_sources ms on ms.id = c.marketing_source_id
LEFT JOIN source_data sdu ON sdu.sourceable_id = u.id AND sdu.sourceable_type = 'User'
JOIN source_data sdc ON sdc.sourceable_id = c.id AND sdc.sourceable_type = 'Cart'
LEFT JOIN lead_requests lr on sdc.sourceable_id = lr.id and sdc.sourceable_type = 'LeadRequest'
LEFT JOIN lead_requests lr2 on sdu.sourceable_id = lr2.id and sdu.sourceable_type = 'LeadRequest'
JOIN products p ON p.id = ci.resource_id 
LEFT JOIN product_tags ptg ON ptg.id = p.product_tag_id
JOIN marketing_source_groups msg_u ON ms.source_group_id = msg_u.id
Where (u.marketing_source_id IN (8381,8382) OR c.marketing_source_id IN (8381,8382))
	AND DATE(c.updated_at) BETWEEN {{startDate}} AND {{endDate}} 
	AND DATE(u.created_at) BETWEEN {{start_reg_Date}} AND {{end_reg_Date}}
	AND c.state = 'successful'
	AND (ci.type IN ('CartItems::EntireCourse', 'CartItems::MonthAccess', 'CartItems::StandardAccess','CartItems::Product')
	OR ptg.id = 4)
	AND
		DATE(u.created_at)
			BETWEEN
				(
					select case
						when {{u_type}} in ('Старые','Все','С','c','Прошлогодние','П','п') then date('2012-01-01')
						when {{u_type}} in ('Новые','Н','н') then date('2018-01-01')
					end
				)
			AND
				(
					select case
						when {{u_type}} in ('Новые','Все','Н','н') then date('2030-01-01')
						when {{u_type}} in ('Старые','С','c','Прошлогодние','П','п') then date('2018-01-01')
					end
				)
	[[AND u.marketing_source_id = {{reg_source}}]]
    [[AND c.marketing_source_id = {{cart_source}}]]
	[[AND sdu.referer = {{reg_ref}}]]
	[[AND sdc.referer = {{cart_ref}}]]
	[[AND lr.referer = {{reg_ref}}]]
	[[AND lr2.referer = {{cart_ref}}]]
	[[AND ci.type = {{product_type}}]]
Group by u.marketing_source_id, ms.id, c.marketing_source_id, sdu.referer, sdc.referer