Skip to content

armgit / dub

Public Imported from GitHub
0

The modern link attribution platform. Loved by world-class engineering teams at Twilio, Zillow, Jobber, Vercel, Raycast, and more.

5.5 KB
Raw Blame
NODE day_intervals
SQL >

%
        WITH
            toStartOfDay(
                toDateTime64({{ DateTime64(start, '2025-09-03 00:00:00.000') }}, 3),
                {{ String(timezone, 'UTC') }}
            ) AS start,
            toStartOfDay(
                toDateTime64({{ DateTime64(end, '2025-10-03 00:00:00.000') }}, 3),
                {{ String(timezone, 'UTC') }}
            ) AS
        end
        SELECT
            arrayJoin(
                arrayMap(
                    x -> toDateTime64(toStartOfDay(toDateTime64(x, 3), {{ String(timezone, 'UTC') }}), 3),
                    range(toUInt32(start + 86400), toUInt32(end + 86400),
                    86400
                )
            )
        ) as interval



NODE usage_events_data
SQL >

%
    SELECT
        toDateTime64(toStartOfDay(timestamp, {{ String(timezone, 'UTC') }}), 3) AS interval,
        {% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
        count(*) AS events
    FROM
        (
            -- Click events
            SELECT e.timestamp{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} m.folder_id {% else %} e.domain {% end %}, {% end %}
            FROM dub_click_events_mv e
            {% if groupBy == 'folder_id' or defined(folderId) %} LEFT JOIN dub_links_metadata_latest m ON m.link_id = e.link_id {% end %}
            WHERE
                workspace_id
                = {{
                    String(
                        workspaceId,
                        'cl7pj5kq4006835rbjlt2ofka',
                        description="The unique ID for the workspace",
                        required=True,
                    )
                }}
                AND timestamp >= {{ DateTime(start, '2025-09-03 00:00:00') }}
                AND timestamp < {{ DateTime(end, '2025-10-03 00:00:00') }}
                {% if defined(folderId) %} AND folder_id = {{ folderId }} {% end %}
                {% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
            UNION ALL
            -- Lead events
            SELECT e.timestamp{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} m.folder_id {% else %} e.domain {% end %}, {% end %}
            FROM dub_lead_events_mv e
            {% if groupBy == 'folder_id' or defined(folderId) %} LEFT JOIN dub_links_metadata_latest m ON m.link_id = e.link_id {% end %}
            WHERE
                workspace_id = {{ String(workspaceId, 'cl7pj5kq4006835rbjlt2ofka') }}
                AND timestamp >= {{ DateTime(start, '2025-09-03 00:00:00') }}
                AND timestamp < {{ DateTime(end, '2025-10-03 00:00:00') }}
                {% if defined(folderId) %} AND folder_id = {{ folderId }} {% end %}
                {% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
            UNION ALL
            -- Sale events
            SELECT e.timestamp{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} m.folder_id {% else %} e.domain {% end %}, {% end %}
            FROM dub_sale_events_mv e
            {% if groupBy == 'folder_id' or defined(folderId) %} LEFT JOIN dub_links_metadata_latest m ON m.link_id = e.link_id {% end %}
            WHERE
                workspace_id = {{ String(workspaceId, 'cl7pj5kq4006835rbjlt2ofka') }}
                AND timestamp >= {{ DateTime(start, '2025-09-03 00:00:00') }}
                AND timestamp < {{ DateTime(end, '2025-10-03 00:00:00') }}
                {% if defined(folderId) %} AND folder_id = {{ folderId }} {% end %}
                {% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
        )
    GROUP BY interval{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %} {% end %}
    ORDER BY interval



NODE usage_events
SQL >

%
        SELECT
            formatDateTime(interval, '%FT%T.000%z') as date,
            {% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
            events as value
        FROM day_intervals
        LEFT JOIN usage_events_data USING interval



NODE usage_links_data
SQL >

%
    SELECT
        toDateTime64(toStartOfDay(timestamp, {{ String(timezone, 'UTC') }}), 3) AS interval,
        {% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
        count(*) as links
    FROM dub_links_metadata_latest FINAL
    WHERE
        workspace_id
        = {{
            String(
                workspaceId,
                'clrei1gld0002vs9mzn93p8ik',
                description="The ID of the workspace",
                required=True,
            )
        }}
        AND toDateTime64(timestamp, 3) >= {{ DateTime(start, '2025-09-03 00:00:00') }}
        AND toDateTime64(timestamp, 3) < {{ DateTime(end, '2025-10-03 00:00:00') }}
        {% if defined(folderId) %} AND folder_id = {{ folderId }}
        {% end %}
        {% if defined(domain) %} AND domain IN {{ Array(domain, 'String') }} {% end %}
    GROUP BY interval{% if defined(groupBy) %}, {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %} {% end %}
    ORDER BY interval



NODE usage_links
SQL >

%
        SELECT
          formatDateTime(interval, '%FT%T.000%z') as date,
          {% if defined(groupBy) %} {% if groupBy == 'folder_id' %} folder_id {% else %} domain {% end %}, {% end %}
          links as value
        FROM day_intervals
        LEFT JOIN usage_links_data USING interval



NODE endpoint
SQL >

%
    SELECT * FROM {% if resource == 'events' %} usage_events {% else %} usage_links {% end %}

TYPE ENDPOINT