def get_team_distinct_ids_query(team_id: int) -> str:
from posthog.client import substitute_params
global using_new_table
using_new_table = using_new_table or _fetch_person_distinct_id2_ready()
if using_new_table:
return substitute_params(GET_TEAM_PERSON_DISTINCT_IDS_NEW_TABLE, {"team_id": team_id})
else:
return substitute_params(GET_TEAM_PERSON_DISTINCT_IDS, {"team_id": team_id})
def build_target_event_query(
filter: RetentionFilter,
team: Team,
aggregate_users_by_distinct_id: Optional[bool] = None):
target_event_query_templated, target_event_params = RetentionEventsQuery(
filter=filter,
team=team,
event_query_type=(RetentionQueryType.TARGET_FIRST_TIME if
(filter.retention_type == RETENTION_FIRST_TIME) else
RetentionQueryType.TARGET),
aggregate_users_by_distinct_id=aggregate_users_by_distinct_id,
).get_query()
query = substitute_params(target_event_query_templated,
target_event_params)
return query
def build_returning_event_query(
filter: RetentionFilter,
team: Team,
aggregate_users_by_distinct_id: Optional[bool] = None):
returning_event_query_templated, returning_event_params = RetentionEventsQuery(
filter=filter.with_data({
"breakdowns": []
}), # Avoid pulling in breakdown values from returning event query
team=team,
event_query_type=RetentionQueryType.RETURNING,
aggregate_users_by_distinct_id=aggregate_users_by_distinct_id,
).get_query()
query = substitute_params(returning_event_query_templated,
returning_event_params)
return query
def build_actor_activity_query(
filter: RetentionFilter,
team: Team,
filter_by_breakdown: Optional[BreakdownValues] = None,
selected_interval: Optional[int] = None,
aggregate_users_by_distinct_id: Optional[bool] = None,
) -> str:
"""
The retention actor query is used to retrieve something of the form:
breakdown_values, intervals_from_base, actor_id
We use actor here as an abstraction over the different types we can have aside from
person_ids
"""
returning_event_query = build_returning_event_query(
filter=filter,
team=team,
aggregate_users_by_distinct_id=aggregate_users_by_distinct_id)
target_event_query = build_target_event_query(
filter=filter,
team=team,
aggregate_users_by_distinct_id=aggregate_users_by_distinct_id)
all_params = {
"period": filter.period.lower(),
"breakdown_values":
list(filter_by_breakdown) if filter_by_breakdown else None,
"selected_interval": selected_interval,
}
query = substitute_params(RETENTION_BREAKDOWN_ACTOR_SQL,
all_params).format(
returning_event_query=returning_event_query,
target_event_query=target_event_query,
)
return query
def _build_actor_query(
filter: RetentionFilter,
team: Team,
filter_by_breakdown: Optional[BreakdownValues] = None,
selected_interval: Optional[int] = None,
):
actor_activity_query = build_actor_activity_query(
filter=filter,
team=team,
filter_by_breakdown=filter_by_breakdown,
selected_interval=selected_interval,
aggregate_users_by_distinct_id=False,
)
actor_query_template = """
SELECT
actor_id,
groupArray(actor_activity.intervals_from_base) AS appearances
FROM ({actor_activity_query}) AS actor_activity
GROUP BY actor_id
-- make sure we have stable ordering/pagination
-- NOTE: relies on ids being monotonic
ORDER BY actor_id
LIMIT 100
OFFSET %(offset)s
"""
actor_query = substitute_params(actor_query_template, {
"offset": filter.offset
}).format(actor_activity_query=actor_activity_query)
return actor_query