CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
geoff
geoff

Reputation: 15

Is there a way to have a sum window function not progressively sum through the partition?

When I try to sum the partition to get the total of each stage, it adds progressively through the rows of the partition instead.

select
        sub_business,
        channel,
        stage,
        time::date                                                                     as time,
        count(session_id)                                                              as count,
        count / lag(count) over (partition by time::date, channel order by count desc) as conversion_rate,
        sum(count) over (partition by stage, time::date order by count)                as total
    from
        loan_dw_test.marketing.uploan_funnel_table
    where
          time::date = '2019-06-21'
      and stage in ('application_view', 'lead')
    group by
        time::date,
        sub_business,
        channel,
        stage
    order by
        time  desc,
        sub_business,
        channel,
        count desc

For example I'd want to get the total of 57 for all application_view rows and 62 for all lead rows.

Upvotes: 1

Views: 103

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

In order to calculate sum per entire partition you could skip ORDER BY:

SELECT  ...
       ,sum(count) over (partition by stage, time::date)
FROM ...

Upvotes: 2

Related Questions