Instantly share code, notes, and snippets.

Embed
What would you like to do?
23232e4dddddd
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment