Untitled

kirzecy670

Nov 26th, 2024 (edited)

173

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

PostgreSQL 2.51 KB | None | 0 0

raw download clone embed print report

WITH approved_parents AS (
SELECT
pp.uid AS parent_uid,
pp.platform as parent_platform,
pp.full_reg_datetime as parent_full_reg_datetime,
children.2 AS child_id,
children.3 AS child_status
FROM
analytics.parent_properties pp ARRAY JOIN children
WHERE
pp.is_full_register
AND pp.full_reg_datetime < today() - INTERVAL 7 DAY
AND pp.full_reg_datetime >= today() - INTERVAL 57 DAY
AND pp.ip_country = 'us'
-- AND children.3 = 'approved'
),
android_children AS (
SELECT
child_props.uid AS parent_uid,
child_props.id AS child_id
FROM
analytics.child_properties child_props
JOIN
approved_parents ap ON ap.child_id = child_props.id
WHERE
child_props.platform = 'Android'
),
aggregated_app_usage AS (
SELECT
usage_data.dt AS usage_date,
usage_data.child AS child_id,
usage_data.appId AS app_id,
argMax(usageData, ts) AS max_usage_data
FROM
stat.appUsageWithAggregated usage_data
JOIN
android_children ac ON usage_data.child = ac.child_id
WHERE
usage_data.dt >= today() - INTERVAL 7 DAY
AND usage_data.dt < today()
GROUP BY
usage_date, child_id, app_id
),
daily_usage_metrics AS (
SELECT
child_id,
SUM(visitParamExtractInt(max_usage_data, 'secondUsageDay')) / COUNT(DISTINCT usage_date) AS daily_usage_seconds
FROM
aggregated_app_usage
GROUP BY
child_id
HAVING
daily_usage_seconds BETWEEN 15000 and 21000
),
child_routes_data AS (
SELECT user_id,
SUM(route_distance)/uniqExact(start_dt) AS avg_daily_distance
FROM
analytics.child_routes_new crn
JOIN android_children ac on crn.user_id = ac.child_id
WHERE
start_dt >= today() - INTERVAL 14 DAY
AND start_dt < today()
GROUP BY 1
-- HAVING avg_daily_distance BETWEEN 20000 and 40000/
)
SELECT
ap.parent_uid as parent_uid,
parent_platform,
parent_full_reg_datetime::date as parent_full_reg_date,
ap.child_id as child_id,
round(child_routes_data.avg_daily_distance) as avg_daily_distance_meters,
round(dum.daily_usage_seconds/3600, 1) as avg_daily_usage_hours
FROM
approved_parents ap
JOIN daily_usage_metrics dum on ap.child_id = dum.child_id
LEFT JOIN child_routes_data on child_routes_data.user_id = ap.child_id
ORDER BY parent_uid, child_id;

Add Comment

Please, Sign In to add comment