tnightengale / dbt-activity-schema

A dbt-Core package for generating models from an activity stream.
GNU General Public License v3.0
38 stars 5 forks source link

add custom query logic for aggregate_all_ever relationship #39

Open bcodell opened 1 year ago

bcodell commented 1 year ago

Resolves #36

This PR:

Generated sql for dataset__aggregate_all_ever_1:

with filter_activity_stream_using_primary_activity as (
    select
        stream.activity_id as activity_id,
        stream.entity_uuid as entity_uuid,
        stream.ts as ts,
        stream.revenue_impact as revenue_impact,
        stream.activity as activity,
        stream.activity_occurrence as activity_occurrence,
        stream.activity_repeated_at as activity_repeated_at

    from "dbt"."main"."input__aggregate_all_ever" as stream
    where stream.activity = 'signed up'
        and (true)
),
append_and_aggregate__1__aggregate_all_ever
 as (
    select
    -- special case for aggregate_all_ever relationship to avoid exploding join
        entity_uuid,
        count(appended.activity_id) as aggregate_all_ever_visit_page_activity_id
    from "dbt"."main"."input__aggregate_all_ever" as appended
    where appended.activity = 'visit page'
    group by entity_uuid

),
rejoin_aggregated_activities as (
    select
        stream.activity_id,
        stream.entity_uuid,
        stream.ts,
        stream.revenue_impact,
    append_and_aggregate__1__aggregate_all_ever.aggregate_all_ever_visit_page_activity_id
    from filter_activity_stream_using_primary_activity as stream
    left join append_and_aggregate__1__aggregate_all_ever
        on append_and_aggregate__1__aggregate_all_ever.entity_uuid = stream.entity_uuid
)

select * from rejoin_aggregated_activities