health_score_retention.pipe

NODE health_score_retention_current_quarter
SQL >
    %
    {% if defined(project) %}
        SELECT segmentId, groupUniqArray(memberId) AS currentQuarterMembers
        FROM activityRelations_bucket_routing
        WHERE
            memberId != ''
            AND segmentId = (SELECT segmentId FROM segments_filtered)
            AND channel NOT IN (SELECT channel FROM repos_to_channels_excluded)
            AND (type, platform) IN (SELECT activityType, platform FROM activityTypes_filtered)
            {% if defined(repos) %} AND channel IN (SELECT channel FROM repos_to_channels) {% end %}
            {% if defined(endDate) %}
                AND timestamp >= toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                    - INTERVAL 1 QUARTER
                )
                AND timestamp < toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                )
            {% else %}
                AND timestamp >= toStartOfQuarter(now() - INTERVAL 1 QUARTER)
                AND timestamp < toStartOfQuarter(now())
            {% end %}
        GROUP BY segmentId
    {% else %}
        SELECT segmentId, groupUniqArray(memberId) AS currentQuarterMembers
        FROM activityRelations_deduplicated_cleaned_bucket_union
        WHERE
            memberId != ''
            AND channel NOT IN (SELECT channel FROM repos_to_channels_excluded)
            AND (type, platform) IN (SELECT activityType, platform FROM activityTypes_filtered)
            {% if defined(endDate) %}
                AND timestamp >= toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                    - INTERVAL 1 QUARTER
                )
                AND timestamp < toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                )
            {% else %}
                AND timestamp >= toStartOfQuarter(now() - INTERVAL 1 QUARTER)
                AND timestamp < toStartOfQuarter(now())
            {% end %}
        GROUP BY segmentId
    {% end %}

NODE health_score_retention_previous_quarter
SQL >
    %
    {% if defined(project) %}
        SELECT segmentId, groupUniqArray(memberId) AS previousQuarterMembers
        FROM activityRelations_bucket_routing
        WHERE
            memberId != ''
            AND segmentId = (SELECT segmentId FROM segments_filtered)
            AND channel NOT IN (SELECT channel FROM repos_to_channels_excluded)
            AND (type, platform) IN (SELECT activityType, platform FROM activityTypes_filtered)
            {% if defined(endDate) %}
                AND timestamp >= toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                    - INTERVAL 2 QUARTER
                )
                AND timestamp < toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                    - INTERVAL 1 QUARTER
                )
            {% else %}
                AND timestamp >= toStartOfQuarter(now() - INTERVAL 2 QUARTER)
                AND timestamp < toStartOfQuarter(now() - INTERVAL 1 QUARTER)
            {% end %}
        GROUP BY segmentId
    {% else %}
        SELECT segmentId, groupUniqArray(memberId) AS previousQuarterMembers
        FROM activityRelations_deduplicated_cleaned_bucket_union
        WHERE
            memberId != ''
            AND channel NOT IN (SELECT channel FROM repos_to_channels_excluded)
            AND (type, platform) IN (SELECT activityType, platform FROM activityTypes_filtered)
            {% if defined(endDate) %}
                AND timestamp >= toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                    - INTERVAL 2 QUARTER
                )
                AND timestamp < toStartOfQuarter(
                    parseDateTimeBestEffort(
                        {{ DateTime(endDate, description="Filter before date", required=False) }}
                    )
                    - INTERVAL 1 QUARTER
                )
            {% else %}
                AND timestamp >= toStartOfQuarter(now() - INTERVAL 2 QUARTER)
                AND timestamp < toStartOfQuarter(now() - INTERVAL 1 QUARTER)
            {% end %}
        GROUP BY segmentId
    {% end %}

NODE health_score_retention_counts
SQL >
    %
    SELECT
        cur.segmentId AS segmentId,
        if(
            length(coalesce(prev.previousQuarterMembers, [])) > 0,
            round(
                100 * length(
                    arrayIntersect(
                        coalesce(cur.currentQuarterMembers, []),
                        coalesce(prev.previousQuarterMembers, [])
                    )
                )
                / length(coalesce(prev.previousQuarterMembers, []))
            ),
            0
        ) AS retentionRate
    FROM health_score_retention_current_quarter AS cur
    LEFT JOIN health_score_retention_previous_quarter AS prev USING (segmentId)

NODE health_score_retention_benchmark
SQL >
    %
    SELECT
        segmentId,
        retentionRate,
        CASE
            WHEN retentionRate BETWEEN 0 AND 2
            THEN 0
            WHEN retentionRate BETWEEN 3 AND 5
            THEN 1
            WHEN retentionRate BETWEEN 6 AND 9
            THEN 2
            WHEN retentionRate BETWEEN 10 AND 14
            THEN 3
            WHEN retentionRate BETWEEN 15 AND 19
            THEN 4
            WHEN retentionRate >= 20
            THEN 5
            ELSE 0
        END AS retentionBenchmark
    FROM health_score_retention_counts