def test_get_earliest_timestamp(db, team):
_create_event(team=team,
event="sign up",
distinct_id="1",
timestamp="2020-01-04T14:10:00Z")
_create_event(team=team,
event="sign up",
distinct_id="1",
timestamp="2020-01-06T14:10:00Z")
assert get_earliest_timestamp(team.id) == datetime(2020,
1,
4,
14,
10,
tzinfo=pytz.UTC)
_create_event(team=team,
event="sign up",
distinct_id="1",
timestamp="1984-01-06T14:10:00Z")
_create_event(team=team,
event="sign up",
distinct_id="1",
timestamp="2014-01-01T01:00:00Z")
_create_event(team=team,
event="sign up",
distinct_id="1",
timestamp="2015-01-01T01:00:00Z")
assert get_earliest_timestamp(team.id) == datetime(2015,
1,
1,
1,
tzinfo=pytz.UTC)
def get_query(self) -> str:
step_counts = self.get_step_counts_without_aggregation_query()
# Expects multiple rows for same person, first event time, steps taken.
self.params.update(self.funnel_order.params)
reached_from_step_count_condition, reached_to_step_count_condition, _ = self.get_steps_reached_conditions(
)
trunc_func = get_trunc_func_ch(self._filter.interval)
interval_func = get_interval_func_ch(self._filter.interval)
if self._filter.date_from is None:
_date_from = get_earliest_timestamp(self._team.pk)
else:
_date_from = self._filter.date_from
breakdown_clause = self._get_breakdown_prop()
formatted_date_from = format_ch_timestamp(_date_from, self._filter)
formatted_date_to = format_ch_timestamp(self._filter.date_to,
self._filter)
self.params.update({
"formatted_date_from": formatted_date_from,
"formatted_date_to": formatted_date_to,
"interval": self._filter.interval,
})
query = f"""
SELECT
entrance_period_start,
reached_from_step_count,
reached_to_step_count,
if(reached_from_step_count > 0, round(reached_to_step_count / reached_from_step_count * 100, 2), 0) AS conversion_rate
{breakdown_clause}
FROM (
SELECT
entrance_period_start,
countIf({reached_from_step_count_condition}) AS reached_from_step_count,
countIf({reached_to_step_count_condition}) AS reached_to_step_count
{breakdown_clause}
FROM (
{step_counts}
) GROUP BY entrance_period_start {breakdown_clause}
) data
RIGHT OUTER JOIN (
SELECT
{trunc_func}(toDateTime(%(formatted_date_from)s) + {interval_func}(number)) AS entrance_period_start
{', breakdown_value as prop' if breakdown_clause else ''}
FROM numbers(dateDiff(%(interval)s, toDateTime(%(formatted_date_from)s), toDateTime(%(formatted_date_to)s)) + 1) AS period_offsets
{'ARRAY JOIN (%(breakdown_values)s) AS breakdown_value' if breakdown_clause else ''}
) fill
USING (entrance_period_start {breakdown_clause})
ORDER BY entrance_period_start ASC
SETTINGS allow_experimental_window_functions = 1"""
return query
def get_active_user_params(filter: Union[Filter, PathFilter], entity: Entity, team_id: int) -> Dict[str, Any]:
params = {}
params.update({"prev_interval": "7 DAY" if entity.math == WEEKLY_ACTIVE else "30 day"})
diff = timedelta(days=7) if entity.math == WEEKLY_ACTIVE else timedelta(days=30)
if filter.date_from:
params.update(
{
"parsed_date_from_prev_range": f"AND timestamp >= '{format_ch_timestamp(filter.date_from - diff, filter)}'"
}
)
else:
try:
earliest_date = get_earliest_timestamp(team_id)
except IndexError:
raise ValidationError("Active User queries require a lower date bound")
else:
params.update(
{
"parsed_date_from_prev_range": f"AND timestamp >= '{format_ch_timestamp(earliest_date - diff, filter)}'"
}
)
return params
def test_get_earliest_timestamp_with_no_events(db, team):
assert get_earliest_timestamp(team.id) == datetime(2021,
1,
14,
tzinfo=pytz.UTC)
def track_earliest_timestamp(self):
get_earliest_timestamp(2)
def earliest_timestamp_func(team_id: int):
from posthog.queries.util import get_earliest_timestamp
return get_earliest_timestamp(team_id)