How do you rewrite this code correctly in Snowflake?

select account_code, date,
       sum(box_revenue_recognition_amount) as box_revenue_recognition_amount
       , sum(case when box_flg = 1 then box_sku_quantity end) as box_sku_quantity
       , sum(box_revenue_recognition_refund_amount) as box_revenue_recognition_refund_amount
       , sum(box_discount_amount) as box_discount_amount
       , sum(box_shipping_amount) as box_shipping_amount
       , sum(box_cogs) as box_cogs
       , max(invoice_number) as invoice_number
       , max(order_number) as order_number
       , min(box_refund_date) as box_refund_date
       , first (case when order_season_rank = 1 then box_type end) as box_type
       , first (case when order_season_rank = 1 then box_order_season end) as box_order_season
       , first (case when order_season_rank = 1 then box_product_name end) as box_product_name
       , first (case when order_season_rank = 1 then box_coupon_code end) as box_coupon_code
       , first (case when order_season_rank = 1 then revenue_recognition_reason end) as revenue_recognition_reason

    from dedupe_sub_user_day
    group by account_code, date

I have tried to apply window rule has explained in

CodePudding user response：

With the way you've written your case statement, it leads me to believe that there is only one row with order_season_rank = 1 when grouping by account_code and date.

If that is true, then you can use several of Snowflake's aggregate functions and you will get what you want. Rather than trying to get the first value, you could use min, max, any_value, mode (or really any aggregate function that will ignore nulls) to return the only non-null value in the aggregation.