example.sql

WITH all_by_rank AS
    (
        SELECT
            row_number() OVER (
                ORDER BY SUM(insight_rollup_select.count) DESC
            ) AS rank,
            axis_datum.value AS value,
            SUM(insight_rollup_select.count) AS impressions,
            GREATEST(0, hll_cardinality(hll_union_agg(insight_rollup_select.users_id_hll))) AS uniques,
            GREATEST(0, SUM(insight_rollup_select.count) /
                GREATEST(1,
                    GREATEST(0,
                        hll_cardinality(hll_union_agg(insight_rollup_select.users_id_hll))))) AS rate
        FROM
            (
                SELECT
                    anon_1.insight_id AS insight_id,
                    anon_1.period AS period,
                    anon_1.start_time AS start_time,
                    anon_1.users_id_hll AS users_id_hll,
                    anon_1.count AS count,
                    xxx_upsert_datum(anon_1.axis_datum) AS axis_datum_id
                FROM
                    (
                        SELECT
                            14 AS insight_id,
                            CAST(NULL AS rollup_period_enum) AS period,
                            date_trunc(NULL, timezone('UTC', impression.created_at)) AS start_time,
                            hll_add_agg(hll_hash_integer(impression.customer_id)) AS users_id_hll,
                            COUNT(*) AS count,
                            unnest((
                                SELECT array_agg(unnest) AS array_agg_1
                                FROM unnest(place.categories)
                            )) AS axis_datum
                        FROM
                            (
                                SELECT
                                    impression.id AS id,
                                    impression.api_user_id AS api_user_id,
                                    impression.place_id AS place_id,
                                    impression.customer_id AS customer_id,
                                    impression.client_created_at AS client_created_at,
                                    impression.bundle_id AS bundle_id,
                                    impression.created_at AS created_at,
                                    impression.source_os AS source_os,
                                    impression.custom_events AS custom_events
                                FROM impression
                                WHERE
                                    impression.api_user_id = 3
                                ORDER BY impression.created_at DESC
                                LIMIT 5000
                            ) AS impression
                        JOIN place ON impression.place_id = place.id
                        LEFT OUTER JOIN brand ON place.brand_id = brand.id
                        WHERE
                            impression.api_user_id = 3
                            AND true = ANY (
                                SELECT lower(coalesce(unnest, '')) = 'consumer' AS anon_2
                                FROM unnest(ARRAY[place.context])
                            )
                        GROUP BY axis_datum, date_trunc(NULL, timezone('UTC', impression.created_at))
                    ) AS anon_1
                WHERE anon_1.axis_datum IS NOT NULL
            ) AS insight_rollup_select
            JOIN axis_datum ON insight_rollup_select.axis_datum_id = axis_datum.id
            GROUP BY insight_rollup_select.axis_datum_id, axis_datum.value
            ORDER BY SUM(insight_rollup_select.count) DESC
        )
    SELECT
        all_by_rank.rank,
        all_by_rank.value,
        all_by_rank.impressions,
        all_by_rank.uniques,
        all_by_rank.rate
    FROM all_by_rank
    WHERE
        all_by_rank.rank >= 10
        AND all_by_rank.rank <= 20