-- 1
--Представим ситуацию: к нам обратились маркетологи с просьбой сравнить две рекламные кампании.
--В рекламной кампании № 1 о нашем приложении рассказал известный блогер на Youtube-канале о кулинарии.
--На эту интеграцию суммарно потратили 250 тысяч рублей.
--В результате этой кампании 1 сентября в приложении зарегистрировался 171 человек.
--В рамках рекламной кампании № 2 пользователям показывали таргетированную рекламу в социальных сетях.
--На это тоже суммарно потратили 250 тысяч рублей, и в результате 1 сентября у нас появилось 236 новых пользователей.
--На основе таблицы user_actions рассчитайте метрику CAC для двух рекламных кампаний.
--Список id пользователей, пришедших после рекламной кампании № 1: 8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135
--Список id пользователей, пришедших после рекламной кампании № 2:8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131
--Колонку с наименованиями кампаний назовите ads_campaign, а колонку со значением метрики — cac.
--Наименования кампаний выведите в следующем виде: Кампания № 1б Кампания № 2
--Полученные значения метрики округлите до двух знаков после запятой. Результат должен быть отсортирован по убыванию значения метрики.
--Покупателями будем считать тех пользователей, которые сделали хотя бы один заказ, который в дальнейшем не был отменён. Например, если человек сделал только один заказ, а потом отменил его, то покупателем мы его не считаем.
with
september_new_users AS (
SELECT user_id,
CASE
WHEN user_id IN (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741,
8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845,
8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972,
8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071,
9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317,
9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472,
9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550,
9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654,
9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762,
9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871,
9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998,
9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103,
10105, 10122, 10134, 10135) THEN 'Кампания № 1'
WHEN user_id IN (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681,
8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754,
8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851,
8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000,
9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117,
9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203,
9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289,
9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383,
9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497,
9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567,
9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692,
9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863,
9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930,
9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058,
10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131) THEN 'Кампания № 2'
ELSE NULL
END AS ads_campaign
FROM (SELECT DATE(MIN(time)) AS date, user_id
FROM user_actions
GROUP BY user_id) t
WHERE DATE_PART('month', date)=9 AND DATE_PART('year', date)=2022
),
september_orders AS(
SELECT DATE(time), user_id, order_id
FROM user_actions
WHERE order_id NOT IN (SELECT order_id FROM user_actions WHERE action = 'cancel_order')
AND DATE_PART('month', time)=9 AND DATE_PART('year', time)=2022
)
SELECT ads_campaign, ROUND (250000/COUNT (DISTINCT user_id)::DECIMAL,2) AS cac
FROM (SELECT user_id, ads_campaign, COUNT (order_id) as september_new_users_orders
FROM september_new_users LEFT JOIN september_orders USING (user_id)
WHERE ads_campaign IS NOT NULL
GROUP BY user_id, ads_campaign
) t
WHERE september_new_users_orders >0
GROUP BY ads_campaign
ORDER BY cac desc;
--Вариант верного решения:
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
round(250000.0 / count(distinct user_id), 2) as cac
FROM (SELECT user_id,
order_id,
action,
case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
10113, 10131) then 2
else 0 end as ads_campaign,
count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
FROM user_actions) t1
WHERE ads_campaign in (1, 2)
and is_canceled = 0
GROUP BY ads_campaign
ORDER BY cac desc;
-- 2
--Рассчитайте ROI для каждого рекламного канала.
--Колонку с наименованиями кампаний назовите ads_campaign, а колонку со значением метрики — roi.
--Наименования кампаний выведите в следующем виде:
--Кампания № 1
--Кампания № 2
--Полученные значения метрики необходимо выразить в процентах и округлить до двух знаков после запятой.
--Результат должен быть отсортирован по убыванию значения метрики.
--Покупателями будем считать тех пользователей, которые сделали хотя бы один заказ, который в дальнейшем не был отменён. Например, если человек сделал только один заказ, а потом отменил его, то покупателем мы его не считаем.
--Не забывайте при делении заранее приводить значения к нужному типу данных.
--Списки зарегистрировавшихся пользователей те же, что и на предыдущем шаге.
with
main_t AS(
SELECT user_id, order_id, action,
case
when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135)
then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131)
then 2
else 0 end as ads_campaign,
count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
FROM user_actions
),
order_price AS (
SELECT order_id, SUM(price) AS order_price
FROM (SELECT order_id, unnest(product_ids) AS product_id
FROM orders) t1
LEFT JOIN products USING (product_id)
GROUP BY order_id
)
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
round((SUM(order_price)-250000)/250000*100, 2) as roi
FROM main_t LEFT JOIN order_price USING (order_id)
WHERE ads_campaign in (1, 2)
and is_canceled = 0
GROUP BY ads_campaign
ORDER BY roi desc;
Вариант верного решения:
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
round((sum(price) - 250000.0) / 250000.0 * 100, 2) as roi
FROM (SELECT ads_campaign,
user_id,
order_id,
product_id,
price
FROM (SELECT ads_campaign,
user_id,
order_id
FROM (SELECT user_id,
order_id,
case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
10113, 10131) then 2
else 0 end as ads_campaign,
count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
FROM user_actions) t1
WHERE ads_campaign in (1, 2)
and is_canceled = 0) t2
LEFT JOIN (SELECT order_id,
unnest(product_ids) as product_id
FROM orders) t3 using(order_id)
LEFT JOIN products using(product_id)) t4
GROUP BY ads_campaign
ORDER BY roi desc
-- 3
--Для каждой рекламной кампании посчитайте среднюю стоимость заказа привлечённых пользователей за первую неделю использования приложения с 1 по 7 сентября 2022 года.
--Колонку с наименованиями кампаний назовите ads_campaign, а колонку со значением метрики — avg_check.
--Наименования кампаний выведите в следующем виде:
--Кампания № 1
--Кампания № 2
--Полученные значения метрики необходимо округлить до двух знаков после запятой.
--Результат должен быть отсортирован по убыванию значения метрики.
with
main_t AS(
SELECT user_id, order_id, action, order_price, ads_campaign, is_canceled
FROM
(SELECT DATE(time) AS date, user_id, order_id, action,
case
when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135)
then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131)
then 2
else 0 end as ads_campaign,
count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
FROM user_actions) AS t1
LEFT JOIN
(SELECT order_id, SUM(price) AS order_price
FROM (SELECT order_id, unnest(product_ids) AS product_id
FROM orders) t
LEFT JOIN products USING (product_id)
GROUP BY order_id) AS t2
USING (order_id)
WHERE date BETWEEN '2022-09-01' AND '2022-09-07 23:59:59'
)
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
ROUND (AVG (avg_order_price),2) AS avg_check
FROM (
SELECT user_id, ads_campaign, is_canceled, AVG (order_price) AS avg_order_price
FROM main_t
GROUP BY user_id, ads_campaign, is_canceled) t
WHERE ads_campaign in (1, 2)
and is_canceled = 0
GROUP BY ads_campaign
ORDER BY avg_check desc;
Вариант верного решения:
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
round(avg(user_avg_check), 2) as avg_check
FROM (SELECT ads_campaign,
user_id,
round(avg(order_price), 2) as user_avg_check
FROM (SELECT ads_campaign,
user_id,
order_id,
sum(price) as order_price
FROM (SELECT ads_campaign,
user_id,
order_id,
product_id,
price
FROM (SELECT ads_campaign,
user_id,
order_id
FROM (SELECT user_id,
order_id,
time,
case when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732,
8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791,
8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867,
8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940,
8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020,
9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089,
9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175,
9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278,
9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398,
9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476,
9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528,
9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605,
9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660,
9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739,
9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818,
9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896,
9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993,
9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051,
10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135) then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670,
8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704,
8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771,
8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825,
8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882,
8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929,
8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971,
8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042,
9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109,
9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179,
9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227,
9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281,
9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333,
9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404,
9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485,
9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530,
9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570,
9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658,
9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719,
9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757,
9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841,
9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882,
9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929,
9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033,
10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069,
10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110,
10113, 10131) then 2
else 0 end as ads_campaign,
count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
FROM user_actions) t1
WHERE ads_campaign in (1, 2)
and is_canceled = 0
and time::date >= '2022-09-01'
and time::date < '2022-09-08') t2
LEFT JOIN (SELECT order_id,
unnest(product_ids) as product_id
FROM orders) t3 using(order_id)
LEFT JOIN products using(product_id)) t4
GROUP BY ads_campaign, user_id, order_id) t5
GROUP BY ads_campaign, user_id) t6
GROUP BY ads_campaign
ORDER BY avg_check desc
-- 4
--На основе данных в таблице user_actions рассчитайте показатель дневного Retention для всех пользователей,
--разбив их на когорты по дате первого взаимодействия с нашим приложением.
--В результат включите четыре колонки:
--месяц первого взаимодействия,
--дату первого взаимодействия,
--количество дней, прошедших с даты первого взаимодействия (порядковый номер дня начиная с 0)
--значение Retention.
--Колонки со значениями назовите соответственно start_month, start_date, day_number, retention.
--Метрику необходимо выразить в виде доли, округлив полученные значения до двух знаков после запятой.
--Месяц первого взаимодействия укажите в виде даты, округлённой до первого числа месяца.
--Результат должен быть отсортирован сначала по возрастанию даты первого взаимодействия, затем по возрастанию порядкового номера дня.
--В этой задаче учитывайте всех уникальных пользователей в таблице user_actions.
SELECT DATE_TRUNC('month', start_date)::date AS start_month,
start_date,
date - start_date AS day_number,
ROUND (COUNT(DISTINCT user_id)::decimal/ MAX(COUNT(DISTINCT user_id)) OVER (PARTITION BY start_date), 2) AS retention
FROM (
SELECT user_id,
MIN(time::date) OVER (PARTITION BY user_id) AS start_date,
time::date AS date
FROM user_actions
) t1
GROUP BY date, start_date
ORDER BY start_date, day_number;
Вариант верного решения:
SELECT date_trunc('month', start_date)::date as start_month,
start_date,
date - start_date as day_number,
round(users::decimal / max(users) OVER (PARTITION BY start_date), 2) as retention
FROM (SELECT start_date,
time::date as date,
count(distinct user_id) as users
FROM (SELECT user_id,
time::date,
min(time::date) OVER (PARTITION BY user_id) as start_date
FROM user_actions) t1
GROUP BY start_date, time::date) t2
-- 5
--Для каждой рекламной кампании посчитайте Retention 1-го и 7-го дня у привлечённых пользователей.
--В результат включите четыре колонки: колонку с наименованиями кампаний, дату первого взаимодействия с приложением,
--количество дней, прошедших с даты первого взаимодействия (порядковый номер), и само значение Retention.
--Колонки со значениями назовите соответственно ads_campaign, start_date, day_number, retention.
--Наименования кампаний выведите в следующем виде:
--Кампания № 1
--Кампания № 2
--Метрику необходимо выразить в виде доли, округлив полученные значения до двух знаков после запятой.
--Результат должен быть отсортирован сначала по наименованию кампании (по возрастанию), затем по возрастанию порядкового номера дня.
--В результат должны попасть следующие дни: нулевой, первый и седьмой.
--Списки зарегистрировавшихся пользователей те же, что и на предыдущих шагах.
SELECT ads_campaign, start_date, day_number,
ROUND(COUNT (DISTINCT user_id)::DECIMAL/ MAX(COUNT (DISTINCT user_id)) OVER (PARTITION BY ads_campaign), 2) AS retention
FROM (
SELECT user_id, ads_campaign,
MIN(date) OVER (PARTITION BY user_id) AS start_date,
date,
date - MIN(date) OVER (PARTITION BY user_id) AS day_number
FROM
(SELECT DATE(time) AS date, user_id,
case
when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135)
then 'Кампания № 1'
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131)
then 'Кампания № 2'
else null
end as ads_campaign
FROM user_actions) t1
WHERE ads_campaign IS NOT NULL
) t2
WHERE day_number IN (0,1,7)
GROUP BY ads_campaign, start_date, day_number;
--Вариант верного решения
with
main_t AS(
SELECT user_id, concat('Кампания № ', ads_campaign) as ads_campaign, start_date, date, date - start_date AS day_number
FROM (SELECT user_id,
MIN(time::date) OVER (PARTITION BY user_id) AS start_date,
time::date AS date
FROM user_actions) t1
LEFT JOIN
(SELECT user_id, order_id, action,
case
when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972, 8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317, 9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654, 9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998, 9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135)
then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754, 8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000, 9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289, 9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567, 9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863, 9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131)
then 2
else 0 end as ads_campaign
FROM user_actions) t2
USING (user_id)
WHERE ads_campaign in (1, 2) AND start_date = '2022-09-01'
)
SELECT ads_campaign, start_date, day_number,
ROUND (COUNT(DISTINCT user_id)::decimal/ MAX(COUNT(DISTINCT user_id)) OVER (PARTITION BY ads_campaign, start_date), 2) AS retention
FROM main_t
WHERE day_number IN (0, 1, 7)
GROUP BY ads_campaign, start_date, date, day_number
ORDER BY ads_campaign, day_number;
-- 6
--Для каждой рекламной кампании для каждого дня посчитайте две метрики:
--1. Накопительный ARPPU.
--2. Затраты на привлечение одного покупателя (CAC).
--Колонку с наименованиями кампаний назовите ads_campaign, колонку с днями — day, а колонки со значениями метрик — cumulative_arppu и cac.
--Значения метрики CAC укажите одинаковым для всех дней (это необходимо для наглядной визуализации).
--Наименования кампаний выведите в следующем виде:
--Кампания № 1
--Кампания № 2
--Дни пронумеруйте начиная с 0 и отобразите в следующем формате: Day 0, Day 1, Day 2 и т.д.
--Полученные значения метрик необходимо округлить до двух знаков после запятой.
--Результат должен быть отсортирован сначала по наименованию кампании (по возрастанию), затем по наименованию дня (также по возрастанию).
--Покупателями будем считать тех пользователей, которые сделали хотя бы один заказ, который в дальнейшем не был отменён.
--Списки зарегистрировавшихся пользователей те же, что и на предыдущих шагах.
--!! Решение, подкрученное под правильное!!
with
main_t AS (
SELECT ads_campaign, day,
COUNT (DISTINCT user_id) AS users,
SUM (order_price) FILTER (WHERE ads_campaign in (1, 2) AND is_canceled = 0 ) as revenue
FROM (
SELECT user_id, start_date, date_order,
date_order - start_date AS day, order_id, order_price, ads_campaign, is_canceled
FROM
(SELECT user_id, DATE(time) as date_order, order_id, action,
case
when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972,
8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317,
9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654,
9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998,
9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135)
then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754,
8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000,
9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289,
9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567,
9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863,
9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131)
then 2
else 0 end as ads_campaign,
count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
FROM user_actions) t1
LEFT JOIN
(SELECT user_id, MIN(time)::DATE AS start_date
FROM user_actions
GROUP BY user_id) t2 USING (user_id)
LEFT JOIN
(SELECT order_id, SUM(price) AS order_price
FROM (SELECT order_id, unnest (product_ids) AS product_id
FROM orders) t3
LEFT JOIN products USING (product_id)
GROUP BY order_id) t3 USING (order_id)
) t4
WHERE ads_campaign in (1, 2)
GROUP BY ads_campaign, day
)
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
concat('Day ', day) as day,
CASE
WHEN ads_campaign = 1 THEN ROUND (SUM (revenue) OVER (PARTITION BY ads_campaign ORDER BY day)/171::DECIMAL, 2)
ELSE ROUND (SUM (revenue) OVER (PARTITION BY ads_campaign ORDER BY day)/234::DECIMAL, 2)
END AS cumulative_arppu,
CASE
WHEN ads_campaign = 1 THEN ROUND (250000/171::DECIMAL, 2)
ELSE ROUND (250000/234::DECIMAL, 2)
END AS cac
FROM main_t
--Вариант верного решения
with
main_table as (
SELECT ads_campaign, user_id, order_id, time, product_id, price
FROM (SELECT ads_campaign, user_id, order_id, time
FROM (SELECT user_id, order_id, time,
case
when user_id in (8631, 8632, 8638, 8643, 8657, 8673, 8706, 8707, 8715, 8723, 8732, 8739, 8741, 8750, 8751, 8752, 8770, 8774, 8788, 8791, 8804, 8810, 8815, 8828, 8830, 8845, 8853, 8859, 8867, 8869, 8876, 8879, 8883, 8896, 8909, 8911, 8933, 8940, 8972,
8976, 8988, 8990, 9002, 9004, 9009, 9019, 9020, 9035, 9036, 9061, 9069, 9071, 9075, 9081, 9085, 9089, 9108, 9113, 9144, 9145, 9146, 9162, 9165, 9167, 9175, 9180, 9182, 9197, 9198, 9210, 9223, 9251, 9257, 9278, 9287, 9291, 9313, 9317,
9321, 9334, 9351, 9391, 9398, 9414, 9420, 9422, 9431, 9450, 9451, 9454, 9472, 9476, 9478, 9491, 9494, 9505, 9512, 9518, 9524, 9526, 9528, 9531, 9535, 9550, 9559, 9561, 9562, 9599, 9603, 9605, 9611, 9612, 9615, 9625, 9633, 9652, 9654,
9655, 9660, 9662, 9667, 9677, 9679, 9689, 9695, 9720, 9726, 9739, 9740, 9762, 9778, 9786, 9794, 9804, 9810, 9813, 9818, 9828, 9831, 9836, 9838, 9845, 9871, 9887, 9891, 9896, 9897, 9916, 9945, 9960, 9963, 9965, 9968, 9971, 9993, 9998,
9999, 10001, 10013, 10016, 10023, 10030, 10051, 10057, 10064, 10082, 10103, 10105, 10122, 10134, 10135)
then 1
when user_id in (8629, 8630, 8644, 8646, 8650, 8655, 8659, 8660, 8663, 8665, 8670, 8675, 8680, 8681, 8682, 8683, 8694, 8697, 8700, 8704, 8712, 8713, 8719, 8729, 8733, 8742, 8748, 8754,
8771, 8794, 8795, 8798, 8803, 8805, 8806, 8812, 8814, 8825, 8827, 8838, 8849, 8851, 8854, 8855, 8870, 8878, 8882, 8886, 8890, 8893, 8900, 8902, 8913, 8916, 8923, 8929, 8935, 8942, 8943, 8949, 8953, 8955, 8966, 8968, 8971, 8973, 8980, 8995, 8999, 9000,
9007, 9013, 9041, 9042, 9047, 9064, 9068, 9077, 9082, 9083, 9095, 9103, 9109, 9117, 9123, 9127, 9131, 9137, 9140, 9149, 9161, 9179, 9181, 9183, 9185, 9190, 9196, 9203, 9207, 9226, 9227, 9229, 9230, 9231, 9250, 9255, 9259, 9267, 9273, 9281, 9282, 9289,
9292, 9303, 9310, 9312, 9315, 9327, 9333, 9335, 9337, 9343, 9356, 9368, 9370, 9383, 9392, 9404, 9410, 9421, 9428, 9432, 9437, 9468, 9479, 9483, 9485, 9492, 9495, 9497, 9498, 9500, 9510, 9527, 9529, 9530, 9538, 9539, 9545, 9557, 9558, 9560, 9564, 9567,
9570, 9591, 9596, 9598, 9616, 9631, 9634, 9635, 9636, 9658, 9666, 9672, 9684, 9692, 9700, 9704, 9706, 9711, 9719, 9727, 9735, 9741, 9744, 9749, 9752, 9753, 9755, 9757, 9764, 9783, 9784, 9788, 9790, 9808, 9820, 9839, 9841, 9843, 9853, 9855, 9859, 9863,
9877, 9879, 9880, 9882, 9883, 9885, 9901, 9904, 9908, 9910, 9912, 9920, 9929, 9930, 9935, 9939, 9958, 9959, 9961, 9983, 10027, 10033, 10038, 10045, 10047, 10048, 10058, 10059, 10067, 10069, 10073, 10075, 10078, 10079, 10081, 10092, 10106, 10110, 10113, 10131)
then 2
else 0 end as ads_campaign,
count(action) filter (WHERE action = 'cancel_order') OVER (PARTITION BY order_id) as is_canceled
FROM user_actions) t1
WHERE ads_campaign in (1, 2) and is_canceled = 0) t2
LEFT JOIN (SELECT order_id, unnest(product_ids) as product_id
FROM orders) t3 using(order_id)
LEFT JOIN products using(product_id)
)
SELECT concat('Кампания № ', ads_campaign) as ads_campaign,
concat('Day ', row_number() OVER (PARTITION BY ads_campaign ORDER BY date) - 1) as day, -- ПОЧЕМУ МЫ ВЫЧИТАЕМ 1 ??????
round(sum(revenue) OVER (PARTITION BY ads_campaign ORDER BY date) / paying_users::decimal, 2) as cumulative_arppu,
cac
FROM (SELECT ads_campaign,
time::date as date,
sum(price) as revenue
FROM main_table
GROUP BY ads_campaign, time::date) t1
LEFT JOIN (SELECT ads_campaign,
count(distinct user_id) as paying_users,
round(250000.0 / count(distinct user_id), 2) as cac
FROM main_table
GROUP BY ads_campaign) t2
USING (ads_campaign)
To embed this program on your website, copy the following code and paste it into your website's HTML: