def _execute_sql(
self,
filter: RetentionFilter,
team: Team,
) -> Dict[Tuple[int, int], Dict[str, Any]]:
period = filter.period
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties,
team.pk,
filter_test_accounts=filter.filter_test_accounts)
target_entity = filter.target_entity
returning_entity = filter.returning_entity
is_first_time_retention = filter.retention_type == RETENTION_FIRST_TIME
date_from = filter.date_from
date_to = filter.date_to
target_query = ""
target_params: Dict = {}
trunc_func = get_trunc_func_ch(period)
target_query, target_params = self._get_condition(target_entity,
table="e")
returning_query, returning_params = self._get_condition(
returning_entity, table="e", prepend="returning")
target_query_formatted = "AND {target_query}".format(
target_query=target_query)
returning_query_formatted = "AND {returning_query}".format(
returning_query=returning_query)
reference_event_sql = (REFERENCE_EVENT_UNIQUE_SQL
if is_first_time_retention else
REFERENCE_EVENT_SQL).format(
target_query=target_query_formatted,
filters=prop_filters,
trunc_func=trunc_func,
)
target_condition, _ = self._get_condition(target_entity,
table="reference_event")
if is_first_time_retention:
target_condition = target_condition.replace(
"reference_event.uuid", "reference_event.min_uuid")
target_condition = target_condition.replace(
"reference_event.event", "reference_event.min_event")
returning_condition, _ = self._get_condition(returning_entity,
table="event",
prepend="returning")
result = sync_execute(
RETENTION_SQL.format(
target_query=target_query_formatted,
returning_query=returning_query_formatted,
filters=prop_filters,
trunc_func=trunc_func,
extra_union="UNION ALL {} ".format(reference_event_sql),
reference_event_sql=reference_event_sql,
target_condition=target_condition,
returning_condition=returning_condition,
),
{
"team_id":
team.pk,
"start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"end_date":
date_to.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"reference_start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"reference_end_date":
((date_from + filter.period_increment) if filter.display
== TRENDS_LINEAR else date_to).strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
**prop_filter_params,
**target_params,
**returning_params,
"period":
period,
},
)
initial_interval_result = sync_execute(
INITIAL_INTERVAL_SQL.format(
reference_event_sql=reference_event_sql,
trunc_func=trunc_func,
),
{
"team_id":
team.pk,
"start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"end_date":
date_to.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"reference_start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"reference_end_date":
((date_from + filter.period_increment) if filter.display
== TRENDS_LINEAR else date_to).strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
**prop_filter_params,
**target_params,
**returning_params,
"period":
period,
},
)
result_dict = {}
for initial_res in initial_interval_result:
result_dict.update({
(initial_res[0], 0): {
"count": initial_res[1],
"people": []
}
})
for res in result:
result_dict.update({
(res[0], res[1]): {
"count": res[2],
"people": []
}
})
return result_dict
def calculate_retention(self, filter: Filter, team: Team,
total_intervals: int) -> List[Dict[str, Any]]:
period = filter.period or "Day"
tdelta, trunc_func, t1 = self._determineTimedelta(
total_intervals, period)
filter._date_to = ((filter.date_to if filter.date_to else now()) +
t1).isoformat()
if period == "Hour":
date_to = filter.date_to if filter.date_to else now()
date_from = date_to - tdelta
else:
date_to = (filter.date_to if filter.date_to else now()).replace(
hour=0, minute=0, second=0, microsecond=0)
date_from = date_to - tdelta
filter._date_from = date_from.isoformat()
filter._date_to = date_to.isoformat()
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team)
target_query = ""
target_params: Dict = {}
target_entity = (Entity({
"id": "$pageview",
"type": TREND_FILTER_TYPE_EVENTS
}) if not filter.target_entity else filter.target_entity)
if target_entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=target_entity.id)
action_query, target_params = format_action_filter(action,
use_loop=True)
target_query = "AND e.uuid IN ({})".format(action_query)
elif target_entity.type == TREND_FILTER_TYPE_EVENTS:
target_query = "AND e.event = %(target_event)s"
target_params = {"target_event": target_entity.id}
result = sync_execute(
RETENTION_SQL.format(
target_query=target_query,
filters="{filters}".format(
filters=prop_filters) if filter.properties else "",
trunc_func=trunc_func,
),
{
"team_id":
team.pk,
"start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"end_date":
date_to.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
**prop_filter_params,
**target_params,
"period":
period,
},
)
result_dict = {}
for res in result:
result_dict.update({
(res[0], res[1]): {
"count": res[2],
"people": []
}
})
if period == "Week":
date_from = date_from - timedelta(days=date_from.isoweekday() % 7)
parsed = [{
"values": [
result_dict.get((first_day, day), {
"count": 0,
"people": []
}) for day in range(total_intervals - first_day)
],
"label":
"{} {}".format(period, first_day),
"date":
(date_from + self._determineTimedelta(first_day, period)[0]),
} for first_day in range(total_intervals)]
return parsed
def _execute_sql(
self,
filter: Filter,
date_from: datetime.datetime,
date_to: datetime.datetime,
target_entity: Entity,
returning_entity: Entity,
is_first_time_retention: bool,
team: Team,
) -> Dict[Tuple[int, int], Dict[str, Any]]:
period = filter.period
prop_filters, prop_filter_params = parse_prop_clauses(
filter.properties, team.pk)
target_query = ""
target_params: Dict = {}
trunc_func = self._get_trunc_func_ch(period)
if target_entity.type == TREND_FILTER_TYPE_ACTIONS:
action = Action.objects.get(pk=target_entity.id)
action_query, target_params = format_action_filter(action,
use_loop=True)
target_query = "AND e.uuid IN ({})".format(action_query)
elif target_entity.type == TREND_FILTER_TYPE_EVENTS:
target_query = "AND e.event = %(target_event)s"
target_params = {"target_event": target_entity.id}
target_query, target_params = self._get_condition(target_entity)
returning_query, returning_params = self._get_condition(
returning_entity, "returning")
target_query_formatted = (
"AND {target_query}".format(
target_query=target_query) if is_first_time_retention else
"AND ({target_query} OR {returning_query})".format(
target_query=target_query, returning_query=returning_query))
returning_query_formatted = (
"AND {returning_query}".format(
returning_query=returning_query) if is_first_time_retention
else "AND ({target_query} OR {returning_query})".format(
target_query=target_query, returning_query=returning_query))
reference_event_sql = (REFERENCE_EVENT_UNIQUE_SQL
if is_first_time_retention else
REFERENCE_EVENT_SQL).format(
target_query=target_query_formatted,
filters=prop_filters,
trunc_func=trunc_func,
)
result = sync_execute(
RETENTION_SQL.format(
target_query=target_query_formatted,
returning_query=returning_query_formatted,
filters=prop_filters,
trunc_func=trunc_func,
extra_union="UNION ALL {}".format(reference_event_sql)
if is_first_time_retention else "",
reference_event_sql=reference_event_sql,
),
{
"team_id":
team.pk,
"start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
"end_date":
date_to.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
**prop_filter_params,
**target_params,
**returning_params,
"period":
period,
},
)
result_dict = {}
for res in result:
result_dict.update({
(res[0], res[1]): {
"count": res[2],
"people": []
}
})
return result_dict
def _execute_sql(
self,
filter: RetentionFilter,
team: Team,
) -> Dict[Tuple[int, int], Dict[str, Any]]:
period = filter.period
is_first_time_retention = filter.retention_type == RETENTION_FIRST_TIME
date_from = filter.date_from
trunc_func = get_trunc_func_ch(period)
returning_event_query, returning_event_params = RetentionEventsQuery(
filter=filter,
team_id=team.pk,
event_query_type=RetentionQueryType.RETURNING).get_query()
target_event_query, target_event_params = RetentionEventsQuery(
filter=filter,
team_id=team.pk,
event_query_type=RetentionQueryType.TARGET_FIRST_TIME
if is_first_time_retention else RetentionQueryType.TARGET,
).get_query()
all_params = {
"team_id":
team.pk,
"start_date":
date_from.strftime("%Y-%m-%d{}".format(
" %H:%M:%S" if filter.period == "Hour" else " 00:00:00")),
**returning_event_params,
**target_event_params,
"period":
period,
}
result = sync_execute(
RETENTION_SQL.format(
returning_event_query=returning_event_query,
trunc_func=trunc_func,
target_event_query=target_event_query,
),
all_params,
)
initial_interval_result = sync_execute(
INITIAL_INTERVAL_SQL.format(
reference_event_sql=target_event_query,
trunc_func=trunc_func,
), all_params)
result_dict = {}
for initial_res in initial_interval_result:
result_dict.update({
(initial_res[0], 0): {
"count": initial_res[1],
"people": []
}
})
for res in result:
result_dict.update({
(res[0], res[1]): {
"count": res[2],
"people": []
}
})
return result_dict