Построить воронку продаж по странам вида: “country”, “installs”, “trials”,
“purchases”, “conversion_rate_to_trial”, “conversion_rate_to_purchase”

Ограничения: на 1 пользователя может быть только 1 оформление триала и 1 оформление purchase
Покупка возможна только по истечению платного периода.

Таблица:

DROP TABLE IF EXISTS events;
CREATE TABLE events
(
transaction_id SERIAL PRIMARY KEY,
datetime timestamp,
event_type VARCHAR(255) NOT NULL,
user_id INT NOT NULL,
country VARCHAR(255) NOT NULL
);
INSERT INTO events (transaction_id, datetime, event_type, user_id, country)
VALUES
(345642,'2021-01-03', 'installs', 3253, 'Russia'),
(353787,'2021-01-03', 'trial', 3253, 'Russia'),
(363568,'2021-01-10', 'purchase', 3253, 'Russia'),
(436561,'2021-01-05', 'installs', 3333, 'Russia'),
(252512,'2021-01-06', 'trial', 3333, 'Russia'),
(325368,'2021-01-13', 'purchase', 3333, 'Russia'),
(417809,'2021-01-03', 'installs', 3266, 'Brazil'),
(436556,'2021-01-14', 'installs', 3266, 'Brazil'),
(436578,'2021-01-16', 'trial', 3266, 'Brazil'),
(436800,'2021-01-23', 'purchase', 3266, 'Brazil')


Здесь рассчитал общее количество событий по типам:
SELECT country,
sum(case when event_type='trial' then 1 else 0 end) as trial,
sum(case when event_type='installs' then 1 else 0 end) as installs,
sum(case when event_type='purchase' then 1 else 0 end) as purchase
FROM events
GROUP BY country;


Но нужно еще учесть, то что загрузок может быть несколько. Т.е установил один раз, удалил, установил второй раз. Только после этого второй установки взял пробный период и только после него возможна покупка.
Пока не пойму как это реализовать.

with installs as (
select
distinct e.user_id
from events e
where e.event_type = 'installs'
),
trials as (
select
distinct e.user_id
from installs s
inner join events e on e.user_id = s.user_id
where e.event_type = 'trial'
),
purchases as (
select
distinct e.user_id
from trials a
inner join events e on e.user_id = a.user_id
where e.event_type = 'purchase'
)

select 'installs' as step, COUNT(*) from installs
union
select 'trials' as step, COUNT(*) from trials
union
select 'purchase' as step, COUNT(*) from purchases
order by count desc
;

[quot gggg111#22424621]Построить воронку продаж по странам вида: “country”, “installs”, “trials”,
“purchases”, “conversion_rate_to_trial”, “conversion_rate_to_purchase”

Ограничения: на 1 пользователя может быть только 1 оформление триала и 1 оформление purchase
Покупка возможна только по истечению платного периода.

Таблица:





Здесь рассчитал общее количество событий по типам:




Но нужно еще учесть, то что загрузок может быть несколько. Т.е установил один раз, удалил, установил второй раз. Только после этого второй установки взял пробный период и только после него возможна покупка.
Пока не пойму как это реализовать.