19.9 - потому что с тех пор не обновлялись (
оригинальный запрос, который выдаёт рандомные числа в третьей колонке, при каждом запуске разные
select cnt,
count(distinct ordinary_user) as ordinary_users_cnt,
count(distinct special_user) as special_users_cnt
from (
select amp.user_uid as ordinary_user,
count(*) as cnt
from events.amplitude_event as amp
all inner join events.user_action as action on action.user_uid = amp.user_uid
where dateDiff('hour', action.signup_time, event_time) < 12
group by ordinary_user
) as first_query
all left join (
select user_uid as special_user
from events.user_action
where dateDiff('hour', signup_time, action_time) between 12 and 36
group by special_user
) as second_query on first_query.ordinary_user = second_query.special_user
group by cnt
order by cnt
изврат, что бы получить таки правильные цифры
select cnt,
count(distinct ordinary_user) as ordinary_users_cnt,
count(distinct special_user) as special_users_cnt
from (
select amp.user_uid as ordinary_user,
amp.user_uid as special_user,
count(*) as cnt
from events.amplitude_event as amp
inner join events.user_action as action on action.user_uid = amp.user_uid
where dateDiff('hour', action.signup_time, event_time) < 12
and dateDiff('hour', signup_time, action_time) between 12 and 36
group by user_uid, special_user
union all
select amp.user_uid as ordinary_user,
null as special_user,
count(*) as cnt
from events.amplitude_event as amp
inner join events.user_action as action on action.user_uid = amp.user_uid
where dateDiff('hour', action.signup_time, event_time) < 12
and (
dateDiff('hour', signup_time, action_time) not between 12 and 36
or action_time is null
)
group by user_uid, special_user
)
group by cnt
order by cnt
сори если запросы недостаточно упрощены, но вобщем суть цифр примерно такая:
хочется увидеть общее кол-во пользователей совершивших какое-либо действие в интервале 0-12 часов, с разбивкой по кол-ву событий
и отдельно увидеть сколько из них совершили специальное действие в следующие 24ч.
