Tinybird Wrapped endpoint

wrapped_pipe_stats_summary.pipe

DESCRIPTION >
    Endpoint to get pipe statistics summary with wrapped_id filter, combining daily and historic monthly data.
    Supports multiple wrapped_ids (comma-separated) for organizations split across regions.

NODE organization_lookup
SQL >
    %
    SELECT groupUniqArray(organization_id) as organization_ids
    FROM wrapped_organizations
    WHERE wrapped_id IN splitByChar(',', {{ String(wrapped_id) }})

NODE daily_max_requests
SQL >
    %
    SELECT
        timestamp as max_requests_date,
        total_daily_requests as max_requests_count
    FROM (
        SELECT
            timestamp,
            sumMerge(request_sum) as total_daily_requests
        FROM mv_pipe_stats_daily
        WHERE
            timestamp >= '2025-12-01'
            AND timestamp <= '2025-12-31'
            AND organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
            {\% if defined(pipe_id) %}
            AND pipe_id = {{ String(pipe_id) }}
            {\% end %}
            {\% if defined(workspace_id) %}
            AND workspace_id = {{ String(workspace_id) }}
            {\% end %}
        GROUP BY timestamp
        ORDER BY total_daily_requests DESC
        LIMIT 1
    )

NODE storage_info
SQL >
    %
    WITH latest_storage AS (
        SELECT
            workspace_id,
            workspace_name,
            datasource_id,
            datasource_name,
            argMax(bytes, timestamp) as latest_bytes,
            argMax(rows, timestamp) as latest_rows
        FROM raw_storage
        WHERE
            organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
            {\% if defined(workspace_id) %}
            AND workspace_id = {{ String(workspace_id) }}
            {\% end %}
            AND timestamp >= now() - INTERVAL 7 DAY
        GROUP BY workspace_id, workspace_name, datasource_id, datasource_name
    ),
    total_storage AS (
        SELECT
            sum(latest_bytes) as total_storage_bytes,
            sum(latest_rows) as total_storage_rows
        FROM latest_storage
    ),
    max_storage AS (
        SELECT
            workspace_name,
            datasource_name,
            latest_bytes as max_storage_bytes
        FROM latest_storage
        ORDER BY latest_bytes DESC
        LIMIT 1
    )
    SELECT
        total_storage_bytes,
        total_storage_rows,
        max_storage_bytes,
        workspace_name as max_storage_workspace,
        datasource_name as max_storage_datasource
    FROM total_storage
    CROSS JOIN max_storage

NODE ingestion_info
SQL >
    %
    WITH ingestion_by_type AS (
        SELECT
            event_type,
            sum(written_bytes) as total_ingested_bytes,
            sum(written_rows) as total_ingested_rows
        FROM (
            SELECT
                event_type,
                written_bytes,
                written_rows
            FROM historic_monthly_datasources_ops_log
            WHERE
                organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
                {\% if defined(workspace_id) %}
                AND workspace_id = {{ String(workspace_id) }}
                {\% end %}
            UNION ALL
            SELECT
                event_type,
                sumMerge(written_bytes) as written_bytes,
                sumMerge(written_rows) as written_rows
            FROM mv_datasources_ops_log_daily
            WHERE
                organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
                {\% if defined(workspace_id) %}
                AND workspace_id = {{ String(workspace_id) }}
                {\% end %}
                AND timestamp >= '2025-12-01'
                AND timestamp <= '2025-12-31'
            GROUP BY region, organization_id, workspace_id, workspace_name, event_type, timestamp
        )
        GROUP BY event_type
        ORDER BY total_ingested_bytes DESC
        LIMIT 3
    ),
    total_ingestion AS (
        SELECT
            sum(total_ingested_bytes) as total_ingested_data,
            sum(total_ingested_rows) as total_ingested_rows
        FROM ingestion_by_type
    ),
    top_ingestion_types AS (
        SELECT
            arrayElement(groupArray(event_type), 1) as top1_ingestion_type,
            arrayElement(groupArray(total_ingested_bytes), 1) as top1_ingestion_bytes,
            arrayElement(groupArray(event_type), 2) as top2_ingestion_type,
            arrayElement(groupArray(total_ingested_bytes), 2) as top2_ingestion_bytes,
            arrayElement(groupArray(event_type), 3) as top3_ingestion_type,
            arrayElement(groupArray(total_ingested_bytes), 3) as top3_ingestion_bytes
        FROM ingestion_by_type
    )
    SELECT
        total_ingested_data,
        total_ingested_rows,
        top1_ingestion_type,
        top1_ingestion_bytes,
        top2_ingestion_type,
        top2_ingestion_bytes,
        top3_ingestion_type,
        top3_ingestion_bytes
    FROM total_ingestion
    CROSS JOIN top_ingestion_types

NODE total_written_rows
SQL >
    %
    SELECT
        sum(written_rows) as total_written_rows
    FROM (
        SELECT
            written_rows
        FROM historic_monthly_datasources_ops_log
        WHERE
            organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
            {\% if defined(workspace_id) %}
            AND workspace_id = {{ String(workspace_id) }}
            {\% end %}
        UNION ALL
        SELECT
            sumMerge(written_rows) as written_rows
        FROM mv_datasources_ops_log_daily
        WHERE
            organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
            {\% if defined(workspace_id) %}
            AND workspace_id = {{ String(workspace_id) }}
            {\% end %}
            AND timestamp >= '2025-12-01'
            AND timestamp <= '2025-12-31'
        GROUP BY region, organization_id, workspace_id, workspace_name, event_type, timestamp
    )

NODE top_workspaces_read_rows
SQL >
    %
    WITH workspace_read_rows AS (
        SELECT
            r.workspace_id,
            w.name as workspace_name,
            sum(read_rows) as total_read_rows
        FROM (
            SELECT
                workspace_id,
                sumMerge(read_rows_sum) as read_rows
            FROM mv_pipe_stats_daily
            WHERE
                timestamp >= '2025-12-01'
                AND timestamp <= '2025-12-31'
                AND organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
            GROUP BY workspace_id
            UNION ALL
            SELECT
                workspace_id,
                sum(read_rows_sum) as read_rows
            FROM historic_monthly_pipe_stats
            WHERE
                organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
            GROUP BY workspace_id
        ) r
        LEFT JOIN workspaces_all_rt w FINAL ON r.workspace_id = w.workspace_id
        GROUP BY r.workspace_id, w.name
        ORDER BY total_read_rows DESC
        LIMIT 3
    ),
    total_org_read_rows AS (
        SELECT sum(total_read_rows) as org_total_read_rows
        FROM workspace_read_rows
    )
    SELECT
        arrayElement(groupArray(workspace_name), 1) as top1_read_workspace,
        round(100 * arrayElement(groupArray(total_read_rows), 1) / any(org_total_read_rows), 2) as top1_read_percentage,
        arrayElement(groupArray(workspace_name), 2) as top2_read_workspace,
        round(100 * arrayElement(groupArray(total_read_rows), 2) / any(org_total_read_rows), 2) as top2_read_percentage,
        arrayElement(groupArray(workspace_name), 3) as top3_read_workspace,
        round(100 * arrayElement(groupArray(total_read_rows), 3) / any(org_total_read_rows), 2) as top3_read_percentage
    FROM workspace_read_rows
    CROSS JOIN total_org_read_rows

NODE top_workspaces_written_rows
SQL >
    %
    WITH workspace_written_rows AS (
        SELECT
            r.workspace_id,
            w.name as workspace_name,
            sum(written_rows) as total_written_rows
        FROM (
            SELECT
                workspace_id,
                written_rows
            FROM historic_monthly_datasources_ops_log
            WHERE
                organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
            UNION ALL
            SELECT
                workspace_id,
                sumMerge(written_rows) as written_rows
            FROM mv_datasources_ops_log_daily
            WHERE
                organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
                AND timestamp >= '2025-12-01'
                AND timestamp <= '2025-12-31'
            GROUP BY workspace_id
        ) r
        LEFT JOIN workspaces_all_rt w FINAL ON r.workspace_id = w.workspace_id
        GROUP BY r.workspace_id, w.name
        ORDER BY total_written_rows DESC
        LIMIT 3
    ),
    total_org_written_rows AS (
        SELECT sum(total_written_rows) as org_total_written_rows
        FROM workspace_written_rows
    )
    SELECT
        arrayElement(groupArray(workspace_name), 1) as top1_written_workspace,
        round(100 * arrayElement(groupArray(total_written_rows), 1) / any(org_total_written_rows), 2) as top1_written_percentage,
        arrayElement(groupArray(workspace_name), 2) as top2_written_workspace,
        round(100 * arrayElement(groupArray(total_written_rows), 2) / any(org_total_written_rows), 2) as top2_written_percentage,
        arrayElement(groupArray(workspace_name), 3) as top3_written_workspace,
        round(100 * arrayElement(groupArray(total_written_rows), 3) / any(org_total_written_rows), 2) as top3_written_percentage
    FROM workspace_written_rows
    CROSS JOIN total_org_written_rows

NODE endpoint
SQL >
    %
    SELECT
        sum(total_requests) as request_sum,
        sum(total_errors) as error_sum,
        sum(total_read_bytes) as read_bytes_sum,
        sum(total_read_rows) as read_rows_sum,
        any(max_requests_date) as max_requests_date,
        any(max_requests_count) as max_requests_count,
        any(total_storage_bytes) as total_storage_bytes,
        any(total_storage_rows) as total_storage_rows,
        any(max_storage_bytes) as max_storage_bytes,
        any(max_storage_workspace) as max_storage_workspace,
        any(max_storage_datasource) as max_storage_datasource,
        any(total_ingested_data) as total_ingested_data,
        any(total_ingested_rows) as total_ingested_rows,
        any(top1_ingestion_type) as top1_ingestion_type,
        any(top1_ingestion_bytes) as top1_ingestion_bytes,
        any(top2_ingestion_type) as top2_ingestion_type,
        any(top2_ingestion_bytes) as top2_ingestion_bytes,
        any(top3_ingestion_type) as top3_ingestion_type,
        any(top3_ingestion_bytes) as top3_ingestion_bytes,
        any(total_written_rows) as total_written_rows,
        any(top1_read_workspace) as top1_read_workspace,
        any(top1_read_percentage) as top1_read_percentage,
        any(top2_read_workspace) as top2_read_workspace,
        any(top2_read_percentage) as top2_read_percentage,
        any(top3_read_workspace) as top3_read_workspace,
        any(top3_read_percentage) as top3_read_percentage,
        any(top1_written_workspace) as top1_written_workspace,
        any(top1_written_percentage) as top1_written_percentage,
        any(top2_written_workspace) as top2_written_workspace,
        any(top2_written_percentage) as top2_written_percentage,
        any(top3_written_workspace) as top3_written_workspace,
        any(top3_written_percentage) as top3_written_percentage
    FROM
        (
            SELECT
                pipe_name,
                sumMerge(request_sum) AS total_requests,
                sumMerge(error_sum) AS total_errors,
                sumMerge(read_bytes_sum) AS total_read_bytes,
                sumMerge(read_rows_sum) AS total_read_rows
            FROM mv_pipe_stats_daily
            WHERE
                timestamp >= '2025-12-01'
                AND timestamp <= '2025-12-31'
                AND organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
                {\% if defined(pipe_id) %}
                AND pipe_id = {{ String(pipe_id) }}
                {\% end %}
                {\% if defined(workspace_id) %}
                AND workspace_id = {{ String(workspace_id) }}
                {\% end %}
            GROUP BY pipe_name
            UNION ALL
            SELECT
                pipe_name,
                sum(request_sum) as total_requests,
                sum(error_sum) as total_errors,
                sum(read_bytes_sum) as total_read_bytes,
                sum(read_rows_sum) as total_read_rows
            FROM historic_monthly_pipe_stats
            WHERE
                organization_id IN (SELECT arrayJoin(organization_ids) FROM organization_lookup)
                {\% if defined(pipe_id) %}
                AND pipe_id = {{ String(pipe_id) }}
                {\% end %}
                {\% if defined(workspace_id) %}
                AND workspace_id = {{ String(workspace_id) }}
                {\% end %}
            GROUP BY pipe_name
        )
    CROSS JOIN daily_max_requests
    CROSS JOIN storage_info
    CROSS JOIN ingestion_info
    CROSS JOIN total_written_rows
    CROSS JOIN top_workspaces_read_rows
    CROSS JOIN top_workspaces_written_rows

TYPE endpoint