Untitled

kirzecy670

Jun 25th, 2025 (edited)

869

Never

Add comment

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

MySQL 2.31 KB | None | 0 0

raw download clone embed print report

SELECT
user_id,
family_role
FROM (
SELECT
user_id,
arraySort(x -> x.1, groupArray(family_role_with_priority))[1].2 AS family_role
FROM (
WITH
anyIf(TRUE, n_members > 1) OVER (PARTITION BY circle_id) AS has_other_members,
anyIf(TRUE, user_role = 'Child') OVER (PARTITION BY circle_id) AS has_children,
-- tuples of (priority, family role)
multiIf(
user_role = 'Adult' AND has_children AND adult_order = 1 AND n_members > 2, (1, 'first parent in full family'),
user_role = 'Adult' AND has_children AND adult_order = 1 AND n_members = 2, (2, 'single parent in full family'),
user_role = 'Adult' AND has_other_members AND adult_order = 1, (3, 'first parent with adults only'),
user_role = 'Adult' AND has_children AND adult_order > 1, (4, 'second+ parent in full family'),
user_role = 'Adult' AND has_other_members AND adult_order > 1, (5, 'second+ parent with adults only'),
user_role = 'Child', (6, 'child'),
(7, 'lone adult')
) AS family_role_with_priority
SELECT
circle_id,
user_id,
user_role,
join_ts,
creator_id,
create_ts,
family_role_with_priority
FROM (
SELECT
circle_id,
user_id,
user_role,
created_ts AS join_ts,
argMin(user_id, created_ts) OVER (PARTITION BY circle_id) AS creator_id,
min(created_ts) OVER (PARTITION BY circle_id) AS create_ts,
count(*) OVER (PARTITION BY circle_id) AS n_members,
countIf(user_role = 'Adult') OVER (PARTITION BY circle_id ORDER BY created_ts) AS adult_order
FROM circles.circle_member
)
WHERE age('day', create_ts, now()) >= '1'
AND join_ts BETWEEN create_ts AND create_ts + INTERVAL '7' DAY
)
GROUP BY user_id
)

Add Comment

Please, Sign In to add comment